Pivot Tables in Numbers?
Does anyone know if there is a equivalent to MS Excel pivot tables in Numbers?
MacBook Pro, iOS 9.1
Apple Event: May 7th at 7 am PT
Does anyone know if there is a equivalent to MS Excel pivot tables in Numbers?
MacBook Pro, iOS 9.1
There is no direct equivalent in the Numbers user interface, where you can create (and modify) pivot tables with a few clicks and drags.
However, you can create a "cross tab" type view of your data using SUMIFS. If you look up SUMIFS in the function browser you'll see how it works. It's much easier than it looks.
Here is an example:
The first argument within the () is the column you want to sum. The following arguments are column-condition pairs, where each condition applies to the column just before it.
So the formula here in B2 is saying, add up all the numbers in column C of the data table (the first argument) where the value in column E of the Data table is the same as the value in A2 (the first column-condition pair) and the value in column D of the Data table is the same as the value in B1 (a second column-condition pair). You can add more column-condition pairs as needed.
This (which you can also do in Excel, by the way) requires a little more setup than a pivot table but you get to the same place. And you can use the new summary table as a basis for further calculations and for charts.
If you are doing a lot of this kind of analysis I have an AppleScript that removes some of the tedium by automatically setting up the table with the SUMIFS. I select the cells with the data, click, and the table appears, somewhat similar to Excel. If that is of interest, let me know what types of Pivot Tables you are trying to replicate (specifics will help) and I'll post.
SG
There is no direct equivalent in the Numbers user interface, where you can create (and modify) pivot tables with a few clicks and drags.
However, you can create a "cross tab" type view of your data using SUMIFS. If you look up SUMIFS in the function browser you'll see how it works. It's much easier than it looks.
Here is an example:
The first argument within the () is the column you want to sum. The following arguments are column-condition pairs, where each condition applies to the column just before it.
So the formula here in B2 is saying, add up all the numbers in column C of the data table (the first argument) where the value in column E of the Data table is the same as the value in A2 (the first column-condition pair) and the value in column D of the Data table is the same as the value in B1 (a second column-condition pair). You can add more column-condition pairs as needed.
This (which you can also do in Excel, by the way) requires a little more setup than a pivot table but you get to the same place. And you can use the new summary table as a basis for further calculations and for charts.
If you are doing a lot of this kind of analysis I have an AppleScript that removes some of the tedium by automatically setting up the table with the SUMIFS. I select the cells with the data, click, and the table appears, somewhat similar to Excel. If that is of interest, let me know what types of Pivot Tables you are trying to replicate (specifics will help) and I'll post.
SG
Thank you very much for this very useful and full response. I will give this a try as I really like using my Mac and would like to find an acceptable alternative to pivot tables.
Thanks for the gold star! If you could post an example or two of the kinds of Pivot Tables you do frequently and on what sort of data (see Wayne's explanation how to post a screenshot) I can give you a better idea of whether that type of summary of your data is easy to replicate in Numbers. ... Forgot to mention COUNTIFS function for counts instead of sums.
SG
Pivot Tables in Numbers?