Problem: Most of the Pivot Table examples shown thus far are for summing revenue. What if I need to find out the average sale by customer or the smallest sale?
Strategy: Pivot tables offer eleven calculation functions. Back in Excel 2007, you would use the Field Settings icon to reach these settings. In Excel 2010, you can use the Field Settings or the new Summarize Values By dropdown.
To use Field Settings, select one numeric cell in the pivot table to make that field the active field. In the Options ribbon tab, click Field Settings.
- Choose a revenue cell, then Field Settings.
You can now choose from the 11 functions.
-
Choose from the 11 functions.
When you choose Average, the field heading will become "Average of Revenue". You can edit the custom name in the Value Field Settings dialog to "Average Revenue" or "Average Sale" or any other heading that you would like. Note, however, that you cannot reuse a name already in the pivot table. So, for example, Revenue would not be allowed, but Revenue_ or "Revenue " or " Revenue" would be allowed. Those last two include a trailing space and a leading space.
If you are in Excel 2010 and you need to use Sum, Count, Average, Max, Min, or Product, you can use the Summarize Values By dropdown in the Calculations group of the Options ribbon tab.
- Six of the function choices have been promoted to the Excel 2010 ribbon.
Gotcha: There is no built-in way to create a median for a pivot table. I've heard this question a few times.