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.



Video Transcript (Click to Expand)
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.


22 Responses to “MacMost Now 478: Pivot Tables in iWork 09 Numbers”

  1. Amory says:

    Cool. Never knew about this at all. Thanks.

  2. Mehdi says:

    Hello Gary,

    tanx a lot for your videos, i think there are thousands of new things hidden in Mac, ;)
    Gary, there is a problem which i could not find a solution for that yet, how can one uninstall the software updates of the programs which are uninstalled, as you know sometimes we want to try a software and some updates will be installed with it and after we uninstall the software, the updates are still there, is it a way to uninstall them?
    should we also uninstall our previous updates, e.g. the the very old Itunes updates?

    Cheers
    MG

    • Any updates you install to a piece of software should be uninstalled when you uninstall the software.
      Are you talking about the update installer file that you downloaded? In your Downloads folder? You can throw that away as soon as the update is applied.
      But for iTunes, that is all handled by Mac OS X Software update, You shouldn’t have any files left over from that. Are you seeing some files? If so, what?

  3. Mehdi says:

    Hi Gary,

    Thanks for your reply,
    About my first question, you are right, as i uninstalled them (Fire Fox and Microsoft), they were also gone, but about my second question, i am talking about the Mac programs (like iWork or Safari), i see them in System Preferences> Soft Ware update, by the tab: Installed Software…
    I found that there are some updates of the Old version of iTunes or the iWork 09 that i used to have or some other stuff, as i see i can not change or delete them, is it so?
    Are these necessary for the Mac or should be deleted?

    Cheers
    MG

    • I believe what you are looking at is a log of the updates installed. They are not the installers themselves. Just leave those alone.

  4. nina says:

    thank so much. i hope iWork developers will continue adding more similar excel functions. the table category is much easier to understand than the pivot table in excel. you can’t just teach pivoting to anybody using excel they end up messing with the data causing error. :)

  5. david says:

    hi Gary!
    thanks a lot for the video!!
    i just want to know, i have a great amount of data, and most of them are different from each other, how can i create like an interval for example, to add all the data from 20 to 30, another from 40 to 50, and so on, to make an statistic analysis and create a graphic ??

    • I’m not quite sure what you are looking for. But perhaps consulting a spreadsheet expert is the way to go.

    • James says:

      Create a column next to those numbers and divide by ten and round to whole number.
      In excel you would pivot using that column (e.g. there might be ten 1′s then ten 2′s etc) and get count, sum,average or whatever you choose.
      Perhaps using a similar technique in numbers it might work for you?

  6. Jose from Spain says:

    Great explanation. Very important for the switchers coming from Excel.
    It is not exactly the same as pivot tables have a little more filters, but I ‘ll start working with numbers and I’ll see what I’m able to do.
    Thaknks for sharing it.

  7. Stephen says:

    Is there any way to chart the category summary data?

  8. dpn says:

    Outstanding. Thank you so much!

  9. dmcw says:

    super awesome cool! Just what I needed, just in time.

  10. Ed Connors says:

    Gary, thank you for another informative and well explained video on pivot tables within Numbers. I am hoping that you can help with this question: when I try to categorize my data I am not getting subtotals by each category. All of the data is collected very nicely by category, but no totals are showing. Any idea as to why this would happen?

    Thank you.

  11. Dennis Kron Pedersen says:

    Really nice video. This is really simple and easy to use :-)

    Thanks.

  12. John Calkins says:

    Great solution for emulating pivot tables in Numbers. Is there a way to do this in iOS Numbers? I do not see a Categorize Menu Selection. If not, do you know if there is a user group for iOS Numbers? We want to use Numbers on the iPad to read our data on the road. Great Job, Thank You!

  13. joe says:

    The average function is not correct. All it does is take each row add it up and divide by number of rows… in this example you have different price per units.

    if we add up each price and divide by 6 we get the .24… however this is not the true price per unit

    The true price per unit is 293.00 divided by 1120 which yeilds .26…

    my question is how do we add a header for this … it can be done with excel with tables but not in numbers

    • The average function isn’t what you want for this. You want a simple formula: get the sum of one column and divide it by the sum of the other, for instance — not sure on the exact formula for your situation, but I’m sure you can figure it out.

  14. Nicole Gruen says:

    Thanks for giving me quick, accessible well organized information!

Comments Closed.

Comments have been closed on this post as it is getting a bit old. If you would like to ask a new question, simply visit the MacMost Q&A Forum.