Written by Debra Dalgleish from Contextures Blog
If you change any of the information in a pivot table’s source data, the pivot table won’t immediately show your latest changes. You have to refresh a pivot table, automatically or manually, to see the latest data.
Manually Refresh a Pivot Table
It’s easy to update a pivot table manually:
- Right-click on the pivot table
- Click the Refresh command
NOTE: When you refresh a pivot table, its PivotCache is updated. ALL pivot tables that use the same pivot cache will also be updated.
Refresh When File Opens
You can also set a pivot table to update automatically when file opens. Just follow these steps to change a pivot table setting:
- Right-click a cell in the pivot table
- Click on PivotTable Options
- Click the Data tab
- In the PivotTable Data section, add a check mark to “Refresh data when opening the file”
- Click OK, to save the option setting
Use Pivot Table Macros
Another way to update a pivot table is with macros. For example, use an Excel macro to automatically refresh a pivot table when the pivot table’s worksheet is activated.
There are sample macros on my Contextures site, to update one pivot table, or multiple pivot tables.
Go to the Excel Pivot Table Refresh page to get the macro examples.
Refresh Pivot Table on a Timer
If your workbook has OLAP-based pivot tables, you can also update automatically, based on a timer in its connection.
NOTE: When you created a pivot table, if you added its data to the Data Model, your pivot table is OLAP-based.
To set the connection timer to refresh automatically, follow these steps:
- On the Excel Ribbon, click the Data tab
- Click Queries & Connections
- In the Queries & Connections pane, click the Connections tab
- Right-click on WorksheetConnection, and click Properties
- On the Usage tab, add a check mark for “Refresh every x minutes”
- Type a number in the minutes box, to set the timer
- Tip: If your source data changes frequently, and the data set isn’t too big, use a low number. For other workbooks, use a higher number, so your work isn’t delayed while you wait for the connection to refresh
- Click OK, to save the settings
Get the Sample File
For more tips, and to download the sample file, go to the Refresh a Pivot Table page on my Contextures website.
The sample file is in xlsx format, and doesn’t contain any macros. The file has 2 pivot tables (Data Model and Normal), so you’ll see a connection alert message, when you open the file.
How to Refresh an Excel Pivot Table
How to Refresh an Excel Pivot Table is a post from Contextures Blog and is not allowed to be copied to other sites