Excel 2024: Change the Calculation in a Pivot Table

Pivot tables offer a myriad of calculations in the Field Settings dialog box. Here is a faster way to change a calculation:

1. Drag Revenue to the Values area twice.

2. Double-click on the heading Sum of Revenue2. Excel opens the Value Field Settings dialog.

3. Click on Show Values As and select % of Column Total from the dropdown.

4. Type a new name in the Custom Name field, such as % of Total.

Thanks to Александр Воробьев for suggesting this tip.

Bonus Tip: Why Do Pivot Tables Count Instead of Sum?

In almost every seminar, someone asks why pivot tables default to count instead of sum. This long-standing problem was fixed in May 2018 for Microsoft 365 subscribers. The Count was triggered if you had one revenue cell that contained text or an empty cell.

Someone wrote a letter to the Excel team complaining that a single empty cell should not be treated like text. If a cell is blank and you refer to that cell in a formula, Excel treats the cell as a zero. The letter-writer pointed out that a columns with mostly numbers and a few empty cells should not trigger a Count. The person on the Excel team agreed, and quietly pushed out a change.

If you are not using Microsoft 365, then you can avoid the Count issue by making sure that there are no blank cells in your revenue column. If you don't think that you have any blank cells, make sure you are selecting one cell in your data set and not the entire columns A:J. If your data is in A2:J999 and you select A:J, you are selecting 998 numbers and over a million empty cells.