Spreadsheets: Why Pivot Tables Won’t Sum

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.

4 Powerful Communication Strategies for Your Next Board Meeting

This whitepaper outlines four powerful strategies to amplify board meeting conversations during a time of economic volatility. 

Download the whitepaper

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:

1. Select the entire range that should contain numeric data.
2. Press Ctrl G to display the Go To dialog.
3. In the lower-left corner of the Go To dialog, click Special to display the Go To Special dialog.
4. Choose Blanks from the dialog and click OK. Only the blank cells will be selected.
5. Type a zero and press Ctrl Enter. This will fill the blank cells with zero.

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:

1. Select the entire range that should contain numeric data.
2. Press Ctrl G to display the Go To dialog.
3. In the lower-left corner of the Go To dialog, click Special to display the Go To Special dialog.
4. If the range contains formulas, choose Formulas from the dialog and uncheck Numbers as shown in Figure 1 (below, left). This will specify that you are looking for Formulas 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.

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:

1. Select a single cell in the dataset before starting your pivot table. Excel will automatically extend this single cell to incorporate the entire contiguous range of data.
2. After the pivot table is created, go back to the source data and press Ctrl T in Excel 2007-2010 or Ctrl L in Excel 2003. This will define the range as a table.
3. Later, if you have new data, paste that data in the blank row immediately below the data. These new rows will become part of the table.
4. Select a cell in the pivot table and click the Refresh button. Excel will reread the data from the expanded table into the pivot table cache, and the results will appear in the pivot table. (In Excel 2003, the refresh button is a red exclamation mark on the PivotTable Toolbar. In Excel 2007-2010, look for a large button that says Refresh on the Options tab of the ribbon as shown below.)
   

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.