Customizing a pivot table

  • 1/28/2022

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.

The Excel team fixed the Count Of Revenue bug

In early 2018, Microsoft fixed a bug that caused many pivot tables to provide a count of revenue instead of a sum. If a data column contains all numbers, Excel will default to Sum as the calculation. If a column contains text, the pivot table will default to Count. But up until 2018, a bug appeared if you had a mix of numbers and empty cells. An empty cell would cause your pivot table to Count instead of Sum.

One Excel customer wrote a letter to the Excel team describing this bug: “Why are you treating empty cells as text? Treat them like any other formula would treat them and consider them to be zero. A mix of numbers and zeroes should not cause a Count of that field.”

Without a single vote on Excel.UserVoice.com, someone on the Excel team patched the bug. If you have a mix of numbers and blank cells, you will no longer get a Count Of Revenue. This is a nice improvement.