11/17/10
12:11 pm

MacMost Now 478: Pivot Tables in iWork 09 Numbers

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.

You can also watch this video at YouTube (but with ads).

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

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

Amory

14 years ago

Cool. Never knew about this at all. Thanks.

Mehdi

14 years ago

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

Gary Rosenzweig

14 years ago

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?

Mehdi

14 years ago

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

Gary Rosenzweig

14 years ago

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

nina

13 years ago

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. :)

david

13 years ago

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 ??

Gary Rosenzweig

13 years ago

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

James

12 years ago

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?

Jose from Spain

13 years ago

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.

Stephen

13 years ago

Is there any way to chart the category summary data?

Gary Rosenzweig

13 years ago

Maybe. Did you try it by just selecting those cells and charting them?

dpn

13 years ago

Outstanding. Thank you so much!

dmcw

13 years ago

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

Ed Connors

13 years ago

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.

Gary Rosenzweig

13 years ago

Hard for me to say without being there. Play around with it. Experiment.

Dennis Kron Pedersen

13 years ago

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

Thanks.

John Calkins

12 years ago

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!

Gary Rosenzweig

12 years ago

Not sure how/if this can be done in Numbers for iOS. Apple's discussions section has forums.

joe

12 years ago

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

Gary Rosenzweig

12 years ago

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.

Nicole Gruen

12 years ago

Thanks for giving me quick, accessible well organized information!

Comments Closed.

MacMost Now 478: Pivot Tables in iWork 09 Numbers

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

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information