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.

Until next week.

PS I’m committed to sharing the best of what I know with others so please don’t keep me a secret. If you enjoyed today’s tip, please forward it to anyone you feel may benefit. Alternatively, feel free to reprint it (with full copyright and subscription information) in your newsletters and message boards.

Home   Training   Support   Clients   Contact Details   Virtual Press Room   Disclaimer