Pivot table not updating correctly
Excel Data Tables allow you to run 1, 2 or more sensitivities within a financial model.However, we often get queries about an Excel data table not calculating correctly (typically all answers are the same).Some calculated fields are calculations of the source data while others are calculations of the results of other calculated fields (this is the problematic part).Example and the only workaround I have so far: Short answers like this one are frowned upon in these sites.Works in Excel 2003 and earlier, and in Excel 2007 if you’ve installed the latest service packs.What if the worksheet contains a pivot table and its sister pivot chart?There is an easy way to convert the blanks to zero. 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! , True, False, or even a number stored as text will cause the pivot table to count instead of sum. 1, you can use the Go To Special dialog to find the offending cells. 1 If the range contains values instead of formulas, choose Constants from the dialog and uncheck Numbers as shown in Figure 2 (below, right). 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.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. This will specify that you are looking for Constants that result in Text, Logicals, or Errors. Use the Tab key to move to all of the cells that are causing problems. 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. 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.
The problem: Pivot table with 31 calculated fields.When you have a pivot table that counts instead of sums, it is caused by one of three reasons. 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.But in Excel 2007, these steps don’t work the same way.
When you copy the worksheet with the pivot table and chart, not only does the new pivot table link to the same old data, the new chart also links to the old pivot table.
You are unknowingly running into problems with Reason No. Starting in Excel 2003, there is an easy workaround: Bill Jelen is the author of 32 books about Excel, including Power Pivot for the Excel Data Analyst.