PivotTable Fundamentals - Microsoft Business Intelligence Tools for Excel Analysts - page 26

➤ 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, follow these steps:

1. Right-click any value within the target field and select Value Field Settings.

The Value Field Settings dialog box opens. Refer to Figure 2-11.

2. Select the type of calculation you want to use from the list of calculations.

3. Click OK.

A single blank cell causes Excel to count instead of sum. If all the cells in a column contain
numeric data, Excel chooses Sum. If just one cell is either blank or contains text, Excel
chooses Count. Be sure to pay attention to the fields that you place into the Values area of
the PivotTable. If the field name starts with Count Of, Excel counts the items in the field
instead of summing them.

Note

Suppressing subtotals
Each time you add a field to your PivotTable, Excel adds a subtotal for that field. However, there may be
times when subtotals don't make sense or hinders your PivotTable report. For example, Figure 2-12
shows a PivotTable where the subtotals hide the data.

Figure 2-12: Subtotals sometimes muddle the data you're trying to show.