You may need to refresh an Excel displayed dashboard or pivot table periodically if your data is coming from an external source (database, web or other). This tutorial will show you how to refresh your pivot table automatically.
Here is what you will learn
- Refresh Automatically when you open the file
- Refresh Pivot Table every X minutes
- Refresh on external data change
Refresh Automatically when you open the file
To setup your pivot to refresh as soon as you open the file, right click on the pivot table and click on “Pivot Table Options”.
In the Data Tab, tun ON the option to “Refresh data when opening the file”
Of course this will just refresh the pivot table so if your data is coming from an external source. To do that, go to the Data tab on your ribbon and click anywhere in your data table. Next, click on the down arrow next to the “Refresh All” button.
Go to “Connection Properties” and enable the option to Refresh Data when opening the file. If you do both steps, both your data source and your Pivot Table will refresh automatically.
Refresh Pivot Table every X minutes
Now that you have refreshed data once you open the file, you can learn to set it up so that it refreshes automatically every few minutes based on your needs.
For the External Data source, stay in the same Data connection Properties that you opened in the previous step and enable the Refresh Every X minutes option.You can type in the number of minutes in the box.
For the pivot table refresh you have several options. You can write a macro that refreshes the data every X minutes based on a counter, or you can detect when the tab containing the external data is changing (with the data refresh above) and refresh all pivot tables or a specific pivot table. I will show you the second method below (the most simple one)
Refresh on external data change
To refresh when the data changes because of a refresh, go to the Dveloper tab on your ribbon and click on the Visual Basic button
Once the Visual Basic window opens, go to the VBA Project explorer where you see all your worksheets listed, right click on your worksheet containing external data and click on “View Code”
You’ll get a blank window for code (unless you already have some code written already). In order to write some code for the event of a worksheet change (when the external data gets refreshed) open the drop-down where it says General and Select Worksheet, then open the second drop-down and select Change.
This will create a blank section for the code to be executed when the Worksheet containing External Data will change (due to a refresh)
Type in (or copy paste from below) the following. Change the name “Pivot” in the worksheet with your worksheet’s name and the same for the Pivot Name
Worksheets("Pivot").PivotTables("Pivot").PivotCache.Refresh
To get your Pivot table name, go to your Pivot Table sheet, click anywhere on your Pivot Table, click on the “Analyze” tab on your ribbon and look in the PivotTable Name field. You can rename it per your needs
Note
You may be tempted to use this
Private Sub Worksheet_Change(ByVal Target As Range)
ThisWorkbook.RefreshAll
End Sub
It may work as well. But the problem you may have using this method is that you may get stuck into a continues loop. When Refresh all is done, the data source is refreshed as well calling the function of Worksheet Change again and so on. To disable that, there is an option int the Data Connection Properties to disable the refresh on “Refresh All”
To avoid potential issues, specify the exact Pivot Table you want to refresh.
That’s it. There are many other methods but we are committed to only show the easiest, simplest and smartest one.
If this was helpful, share it with your peers.
