Microsoft Office Tutorials and References

In Depth Information

**Customizing Your Pivot Table**

Changing summary calculations

When you create your pivot table, Excel, by default, summarizes your data by either counting or

summing the items. Instead of Sum or Count, you may want to choose other functions, such as Average,

Min, Max, and so on. In all, 11 options are available, including:

➤
Sum:
Adds all numeric data.

➤
Count:
Counts all data items within a given field, including numeric-, text-, and date-formatted

cells.

➤
Average:
Calculates an average for the target data items.

➤
Max:
Displays the largest value in the target data items.

➤
Min:
Displays the smallest value in the target data items.

➤
Product:
Multiplies all target data items together.

➤
Count Nums:
Counts only the numeric cells in the target data items.

➤
StdDevP and StdDev:
Calculates the standard deviation for the target data items. Use

StdDevP if your data source contains the complete population. Use StdDev if your data

source contains a sample of the population.

➤
VarP and Var:
Calculates the statistical variance for the target data items. Use VarP if your

data contains a complete population. If your data contains only a sampling of the complete

population, use Var to estimate the variance.

To change the summary calculation for any given field, perform the following steps:

1.
Right-click any value within the target field.

2.
To display the Value Field Settings dialog box, select Value Field Settings.

3.
Select the type of calculation you want to use from the list of calculations (see Figure 14-20).

4.
Click OK.

Figure 14-20:
Change the type of calculation used for a field.