Excel Dashboards and Reports: Change Summary Calculations

By Michael Alexander

When creating your pivot table report, Excel will, by default, summarize your data by either counting or summing the items. Instead of Sum or Count, you might want to choose 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 dataset contains the complete population. Use StdDev if your dataset 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.

You can easily change the summary calculation for any given field by taking the following actions:

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

  2. Select Value Field Settings.

    The Value Field Settings dialog box appears.

  3. Choose the type of calculation you want to use from the list of calculations. See the figure.

    image0.jpg

  4. Click OK to apply the changes.

Did you know that a single blank cell causes Excel to count instead of sum? That’s right. 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 pivot table. If the field name starts with Count Of, Excel’s counting the items in the field instead of summing the values.