Most valuable professional
  • Excel Book Excerpt

Excel Update Data Behind a Pivot Table

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

Update Data Behind a Pivot Table

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 tab, you click the Refresh icon to recalculate the pivot table from the worksheet data.

LE10000804.jpg 

Figure 775 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.

<-Previous Topic                    Next Topic->

For more resources for Microsoft Excel: