Single Tables, Filters and Categories in Mac Numbers

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.

You can also watch this video at YouTube (but with ads).

Video Transcript: Hi, this is Gary with MacMost.com. Let's take a look at managing your data in Numbers using filters and categories.
MacMost is brought to you thanks to a great group of more than 1000 supporters. Go to MacMost.com/patreon. There you can read more about the Patreon Campaign. Join us and get exclusive content and course discounts.
Now a lot of times I see people setting up their data in Numbers in different tables. For instance maybe having a table for each month of data. Maybe even having each table in a separate sheet. This is a bad idea because it's really hard to create calculations based on your data when you have it divided up into different tables when the data is essentially the same. 
So for instance look at what I have done here. On one sheet I've got one table and this has all my data in it. The data consists basically of records with a date, a product, amount, and a sale.  Every record is on one row with a table and there are no blank rows or extra rows. I'm not repeating the Header information somewhere here. I'm not leaving blank lines to group things together or anything. The table just goes on and on with one record per row and nothing extra at the end. Now you may think, well this is great but I really want to be able to group things together. I want to see what the records for one month look like. I want to total them up. I want to be able to compare that to other months and such. We you can do that all with this one table pretty easily and without having to create a mess of all these different sheets and tables.
So the first thing you may want to look at is sorting. This is the most basic way to do it. Notice here that the dates aren't exactly in order. There is some February dates and here's some March dates. Maybe this is just the order in which you entered the data in. If you sort a column then you can get a much better picture of what's going on. So I can click to the right of A here for the first column and just use Sort Ascending and it sorts everything. So now all my January dates are together, all my February dates are together, and so on. So this is a very basic thing. It doesn't give you much additional data but at least it does group all the things together.
Now ir you really wanted to see everything, say, for the month of January you could do that with a filter. You can click here to the right of column A and use Quick Filter right here and then it is going to show you all the different options which are a ton of different things because there are all these different dates. So that's not going to be very useful. Instead of you go to Show Filter Options then it is going to take you to Organize Filter on the right here. You can Add a Filter, let's do it for the date column and you see all those checkboxes again. But if you click Date here on the left notice that you can set Arrange. So I'm going to do In the Range and set it for, say, March. March 1, 2022 to March 31, 2022. Now I can see all of my March records here. I could easily change that to April here. I'll just set it up like that and I can easily see through a filter what is going on for each month. So it isn't going to be much additional data and it is kind of annoying to  have to set the start and end date.
But a better way would be to just add categories. So instead of choosing Quick Filter or Show Filter Options I'm going to choose Add Category for Date. When I do you can see it's going to breakup all of my data by month. Why by month? Well, it's going to assume that because I've got dates that span several months. But I can change that. If I go to Organize and then Categories I could see here the categories are not setup for Date By Year/Month. Year/Month because not all the January dates will be grouped together. Just the January ones for 2022. If there was 2021 those would be grouped separately. So I could change that to say Year/Week, Year/Quarter if I want. I could change it to Day of Week and it will group all of those together. So I've got a lot of options here. 
Let's go back to Year/Month and you could see I've got January grouped together. I could Close January, Open it up. I could Option Click and it kind of closes them all and I can just open one group up. So that is already kind of like Filters but a little easier to deal with. Plus I can easily get totals and other  operations. If I look at these cells here and I select one, see this little button that appears to the right of it, I could click it and say I want the Sum. You can see how it gives me the sum of all the values for just this group. I can do the same thing here. I'm going to do Sum and it is going to give me the Sum of all that. I can see it appears for all of the other groups. So, for instance, here's March and it is the sum of all of those. Notice that my categories don't necessarily sort the way I want them to. But I can click here at the first column, the column before A now, the Categories Column there and I can sort in groups and I can say Sort By Date, Year/Month Ascending. You can see now it sorts correctly. Now I've got these great sums here and you saw there were other things that I could do here. Say, averages, or counts or maximums and minimums, all of that. I've got a handy way to see all of this data readily available to me in Categories. 
Another thing that you could do that is very similar to categories is to use Pivot tables. So selecting this table here I can click on Pivot Table at the top or Organize and then Create Pivot Tables and I could do it on the current sheet instead of a new one. So now it is going to appear here to the right. Now with the Pivot Table selected I can decide what goes in the Pivot Table. I'm going to want dates. I going to want products and you can see how it creates those there. Notice how the dates have the same Options. Year/Month, Year/Quarter, Year/Week. However I want to do it. The same thing as in Categories. Now I can decide what data actually goes in those boxes. So I could say I want Amounts and the Amounts go in here. I could say Sale and the sale number goes in there. If I have both you can see how it groups both of them together so I can see all this data. This is the exact same data I saw before in Categories but now it is separate. So I can't expand these to see all of the information under them but I have it instead side-by-side with everything calculated over here based on the data here. I can simply add new rows or update data in here. Use the button at the top right corner here to update the Pivot Table and all the values will be recalculated. 
So whether you choose to use Categories or Pivot Tables the results are going to be the same. It is just going to look different. So it is a matter of how you want to see your information.  
So I want to show you one more technique. How to use categories to be able to arbitrarily put different rows together. So, what I'm going to do here is I'm going to create another row and I'm going to call this, Included. For this I'm going to go to Format, Cell, and I'm going to set it to a Checkbox. I'm going to Copy, double click on e up here at the top and Paste. So it is all Checkboxes. So now let's say I want to include certain rows and some calculations. I can check these, like that, and say I just want to include those rows. I'd like to see the Sum and the information there. Well, if I use e here as the category, so I'll select it there, and then say Add Category for Included. Now you can see I've got a group for Not Included and a group for Is Included. If I want to sort them the other way which could be useful I can go in here and I could change the Sort Order. So I'm going to Sort Included Groups Descending. So now the checkboxes are up here. If I wanted to Add one to this I check it and it jumps to the top. If I want to Remove one I uncheck it and it jumps to the bottom. Plus I can go in here and I could Add a Sum for this. I could Add a Sum for this and now I can see I've got five items here. That's the total. If I add a couple of more to it now I've got eight items and that's the total. Now I can group them as I want.
Instead of checkboxes I could allow myself to enter in anything I want. 1, 2, 3. A, B, C. The names of things. Whatever here and have the categories update as I change the values and then it will group them together and calculate things for me. Then if I want to Dismiss that I could always go to Organize Categories and turn it off. Then continue to use the data in other ways and then turn categories On and it returns to this mode. 
So there is a lot you can do with Categories for organizing your data, for calculating things from your data if you keep all of your data inside of one table. So I encourage you, as you develop your spreadsheets, to put the records that are part of your database in a single table and learn to use these tools instead of dividing the data up in multiple tables and sheets. I hope you found this useful. Thanks for watching.

Comments: 8 Responses to “Single Tables, Filters and Categories in Mac Numbers”

pws442

1 year ago

Is there a way to cap a SUM in a Categories Summary? I.E. I can cap an individual row, but would like to cap the category SUM.

Gary Rosenzweig

pws442: do you mean a sum but limited to a maximum value? You can’t do that with Categories but you can just do it using regular formulas.

Paul

How do I cap a category summary row value?

Paul: What do you mean by "cap?" Do you mean limit it to a maximum value? You can't do that using categories. You'll need to make your own formula that uses MIN and SUM (or whatever the value function is).

Clint

Hey gary,
I'm using numbers for Icloud and iv'e hit the cap on number of filters. I tried following one of your videos but i cant pull it off. I've gotten as far as functional true/false columns. Can not for the life of me group their use in a single filter. Please help!
Thanks

Clint: Turn off the filters. Forget them for a minute. Now create a new column. Use that column to put a formula in it that includes whatever you want the "filter" to be. So a combination of IF and other things. Your goal is that every column you want to "see" should have a TRUE in it, and every column you want hidden should have a FALSE in it. Once you get that working to your satisfaction, then you an put a filter for TRUE on that one column.

Zaph Mann

6 months ago

Hi Gary - your filtering at 3 ins doesn't seem to work in my spreadsheet - For example I have a column with Countries in it and I create a filter with ANY of the negative options for text (say "is not" & Japan) - it then shows w/o Japan entries, but I ONLY want the Japan entries but when I change to "Text Is" is shows no rows at all??

(what i actually want to do is change the spelling in all the fields in this column but not others - so I thought - select them, then find/replace)

Zaph: Not sure what you are doing there with your filter so it isn't working like you expect. But to change the spelling, do a sort instead. No filter, just a sort. Then you can easily select those cells as they will be grouped together and paste the new value.

Comments Closed.

Single Tables, Filters and Categories in Mac Numbers

Comments: 8 Responses to “Single Tables, Filters and Categories in Mac Numbers”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information