9/30/21
9:00 am

The New Pivot Table Feature In Numbers

The latest version of Mac Numbers adds a new Pivot Table feature. You can now quickly and easily create pivot tables based on the data of another table. Use pivot tables to visualize information in different ways.

Video Transcript: 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.

Comments: 30 Responses to “The New Pivot Table Feature In Numbers”

Jasper

2 years ago

Do you know if there is a way to save a Pivot Table as a kind of template to apply to new files? I'm currently batch processing.

All I can think of is: 1. duplicate the first Numbers file that contains the set-up Pivot Table; 2. Paste in the next data set; 3. Refresh the Pivot Table; 4. Save with a new, appropriate name.

If you've any more elegant ideas, I'm all ears!

Gary Rosenzweig

2 years ago

Jasper: Just create a normal template like you would in any other situation. File, Save as Template.

Jane

2 years ago

Thank you and this is very useful, just in time for me business wise.

JC Blanchard

2 years ago

Very powerful tool and very well explained. Thank you!

Kent

2 years ago

thank you. very useful!

Ian

2 years ago

Hi, Gary, I'd be interested to know if you are aware of a real reason why Apple have excluded Catalina from this upgrade? My Late 2012 iMac is perfectly cable of using Pivot Tables on some very large datasets in Excel so why not in Numbers?

Gary Rosenzweig

2 years ago

Ian: I don't know. But I can guess that there are other features, perhaps from other recent updates, that require Big Sur or newer so you can't upgrade to the latest version.

Ian

2 years ago

Gary, thank you. I guess, it just seems odd that I can run 11.1 but not 11.2, I might have a bit more accepting had it been more of a major release. I now just need Apple to release the new larger iMac :-)

Ray Johnston

2 years ago

Hi, Gary. I, too, was excited to see the Pivot table addition to Numbers, and sad to see that it was not accessible via Numbers 11.1 on my old iMac running Catalina 10.15.7 . Maybe later, or when I finally spring for the new iMac.

It seems like creating a Pivot table is a "once and done" operation. I haven't found an easy way to modify the Pivot table on my iPhone until I get what I want. Am I missing something? My memory isn't as good as it used to be...

Gary Rosenzweig

2 years ago

Ray: Not sure what you are looking for in terms of modifying the pivot table. But I show lots of ways to modify them in this video.

Keith Flanagan

2 years ago

Have you tried pivot charts?

Ray

2 years ago

When you begin work, creating a Pivot table, a dialog opens that lets you move features around, then you open the table you've created. Is there a way to get back to that original dialog without starting over?

Ray

2 years ago

My iPhone version doesn't show all the menus and features on the same Sheet as the Pivot result like it does on your Mac, Gary. Its an iPhone 11 running IOS 15.0. Hmmm, thought I updated that... Now it says 15.0.1 is available, with a few bug fixes. Installing the update now. I apologize if this was the problem. I really enjoy your demos!!!

Ray

2 years ago

OK, that helps, a lot. On my iPad can display the Pivot selection option to the side of the Pivot table, and move items around. The exact movements are not quite the same, but they work. Thanks, Gary

Gene

2 years ago

Great video. It appears that the pivot table functions in Numbers is more intuitive than it is (was) in Excel although it's been a long time since I worked in Excel. Thanks for a professionally done video that is incredibly instructive!

David

2 years ago

Is it possible to filter a pivot table equal to the value of a cell in a different table?

Gary Rosenzweig

2 years ago

David: Not sure what you mean, but try it and see.

David

2 years ago

I would like to be able to type a name in a one cell table and have my pivot table filter on that cell. I’ve been trying with no success so far.

David

2 years ago

Example: Dear (name) your contributions last year ……. and show pivot table that is filtered on (name).

Gary Rosenzweig

2 years ago

David: Just use the filter function that is already there. In the right sidebar you'll see the Filter section. Use that.

David

2 years ago

I was hoping to be able to setup the filter function to use the (name) in the one cell table to filter on. It appears that is not possible.

2 years ago

Hi Gary, Is it possible in numbers to create a pivot for multiple tables or sheets containing same dataset i.e. the headers are identical? like in MS Excel you can create multiple sheets into one database by using power query and create a Pivot for that dataset.
In other words is it possible to combine the data from multiple tables or sheets, with identical header and then create a Pivot in Numbers.

Gary Rosenzweig

2 years ago

GJ: No. But if the headers are identical, then why are they in separate tables? Combine them into one table and then you can do pivots and other things with the data.

2 years ago

Thanks Gary. Yes currently table needs to be combined into 1, but in some scenarios the data is received from multiple sources. The data is same with identical headers, however they are not in the same position, they are scattered and since the source of data is a third party, you end up organising (preprocessing) it into a single table first and then do the pivoting, Whereas in MS Excel, there is this wonderful feature of power query, that can be used to perform this pre processing.

2 years ago

Do you think if Numbers is going to get this feature anytime in future ?

Thanks.

Gary Rosenzweig

2 years ago

GJ: I doubt it. I would work to combine the tables first, then create the Pivot table and do other things with the data.

Angela

2 years ago

Hi Gary, can you have other values like text in the pivot tables in Numbers?

Gary Rosenzweig

2 years ago

Angela: As the labels, sure. Not sure how text would work.

Kevin

1 year ago

Hi Gary, great video thanks! One question I can't seem to figure out after googling is if there's any ability to update the pivot table via script? I've already got a working automator script that updates a bunch of cells with some data scraped via the web, and was hoping I could add a call in that script to trigger a pivot table to update itself, rather than having to do so interactively? Really helps out the more you scale out and create a bunch of pivot tables!

Gary Rosenzweig

1 year ago

Kevin: No script will do that as far as I can tell. Just click the button. Your Automator script needs to be triggered anyway, so now you just have one more click.

Comments Closed.

The New Pivot Table Feature In Numbers

Comments: 30 Responses to “The New Pivot Table Feature In Numbers”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information