Weekly Computer Tip # 349
3 September 2011

Update PivotTables and PivotCharts when you open the workbook (Excel 2003-2007)

One of my bugbears in Excel is the fact that PivotTables and PivotCharts are not automatically updated when you add or modify data (whereas a "normal" chart is). So when I train people how to use PivotTables (a tool, I think, sounds much more complicated than it is!) I always make a big song and dance about the fact that they should remember to refresh the information in the table. (Bonus tip: use ALT + F5.)

But one of my course participants present during one of those "song and dance acts" recently told me you can change a setting that updates PivotTable reports and PivotChart report data whenever you open the workbook. (Thanks for the tip, Chris!)

Here's how:

In Excel 2003:

  1. Right-click the PivotTable and select Table Options.
  2. Tick the Refresh on open check box.

In Excel 2007:

  1. Right-click the PivotTable and select PivotTable Options.
  2. Select the Data tab.
  3. Tick the Refresh data when opening the file check box.

In future your data will be updated whenever you open the Excel workbook that contains that PivotTable report.