Single Tables, Filters and Categories in Mac Numbers

macmost.com/e-2993 Dividing up your data among multiple tables across different sheets can make it difficult to perform calculations and organize. But by using filters or categories you can easily manage your data in a single table. You can also use Pivot Tables in some cases.
FOLLOW MACMOST FOR NEW TUTORIALS EVERY DAY
▶︎ KZbin - macmost.com/j-youtube (And hit the 🔔)
▶︎ KZbin Email Notifications - macmost.com/ytn ⭐️
▶︎ MacMost Weekly Email Newsletter - macmost.com/newsletter ✉️
SUPPORT MACMOST AT PATREON
▶︎ macmost.com/patreon ❤️
00:00 Intro
00:22 One Table For Your Data
01:33 Organizing Your Table By Sorting
02:09 Organizing With Filters
03:15 Organize With Categories
05:14 Summarizing With Pivot Tables
06:35 Categories With an Arbitrary Selection
#macmost #mactutorial

[@AlessandroScaltritti]

You are the best. I thought I didn't need tutorials about Mac before find out your KZbin contents

[@joyoffilming9500]

Again, an awfully helpful instruction video - with every of your videos about Apple Numbers I am getting my mind blown off a bit more. And, I am asking my self why I spent all my professional life with Excel. Your tutorials are invaluable!!!

[@vaniacastillo9600]

Just what I needed, Gary😊!! Thank you very much for another very helpful tutorial 👏🏾👏🏾👏🏾!

[@jeanlucvassort6411]

Excellent, as i use this category function in my numbers files, i find those categories a great tool. Happy for those which will discover this function thanks to you ...

[@MelissaAtwell]

Great video, Gary! I learned some new tricks & inspiration from this. Thank you!

[@TheMechanicalBob]

Thank you for all the great, informative and easy to follow to tutorials Gary! Highly appreciated 👏🏼

[@shutterbug49]

Another great tutorial Gary! Thanks

[@carolinebarlow4795]

Thanks very much, Gary, for this very informative and super useful video today!

[@georgee3401]

Very useful tutorial. Many thanks!

[@gregnelson5796]

Great video and suggestions. Thanks Gary

[@christinebryant9143]

Thanks Gary. As ever a very useful video.

[@RichAesthetic]

Great video, thanks for the tips!

[@loisskiathitis8926]

Pardon my tardiness! A very useful and informative video tutorial today! Thank you, Gary! 👏❤️

[@nueschi]

Brilliant, thanx 👍🏻

[@desertpatient]

Thanks bunches

[@khalidalkaabi5506]

very helpful ❤❤❤❤❤❤

[@jennifermedia6288]

I really need to get to grips with number properly, so keep doing more videos thanks

[@macmost]

Here's all of the ones at KZbin: kzbin.info/aero/PL1bsBsPtN-9S2xQDWp43cwTcGhxPAR1Hp And I have a course at courses.macmost.com too.

[@spacedudey2k]

Great video, Gary. Would be great to see you do some videos on FileMaker Pro.

[@macmost]

Probably not. I don't use it, and I think it is probably a pretty niche product at this point.

[@MacSH-SH]

Thank you very much If possible, it would be great if you could create a video about alternative software to Microsoft Word and how to use them.

[@macmost]

kzbin.info/www/bejne/barOfZV8mcp-rK8

[@shantintsai3730]

Thanks for your tutorials Is it possible to create a master sheet, and copy the whole sheet to another sheet to do filter, when I change the master sheet, the sheet been filtered will also change at the same time ?

[@macmost]

No there is nothing like that in Numbers. But you can do things with Styles and clever formulas, filters and such.

[@vichoverdugo]

Great content Gary!! You know how can I add a filter with the content of a cell? For example: filter ="B2" cell.

[@macmost]

Not sure what you mean. A filter for a cell is based on the value of that cell, not the value of another cell. So what is your goal here,? As there is probably another way to achieve it.

[@avg88883]

hi gary, is it possible to apply conditional highlighting to cells in pivot tables? Also pivot table columns have the summarize type added to the column name, so „october.23 (sum)“ or „october.23 (average). is it possible to remove them visually?

[@macmost]

I don't believe so, no. So if you need that, create your own tables that draw on data from other tables and use highlighting there.

[@peterc2248]

Is there a relational database product for the mac that you would recommend? Something like MS Access? I've tried Libreoffice Base but it so clunky! Or can I recreate a relational database using numbers?

[@macmost]

Sorry, I don't use one so I don't have a recommendation. You don't mention what you want it for, but if there is an app that handles your needs specifically, like something used in your specific industry, look for that.

[@arenawow]

Can we extract information from different file?

[@user-cb1gl3vi9s]

Good morning, is it possible to save filter and category setups? I have my data set up in one table as you suggest but I need a variety of 'views' at different times of the month and year. Do I have to recreate the filters each time or is there a way to save them for re-use?

[@macmost]

Yes, but you have to build that into your table. See kzbin.info/www/bejne/eau4kpiVeMd2fNU

[@francesco5007]

That was extremely helpful, thank you so much! Just one question: at 4:23 the cell with the Sum appears on top, I would like to make it appear in the bottom, how could I do that?

[@macmost]

Instead of using categories, just add a row at the bottom, set that row to be a footer row, and then put the SUM function in it, like =SUM(D) or such. kzbin.info/www/bejne/aKC4pqZoi7mmgLc

[@francesco5007]

@@macmost thanks a lot! ❤️

[@francesco5007]

sorry again, I've just realized that doing this I wont be able to divide my data in months, Also I like the feature of categories of collapse and extend groups, I just dont like to sums, avarages etc, to be shown on top @@macmost

[@macmost]

@@francesco5007 You may have to compromise on that.

[@francesco5007]

I see, thanks a lot anyway@@macmost

[@annieparry5544]

I'm using Numbers for my stock. I already have a filter set up on one column to show (in this case) all my wildlife T Shirts which is in my "category" column. I have another column called "type of animal" which I want to use to show how many of each type of animal I have in the dropdown header box. Problem is it's also showing all the other entries on the spreadsheet that are in the "category" column, not just the ones that are "wildlife". I need to be able to filter "only visible entries". I can't find a way to do this and I need this sort of function on a regular basis. I used to do this very easily on Excel. A video on this would be much appreciated. Thankyou.

[@macmost]

So it sounds like you have to filter on the category column to only show the rows in that category.

[@silambarasankalugu7081]

Hi, post a video analysing CDR using in Police department , if possible

[@macmost]

Sounds like a very specific task you want to accomplish. Not familiar with that. Either learn some spreadsheet skills and then apply them to the problem, or work with someone else who already has the needed skills.

[@julduc]

once the categories filter is active, it is inpossible for me to style the table correctly, example changing gridlines stroke, etc. anybody knows how to change that? thanks you!

[@macmost]

I would switch off the filter, do your styling, then switch it on again.

[@leonardobianco-marketingch5702]

How do I filter the first 10 values (sum) of a pivot? It seems not possible to filter, for example, the first 10 values of a pivot (which are a sum), the filter returns the first 10 values of the table to which the pivot is bound, not those already summed. Am I wrong? thanks!!

[@macmost]

I'm not sure from that description what you mean, sorry. A filter wouldn't be something you use on the pivot table but on the data table.

[@leonardobianco-marketingch5702]

​@@macmost I have a pivot that returns the sum of the turnover of 50 customers. Now, there is a filter that I can apply to the results of the pivot and choose to see only the first 10 customers with the highest turnover. Applying the filter (first 10 values of the pivot) returns me the 10 highest values of the table the pivot refers to and not the 10 highest SUMS produced by the pivot. What is the point of having the possibility to filter the pivot if the filter actually refers to the table?

[@arenawow]

Can we create links between pages?

[@macmost]

Yes. Select some text in a Text Box or in a text cell, then choose Format, Add Link, Sheet.

[@arenawow]

Thank you so much @@macmost

[@sa34w]

Mac numbers is due for a refresh and addition of more features like Excel

[@macmost]

What features would you like to see added?

[@Tom_M_Riddle]

How do I add the last day of the month to every month and to auto populate an expense? Like rent?

[@macmost]

Look into using the EOMONTH function.

[@Paintdeezwallz]

Is it possible to set sharing limitations so other viewers do not get their spreadsheets filtered and categorized?

[@macmost]

Set to view only?

[@Paintdeezwallz]

@@macmost OK thank you, but I still want the other individuals to be able to edit the spreadsheet.

[@Paintdeezwallz]

So basically, we run a paint company and our directors edit look and update the spreadsheet throughout the day some individuals prefer to filter and categorize, but that is reflected on the other individuals so my question is, can that be limited so it does not reflect in real time and onlyseen on one viewer

[@macmost]

@@Paintdeezwallz Maybe a spreadsheet isn't the right tool here? Maybe a web app? But you can lock a table in Numbers. That doesn't completely prevent others from changing those values, but if they follow rules you set for them it will work. Then it is just a matter of putting the elements they can change in tables that are unlocked, and the elements they shouldn't change in locked tables.

[@Paintdeezwallz]

@@macmost thank you 🙏 for the advice. I will apply it.

[@PersonalInjuryMarketing]

gary ive never begged before in my life but i beg that you respond to this and try and help me I am so lost and have tried everything. I filtered my mac numbers sheet to the data that I want and then i have tried everything to try and convert that new filtered sheet to a csv and it just inputs the unfiltered sheet.

[@macmost]

One way is to sort, not filter, so the rows you want to keep are at the top. Then delete the other rows, export, Undo to restore the deleted rows. Another way is to filter, select the cells in the table, then create a new document. Select cell A1 in the new table. Paste. Then export that. Delete that document if you don't want it after that.

[@shakti11111]

But how s Colour filter?

[@macmost]

Not sure what you are asking.

[@shakti11111]

@@macmost suppose in Row some words in Red Colour so how can only red row filter in number?

[@macmost]

@@shakti11111 How did they get to be red? Did you just change the text style? If so, there's nothing you can do with that. Formulas and filters look at values, not styling. So take a step back and look at what you have. Why is the value different and can you filter based on that difference. Or, can you simply add a checkbox value as a column and use that. See how I do that in this very video.

[@DavidBattistella]

You know. Based on this video I was unable to do what seems like a very simple thing. Sort 100 different records FIRST by category and THEN by DATE within the category. Simple expense breakdowns. Tried to sort by individual rows but still the whole table is affected. Would seem very simply. Here are 100 expenses. Sort them by, gas, shopping, etc. and then sort the date within the category. Perhaps I am having a severe block on this but I can't see a simple way to do that.

[@macmost]

Not sure what you are trying to do, but maybe try sorting with categories switch off temporarily, then back on.

[@DavidBattistella]

@@macmost i thought i had replied to this but had not. I resolved it quite simply by adding a categories column and then inserting a category name beside each record. Numbers did a great job of sorting the categories and keeping dates arranged in order within each category (something i could not do without using categories. So thank you again for this channel and for responding , i thought it right to follow up with a solution.

[@catboy721]

Gary - another informative video. Just two comments: 1) At around 6:45 when you begin your category/arbitrary discussion, you say you're creating a new row and it's actually a column. No big deal. Also, just an observation that you tend to speak very quickly. Fortunately, KZbin has a way of dealing with this on the viewer's end. But really great, clearly-explained content. Thanks.