The AutoSum Aggravation
Microsoft Excel is built to calculate numbers, and it often tries to anticipate what math you want to perform. One of its most aggressive predictive features occurs when you convert a range of data into an official Excel “Table” (Insert > Table) or when you insert a PivotTable. If you add a column of numbers (like zip codes, ID numbers, or years) into the “Values” area of a PivotTable, Excel will almost always default to automatically summing them together. It is incredibly frustrating to drag a column of employee ID numbers into a report, only to have Excel unhelpfully add them all up to present you with a meaningless “Sum of ID” total.
How to Change the Default Calculation in PivotTables
While you cannot permanently stop Excel from attempting to aggregate numbers in the Values field of a PivotTable, you can easily change the automatic calculation from a “Sum” to something more useful, like a “Count” or simply displaying the raw text.
1. In your PivotTable, locate the field that Excel has incorrectly summed (e.g., “Sum of Zip Code”).
2. Look at the PivotTable Fields pane on the right side of your screen.
3. In the bottom-right corner, in the Values box, click the small downward arrow next to the offending field.
4. Select Value Field Settings… from the pop-up menu.
5. A new dialog box will appear. Under the “Summarize value field by” tab, change the selection from Sum to Count (or whichever calculation makes sense for your data).
6. Click OK.
Excel will instantly stop adding the numbers together and will instead count how many ID numbers or zip codes exist in that category.
How to Stop Automatic Totals in Standard Tables
If you are using a standard Excel Table (not a PivotTable) and Excel keeps automatically generating a “Total” row at the very bottom that sums up your columns, you can easily toggle this feature off.
1. Click anywhere inside your formatted Table.
2. Look at the main Excel ribbon at the top of the screen. A new, contextual tab called Table Design (or just “Design”) will appear. Click it.
3. In the “Table Style Options” group, look for a checkbox labelled Total Row.
4. Uncheck the box.
The automatic summation row at the bottom of your dataset will immediately disappear, preventing Excel from calculating unwanted totals.