This chapter is from the book
This chapter is from the book
This chapter is from the book
Changing Summary Calculations
When creating your pivot table report, by default Excel summarizes your 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 incorrectly chose 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 2013, the first clue that you have a problem appears when you select the check box for Revenue in the Fields section of the PivotTable Field List. If Excel moves the Revenue field to the Rows drop zone, you know that Excel considers the field to be text instead of numeric.
Be vigilant while dragging fields into the Values drop zone. If a calculation appears to be dramatically low, check to see if the field name reads Count of Revenue instead of Sum of Revenue. 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 no longer in the ribbon in Excel 2013. All of the pivot table calculations icons for the Quick Access Toolbar were removed from Excel 2013. 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.
The 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 would be 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.