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.