The New Pivot Table Feature In Numbers

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.