MrExcel.com - Your One Stop for Excel Tips & Solutions

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

January 20, 2023 - by

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 in the Value Field Settings dialog.

To use Field Settings, select one numeric cell in the pivot table to make that field the active field. In the Analyze tab of the ribbon, click Field Settings.

A pivot table with Sum of Revenue by Customer. From any Revenue cell, click Field Settings.
Figure 953. Choose a revenue cell, then Field Settings.

You can now choose from the 11 functions.

The Value Field Settings dialog offers two tabs. On the Summarize Values By tab, the 11 choices are Sum, Count, Average, Max, Min, Product, Count Numbers, StdDev, StdDevP, Var, and VarP.
Figure 954. 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.

Gotcha: There is no built-in way to create a median for a pivot table. I’ve heard this question a few times.

This article is an excerpt from Power Excel With MrExcel

Title photo by Jukan Tateisi on Unsplash