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.
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:
- 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.
- 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.
- 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.
- 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.