While iWork Numbers doesn’t have true pivot tables, you can get similar results using Table Categories. This feature lets you group data together in various ways to reveal the same summaries that you can get with pivot tables.
Gary Rosenzweig: Hi, this is Gary with MacMost Now. On today’s episode let’s learn how to do pivot tables in iWork 09 Numbers.
So the name of this video is a little misleading because we’re not actually going to make pivot tables. Pivot tables are when you create a second table from the first table that summarizes the data in some way. Usually grouping bits of like data together. In iWork numbers you don’t actually create pivot tables. Instead you do table categories, which kind a has the same result.
Let’s take a look.
So here I’ve got some sample data. It’s basically just a list of sales of different types of fruit on different months, amounts, and prices, and even the calculation of the price times the amount here in the end. So in order to create a category table, what I need to do is I need to be sure that I’ve got a header column here. I already have the header row with the names of these columns in it.
I am going to actually change the style so we can have it be a little more pronounced so you can see the column. So you can see now it’s a little bit better now. You can see which ones are the header columns in rows and which ones are the cells with the data in them.
Then I am going to go and select this column here, the product column. I am going to click on the little down arrow button there and I have several options. One of them is categorize this column. When I select that one what happens is the header column on the left will change to show the different products and it will resort these, you can see, to put them together. So all the apples are together. The oranges, the bananas, are together. In addition, I can collapse these here to collapse each one of the different categories.
Now, notice that each of the rows that contains the name of a product here, a category, will actually be a header row itself. You can see it’s a different color. In addition to that, it will show you some numbers in it. So, for instance, shows you the total amount here, it shows you the average price here, and it shows you the total price there. Now, it actually figured all that out on its own.
I could change that if I wanted to. So, for instance, if I wanted to calculate the average price instead of the total price I could simply select average instead. Notice now when I collapse all of these I still have all this data here. So it’s a real quick easy way to summarize all of the data using category tables. Now, if I want to expand all of these categories I can option click on the arrow here and it will expand them all. Same thing for collapsing them all.
Now, I can create a deeper category here. I can go and actually organize these by date as well. So I can categorize this column as well and you can see now I have apples for January, February, and March, oranges for January, February, et cetera.
I can collapse all of these subcategories with option click or expand and shrink just one, and I can expand all of these again like that. So if I wanted to see basically per month for each different product, I could actually get a quick summary here. I have the same data here with the totals. Or I could go and change to averages and it will change to averages for all of those.
If I wanted this to be the month first and then the product, I could simply get rid of these here. So I will simply go back to the product row and delete that category. Now it leaves just the month. So I can see all the months there and the totals for that. Now I can add this one back in and because I’m adding it back in second it will actually put that underneath the month. So it’s the order that you start the categories that determines how these work.
Now if you’ve used pivot tables before you many want to try this techniques in Numbers and see if it’s a good substitute. Some people report that it actually works better for them than pivot tables while others report that it’s not true pivot tables and it doesn’t give them all the functionality.
So I hope you liked this look at Table Categories in iWork 09 Numbers. Until next time, this is Gary with MacMost Now.