Editor’s Note: To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips page.
Eric S. wins an autographed copy of Pivot Table Data Crunching from CFO and MrExcel for his question: “Is there any way in Excel 2007 to have a pivot table always default to sum the amounts rather than count them?”
A pivot table created from a well-formed dataset will automatically sum the numeric fields. When you have a pivot table that counts instead of sums, it is caused by one of three reasons.
Reason No. 1: There Are One or More Blank Cells in the Column
Excel expects your numeric data to be 100% numeric. If you have a dataset with 50,000 rows of numbers and one blank cell in the middle, the pivot table will count instead of sum. There is an easy way to convert the blanks to zero. Follow these steps:
Be careful after Step 4: if you get the message “No Cells Were Found,” then there are no blank cells in the range. Do not proceed to Step 5.
Reason No. 2: There Are One or More Cells in the Column that Contain Text, an Error, or True/False
Just as with the blank cell, having any cell contain #N/A! or DIV/0!, True, False, or even a number stored as text will cause the pivot table to count instead of sum. As with Reason No. 1, you can use the Go To Special dialog to find the offending cells. Follow these steps:
Fig. 1
If the range contains values instead of formulas, choose Constants from the dialog and uncheck Numbers as shown in Figure 2 (below, right). This will specify that you are looking for Constants that result in Text, Logicals, or Errors. Click OK. Use the Tab key to move to all of the cells that are causing problems. Fix each cell.
Reason No. 3: You Are Selecting the Entire Worksheet or the Entire Column as the Range for Your Pivot Table Source Data
Let’s say that you have data in A1:G150000. If you would select all of columns A:G as the source for your pivot table, then you are including almost 900,000 blank cells below your data in the pivot table cache. You are unknowingly running into problems with Reason No. 1.
Fig. 2
You were likely taught this method so that you could later add new rows below the data, refresh your pivot table, and the new rows would be included in the pivot table cache. Starting in Excel 2003, there is an easy workaround:
Bill Jelen is the author of 32 books about Excel, including PowerPivot for the Excel Data Analyst. You can win a copy of one of his books if your question is selected for a column. Post your question to the community content block on the right.