How to Refresh an Excel Pivot Table

How to Refresh an Excel Pivot Table

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

Refresh an Excel Pivot Table

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.

warning message add data to data model

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
    • warning message data model 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
    • refresh connection 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

pivottablerefresh01a

How to Refresh an Excel Pivot Table

_________________________

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.