How to Auto-Refresh a Pivot Table in Excel

When we analyze large datasets in Excel, we usually use pivot tables to summarize and analyze data. However, one common issue we face is that these tables do not update automatically when our source data changes. So, if we forget to update the tables manually, our reports will become outdated and provide wrong insights. And, in such a case, we need the auto refresh option for pivot tables.

Suppose you maintain monthly sales data in a worksheet, and your manager expects the pivot table dashboard to always reflect the latest figures. But, if you enter new sales records, the pivot table will not update on its own, and thus your dashboard will keep showing old numbers until you manually click Refresh. So, to avoid this, you need to use the auto refresh for pivot tables.

In this article, we will learn three different methods to automatically refresh a pivot table in Excel, including refreshing on file open, refreshing every N seconds, and setting time-based refresh with VBA.

If your data is linked to an external source and changes very frequently, like daily sales reports or some expense log, this auto-refreshing on file open method is most suitable for you.

We will use the dataset below to explain how you can make the pivot tables auto-refresh when you open the file.

We also have two pivot tables from this dataset. The first one is a sales analysis by month, which is shown below.

Another pivot table is for showing the sales analysis by region.

Steps:

➤ First, right-click anywhere inside one of the pivot tables.
➤ Then, select PivotTable Options from the menu.

➤ Now, in the PivotTable Options dialog box, go to the Data tab.
➤ Then, check the box Refresh data when opening the file.
➤ Finally, click OK to save the setting.

If your pivot table is connected to an external data source, such as SQL, Access, or Power Query connections, you can set a time interval to refresh the table automatically. This only works for an external data source, and you cannot find it if your dataset is in the same worksheet.

Here, we have a pivot table from another Excel dataset, and we will explain how you can set a timer to automatically refresh the pivot table every N seconds.

Steps:

➤ First, click anywhere inside the pivot table.
➤ Then, go to PivotTable Analyze and select Properties from the Data group. It only appears in case your pivot table is connected to an external source.

➤ Now, in the Connection Properties window, click on the Usage tab.
➤ From there, select Refresh every under the Refresh Control menu.
➤ Now, write the number of minutes you want the pivot table to refresh. For our data, let’s write 10.
➤ Finally, press Ok.

VBA is the most powerful yet very flexible way to automate the refreshing of our pivot tables. And the most fascinating part is that you can use VBA to auto-refresh any pivot tables, whether it is connected to a local source or an external one.

Now, we will learn how to auto-refresh pivot tables at a certain interval using the VBA code.

Steps:

➤ First, press  Alt  +  F11  on your keyboard to open the VBA editor.
➤ Then, on the top of this new window, you will see a menu. Select Insert from it and click on Module.

➤ Now, a blank code window will appear. Copy and paste the following VBA code inside that window.

Dim NextRefresh As Double
Sub StartAutoRefresh()
    ' Refresh all PivotTables every 10 minutes
    ThisWorkbook.RefreshAll
    NextRefresh = Now + TimeValue("00:10:00") ' Change 10 minutes if needed
    Application.OnTime NextRefresh, "StartAutoRefresh"
End Sub
Sub StopAutoRefresh()
    ' Stop the scheduled refresh
    On Error Resume Next
    Application.OnTime NextRefresh, "StartAutoRefresh", , False
End Sub

Here,

ThisWorkbook.RefreshAll refreshes all PivotTables in the workbook.

TimeValue(“00:10:00”) sets the interval for refreshing. You can change it to any minutes or seconds you want.

StartAutoRefresh schedules the next refresh automatically.

StopAutoRefresh stops the scheduled refresh.

➤ Now, press the small green run icon from the top menu and choose StartAutoRefresh.

➤ Finally, save your workbook as a Macro-Enabled Workbook (.xlsm) and close the VBA editor.
➤ If you want to stop the auto refreshing, open the VBA editor again by pressing   Alt  +  F11  .
➤ Then press  F5  and select StopAutoRefresh. Then click on Run. This will stop the scheduled refresh timer.

Frequently Asked Questions

Why Auto-Refreshing on File Open Is not Working for My PivotTable?

Usually, if your PivotTable is based on an external data source like SQL, Access, or another Excel workbook, Excel sometimes requires authentication, or sometimes the source can be broken or unavailable. Also, recheck if the Refresh data when opening the file option is enabled. In some cases, Excel security can also prevent auto-refreshing. And, some older Excel versions don’t support auto-refresh for certain external connections or Power Query sources. So, you should use this option only for local data.

Will My PivotTable Format Change After Refreshing?

No, refreshing a PivotTable will not change its formatting by default. Your row and column layout, colors, number formats, and styles will remain intact even after the data updates. However, if the refreshed data adds or removes fields, new items may appear or old items may disappear. And, this could slightly affect the layout. So, to preserve formatting consistently, right-click on any cell of the pivot table, select PivotTable Options. Then, under the Layout & Format tab, enable Preserve cell formatting on update.

Is There a Keyboard Shortcut to Auto-Refresh Pivot Tables?

No, there is no built-in shortcut for automatically refreshing a pivot table in Excel. However, there is a keyboard shortcut to refresh your pivot tables manually. To refresh a pivot table instantly, select any cell inside the pivot table and press  Alt  +  F5  to refresh only that table, or  Ctrl  +  Alt  +  F5  to refresh all pivot tables in the workbook.

Wrapping Up

In this article, we have explained three different methods on how to automate the refreshing of PivotTables in Excel, including the built-in option Refresh data when opening the file, refreshing on an interval for pivot tables linked to an external data source, and using VBA for regular interval updates. Give these methods a try, and reach out if you have any questions or want to share your feedback.