Auto Refresh PivotTables

In this tutorial we’re going to look at the options we have available to auto refresh PivotTables in Excel without using VBA. The process differs depending on whether you use Power Query to get the data or not. I’ll also show you the VBA method required for regular PivotTables. Beware because not all PivotTables based on Power Query data will auto refresh.

Watch the Video

Download Workbook

1. Power Query Data Loaded to Data Model

PivotTables created from data loaded to the Power Pivot Data Model via Power Query can be automatically refreshed. The automatic refresh settings are applied in the Query settings, which is also available via the Queries & Connections pane:

2. Power Query Data Loaded to Pivot Cache

Regular PivotTables created based on data you get with Power Query and then load to a PivotTable or Pivot Chart can also be automatically refreshed. You must select either PivotTable Report or PivotChart in the Import Data dialog box:

Note: these options are not available in earlier versions of Excel.

And then set the refresh frequency in the Query Properties as per the previous example.

3. Power Query Data Loaded to Table

When you load data from Power Query to a Table and then create a PivotTable from said table you cannot use the automatic refresh settings. This is because the PivotTable will refresh before the query has time to finish loading the updated data to the Table that the PivotTable is connected to.

If you intend to build a PivotTable from the query data, avoid loading the query to a Table. Instead, load it direct to the PivotTable or Pivot Chart as shown in the previous example.

If you must load it to a Table first, then you can use the VBA technique described in example 6.

Now strictly speaking, best practice is to use Power Query to get your data and load it to the Power Pivot Data Model or to the Pivot Cache, however because you're able to bypass Power Query, we'll look at those options next.

4. Data Loaded Direct to Data Model

Power Pivot PivotTables created by loading your data to the Power Pivot model either via the Add to Data Model button on the Power Pivot tab:

Or by checking the Add to data model button when creating a new PivotTable:

Can be automatically refreshed via the settings in the Connection Properties. There you can set the refresh to as often as every minute or refresh data when opening the file:

5. Data from External Source

If you create a regular PivotTable by connecting to an external source, whether that’s another Excel file, text or CSV files, or a database, you can set the auto refresh frequency in the Connection Properties as we saw in the first example.

Auto Refresh PivotTable Warning

Careful, each time the auto refresh triggers, Excel becomes temporarily unresponsive and takes focus away from the cell or object you’re working on. This can be super annoying if you’re entering data or making other changes to your file when the refresh triggers, so don’t get carried away setting the refresh frequency too often.

6. Auto Refresh PivotTables with VBA

If you’ve built a regular PivotTable that’s based on data stored in your current file, then the only option for automatic refresh is to use VBA. This is useful if users are entering data into the table that your PivotTable is based on.

It relies on two key elements:

1. Your source data must be on a separate sheet to that of your PivotTables
2. Moving from the source data sheet to another sheet in the file triggers the VBA to refresh the PivotTable.

There are two VBA components:  

1. The code that triggers the VBA to execute when the source data sheet is deselected:

This goes into the module in the VB editor for the sheet(s) containing your source data.

2. The code that refreshes all PivotTables in the file:

This goes into the module for “ThisWorkbook” in the VB editor.

Auto Refresh PivotTable Notes

The file must be open for refresh to occur, so keep this in mind if you’re referencing the PivotTable from another file.