XL5: Pivot Table Aggregate Functions Reflect All Data

• Microsoft Excel for Windows, version 5.0

SUMMARY

In Microsoft Excel version 5.0, you can use pivot tables to summarize, analyze, and display data using one of several calculation methods that you choose in the PivotTable Wizard Step 3 of 4 dialog box. (To choose a calculation method, drag the appropriate field to the data section and double-click the field name button.) The calculation method you choose is also used to calculate the grand totals if you select this option for either or both your rows or columns in the PivotTable Wizard Step 4 of 4 dialog box. Both options are selected (turned on) by default.

The grand totals are based on all of the data in a particular category rather than the totals displayed in the pivot table. As a result, when you use some functions to summarize your data, the grand total may appear incorrect since its calculation is not based on the totals for each category as displayed in your pivot table but rather on all of the data underlying each of those totals. (To view the data that a grand total is based on, double-click the cell containing the total.)

A grand total may appear incorrect when you use AVERAGE() to summarize your data. For example, if your pivot table displays the average annual sales for each of 4 regions, the average shown on the grand total line will not be an average of each region's average as displayed in the pivot table; instead it will be an average of the underlying values.