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
Enter your email address below to download the sample 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:
- Your source data must be on a separate sheet to that of your PivotTables
- Moving from the source data sheet to another sheet in the file triggers the VBA to refresh the PivotTable.
- 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.
- 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.
Fran Cammock
Thank you very much for the VBA code to refresh pivot tables and for this website, it is so helpful.
Mynda Treacy
Great to hear, Fran!
Lawrence Heltzer
Thank you so much for your YouTube videos. So very help and insightful!
Question regarding the VBA to auto update pivot tables. I am curious why you use the worksheet deactivate event rather than worksheet calculate on the worksheet module. I have not tested your method and whether it works with multiple sheets open in the same workbook or it’s just plain the better method.
Thanks again,
Lawrence
Philip Treacy
Hi Lawrence,
There are a couple of reasons. If you manually change some data on the source data sheet like changing 2017 to 2016 on a line, Worksheet_Calculate isn’t triggered so your PT’s wouldn’t be updated.
If you did have calculations on your source data sheet, if they were changed that would fire Worksheet_Calculate potentially causing your PT’s to be updated multiple times (every time a calculation is made), which is unnecessary.
Using Worksheet_Deactivate fires the PT update macro when you leave the sheet – and the assumption here is that you have finished changing the source data. In this approach the PT’s are only updated once.
Yes, the macro does refresh all PT’s in all sheets of the active workbook.
Regards
Phil
Ken McMillan
Thanks
Philip Treacy
You’re welcome.