This chapter is from the book
This chapter is from the book
This chapter is from the book
Changing Summary Calculations
When you create a pivot table report, by default Excel summarizes the data by either counting or summing the items. Instead of Sum or Count, you might want to choose functions such as Min, Max, and Count Numeric. In all, 11 options are available. However, the common reason to change a summary calculation is that Excel has incorrectly chosen to count instead of sum your data.
Understanding Why One Blank Cell Causes a Count
If all the cells in a column contain numeric data, Excel chooses to sum. If just one cell is either blank or contains text, Excel chooses to count.
In Figure 3.23, the worksheet contains mostly numeric entries but has a single blank cell in G2. The one blank cell is enough to cause Excel to count the data instead of summing.
)
Figure 3.23 The single blank cell in G2 causes problems in the default pivot table.
In Excel 2016, the first clue that you have a problem appears when you select the Revenue check box in the top section of the PivotTable Fields list. If Excel moves the Revenue field to the Rows area, you know that Excel considers the field to be text instead of numeric.
When you create the pivot table in Figure 3.24, you should notice that your company has only $562 in revenue instead of millions. This should be a hint that the heading in B3 reads Count of Revenue instead of Sum of Revenue. In fact, 562 is one less than the number of records in the data set; Excel doesn’t include the blank cell in the Count function.
){kind=link}
Figure 3.24 Your revenue numbers look anemic. Notice in cell B3 that Excel chose to count instead of sum the revenue. This often happens if you inadvertently have one blank cell in your Revenue column.
To override the incorrect Count calculation, right-click any pivot table cell in the Revenue column. Choose Summarize Values By and then choose Sum (see Figure 3.25).
)
Figure 3.25 Change the function from Count to Sum in the Summarize Values By drop-down.
Using Functions Other Than Count or Sum
The settings for Summarize Values By and Show Values As were temporarily promoted to drop-downs in the Excel 2010 ribbon, but they are not in the ribbon in Excel 2016. All of the pivot table calculations icons for the Quick Access Toolbar were removed from Excel 2013 and are still gone in 2016. They were apparently removed to make space for Insert Timeline, Drill Down, Drill Up, and Recommended Pivot Tables. If you were a fan of Summarize Values By and Show Values As, you can continue to use them from the right-click menu or by selecting a cell and pressing Shift+F10. These options have always been available in the Value Field Settings dialog.
Excel offers six functions through the Summarize Values By command, plus five more options when you select More Options. The options available are as follows:
- Sum—Provides a total of all numeric data.
- Count—Counts all cells, including numeric, text, and error cells. This is equivalent to the Excel function =COUNTA().
- Average—Provides an average.
- Max—Shows the largest value.
- Min—Shows the smallest value.
- Product—Multiplies all the cells together. For example, if your data set has cells with values of 3, 4, and 5, the product is 60.
- Count Nums—Counts only the numeric cells. This is equivalent to the Excel function =COUNT().
- StdDev and StdDevP—Calculate the standard deviation. Use StdDevP if your data set contains the complete population. Use StdDev if your data set contains a sample of the population.
- Var and VarP—Calculate the statistical variance. 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.