Excel: Update Data Behind a Pivot Table

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

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

Strategy: This is an important thing to understand about pivot tables: When you create a pivot table, all the data is loaded into memory to allow it to calculate quickly. When you change the data on the original worksheet, it 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 or Analyze tab, you click the Refresh icon to recalculate the pivot table from the worksheet data.

  1. After changing the underlying data, refresh the cache.

    Results: The pivot table is updated.

    Additional Details: Making changes to the underlying data could cause the table to grow. For example, if you re-classify 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.