Hi, this is Gary with MacMost.com. Let's take a look
at the new Pivot Tables feature in Mac Numbers.
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.
So Apple added a full featured Pivot Tables function in Numbers. This is in the new version
of Numbers, so version 11.2, available on Mac and also iPad and iPhone. You'll see here the top new
feature is Pivot Tables. So here's some simple examples to get you started with Pivot Tables.
You actually could do a lot of this before using Categories
in Numbers but Pivot Tables allows you to keep your original table as
is and create a separate table where you can visualize the data in different ways.
For instance, let's start with this very simple table here. It's just some sales with some dates,
product, and an amount. With the cell in here selected I could
click the Pivot Table button right here but instead I'm going to go to Organize
and then Create Pivot Table. This allows me to create a Pivot Table on the current sheet.
So I'm going to do that and you'll see this new table forms here. There's really nothing
in it yet because there are a lot of options we need to select first. On the right under
Organize you'll see Pivot Options up here. Then you'll see three columns from the original table
all with empty checkboxes next to them. So let's start off by selecting one thing.
The Amounts. This will create the simplest thing we can do with Pivot Tables and that is
to sum all of the amounts up into one item. So it's basically a zero dimension Pivot Table.
It's just adding everything up. There are no rows and no columns. So let's add a dimension to this.
We'll start with Product. If I do Product now I can see all three products, there are only three
here in the product column, listed and then the sum for each of those here. So already we have
something useful pretty quickly. We could have used the SUMIF function over here to calculate
all of the different sums for each product. But this did it for us really quickly. If there were
more than three it would have done all of them. So we can see exactly how many of each product
are sold. Now if we scroll down here we can see the rows of product and values are the amounts.
We can actually change some things here like, for instance, for the values I can click the i button
there and I can change it to instead of being the Sum it could be the Average. So the average
of each cell for each product. Or I can go and do the count. The number of cells for each product,
not taking into account the amounts of each cell. I could also go and change the value to something
like a percent of the grand total. So you can see percentages here. The grand total being 100%.
Now let's remove Product here and instead add Date. What I'm going to get now are rows that
are for each month. So it's smart enough to figure out, well you have all these different dates you
probably want them grouped into something more than just the day. So we'll start off with Month.
Then you could see the total for each month. Now if we go down here to Date I can click i
and I could see. I could say yes I want them to be monthly, maybe weekly, or maybe quarterly.
So I could set it up anyway I want. I'm not restricted to just per month. I could even say
give me the actual value and have each date listed separately. I could also decide if I want them
sorted here by month or maybe I want them sorted by the amounts here. So I could see
the best month here at the end or sort descending to see the top month at the top.
But now what if I were to select Product as well as Date. Well then I get this much more complex
Pivot Table here. It's going to show me Products in columns and Dates in rows. So now I could see
all of the months here. I could see all the products here. I can see really clearly all
of my data and I could see the totals here for the months going across. So this is the total for
January. I could see the totals for the columns as well. So this is the total for this product.
So this is the whole point here of Pivot Tables. They allow you to visualize information in ways
that's difficult when you're viewing an entire table with one record per row. I could just
keep adding more sales to this and then in this Pivot Table I could use this button up here to
update it once I've added more data and all of the numbers and everything in here will be updated
to reflect the new data in the original table. You can even customize it beyond this. If I don't want
the products to be in the columns I could drag it under here. Now you could see each month
is expandable with the three products underneath it. If I
wanted it to be the other way just drag the product on top of the date.
Now you could see Products are the main thing with each month underneath. So I
can hide or expand for each product here and just see the amount here on the right.
Here's a different kind of table. Here we've got Tests, the Date the test were taken on,
the Student, and the Score. So if I were to select this and then go to Organize, Create Pivot Table
on current sheet, it would put it there. Let's say we want to have the Test, Student, and the Score.
Now we can visualize all this data. It's the same data but it's a little easier to look at.
Here we can see the Students going across. The Tests going vertically and then we can see for
each student, for instance, the total. The Total amount useful here. What we want is the Average.
Now we can see the Average for each student for these tests. Also how the class did for each test.
Another thing you could do with Pivot Tables is you could filter them. Notice the Filter Option
here. I could go in here and add a filter. Let's use that to visualize something that would be hard
to see otherwise. I'm going to add a filter for Score. Then instead of selecting each individual
score here I'm going to go to Number and say, if it's less than and then 80. So I'm going to
see the scores that are lower than 80 here. Now instead of actually seeing the scores I'm going
to go and look at the Count. So the number of times the student for a test, was below 80. So
by doing this I could see quite clearly that this test was the toughest for the students.
I could see pretty clearly that this student was the one having the most trouble and that
these two students did pretty well and this test seemed to be the easiest one for the whole class.
Here's a table with a lot more data. Let's go and create a Pivot Table for this one. Then I'm going
to add the Dates, Stores, Product, Amount, and the Sales Person. Now you can see a very complex
Pivot Table here. I can compress each one of these. Let's put the Stores all together like
that so we can see the five different stores. Let's say we want to do the same thing for the
Months. A shortcut for this is Option Click and you could see it closes all of these. So now you
could see Stores per Month and the total sales. I could also, then if I want to dig down deeper,
I can expand this store here and see all the sales people or I can expand this month
and see all the product. Then if I want to, instead of seeing actual numbers here which
sort of lose meaning when they get too big, I can go here and say instead of base value give me
percentage of grand total. So, for instance, now I can see that the best month was January. The store
with the best sales was the East store here. If I want to go and see the best sales person then I
could actually move this sales person on top of Stores like that. Let's Option Click here to
close all of these. Now I'll see the Sales People here and I could see the best Sales Person there.
So there's a basic introduction for how to use Pivot Tables in Numbers. It's a great
new addition. I know people have been asking for Pivot Tables for a long time in Mac Numbers and
we've been using Categories instead. But now we finally have this functionality built-in
and it works when you import Excel spreadsheets with Pivot Tables into Numbers. It should take
those Pivot Tables, bring them into Numbers, and display them using the new functionality.
The same thing with Exporting from Numbers into Excel. You should then be able to see
those Pivot Tables in Excel at the other end. Hope you found this useful. Thanks for watching.