O'Reilly logo

Excel 2010: The Missing Manual by Matthew MacDonald

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Fine-Tuning Pivot Table Calculations

As you saw earlier, when you add a field to the Values box, Excel guesses what calculation you want to perform. In most cases, it assumes you want to perform a sum operation that totals up all the values in the field. However, this calculation isn't always the right one. Consider the sales summary pivot table that you've been exploring in this chapter. Although it makes sense to examine the total units sold, you may be just as interested in the maximum, minimum, or average order size, or the order count (the number of times the product was ordered, without considering the number of units in each order).

This pivot table drills down through three levels of row groupings. It shows a detailed breakdown that indicates when Chef Anton's Gumbo Mix was ordered, and exactly where the shipments were headed.

Figure 22-15. This pivot table drills down through three levels of row groupings. It shows a detailed breakdown that indicates when Chef Anton's Gumbo Mix was ordered, and exactly where the shipments were headed.

Fortunately, Excel makes it easy to change the type of calculation you're using. And as you'll see in the following sections, you can even perform more than one calculation in the same pivot table, and throw your custom formulas into the mix.

Changing the Type of Calculation

To modify the calculation that the pivot table performs, follow these steps:

  1. Find the appropriate field in the Values box of the PivotTable Field List pane. Click the drop-down arrow, and then choose Value Field Settings.

    If you want to change the current operation, which ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required