cfo.com

Print this article | Return to Article | Return to CFO.com

A Refreshing Change

How to hold on to your updates when you correct a pivot table.
Bill Jelen, CFO Magazine
August 1, 2012

Q: I've discovered that some of the underlying data in my pivot table is wrong. After I correct a number, the pivot table does not appear to include the change. Why does this happen, and how do I hold on to my updates?

A: There is an important concept to understand about pivot tables: When you create a pivot table, all the data is loaded into memory to allow it to calculate quickly. Changing the data on the original worksheet does not automatically update the pivot table.

You need to select a cell in the pivot table. The PivotTable ribbon tabs will appear. On the Options tab, click the Refresh icon to recalculate the pivot table from the worksheet data (see Figure 1). The result should be that the pivot table is updated.

One word of caution: Making changes to the underlying data could cause the table to grow. For example, if you reclassify some records from the East region to the Southeast region, be aware that clicking the Refresh button will cause the table to grow by one column. If there happens to be other data in that column, Excel will warn you and ask if it is okay to overwrite those cells.

If you would like to submit a question to Bill "MrExcel" Jelen, go to CFO's Spreadsheet Community Center at www.cfo.com/spreadsheets.




CFO Publishing Corporation 2009. All rights reserved.