• Excel Book Excerpt

Excel Create a Report That Shows Count, Min, Max, Average, Etc.

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Create a Report That Shows Count,
Min, Max, Average, Etc.

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.

LE10000922.jpg

Figure 886 Choose a revenue cell, then Field Settings.

You can now choose from the 11 functions.

LE10000923.jpg

Figure 887 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.

LE10000924.jpg

Figure 888 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.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel: