How to auto refresh Power Query in Excel (6 ways)

In Excel, Power Query updates when we click the refresh button. This is great if we only want up-to-date data at that point in time. However, there are many scenarios where we want Power Query to automatically refresh. So, how can we achieve that?

There are built-in options and VBA options. We will look at both in this post and discover how to auto refresh Power Query in Excel.

Table of Contents

Download the example file: Join the free Insiders Program and gain access to the example file used for this post.

File name: 0207 Auto refresh Power

Auto refresh Power Query every n minutes

Often, we want data in Excel to be as up-to-date as possible.

Unfortunately, the source data used for Power Query is not part of Excel’s calculation chain. Therefore, Excel has no idea if the source data changed or not.

A common option is to trigger a refresh at regular time intervals. Let’s see how we can achieve this.

Method #1: Query refresh

From the ribbon select Data > Queries & Connections

Then, Right-click on the query and select Properties… from the menu.

In the Query Properties window, check the Refresh Every n Minutes option and enter a time interval. The example below shows every 30 minutes.

Auto refresh power query every x minutes

If we want to prevent Excel from locking up when Power Query refreshes, ensure the enable background refresh option is checked.

Click OK to close the Query Properties dialog box.

The query will now automatically refresh at the set interval.

We set this on a query-by-query basis. So, we can decide exactly which queries to refresh and when. However, be aware that when a query refreshes, it automatically updates any preceding queries in the refresh chain.

Method #2: Full refresh

The method above refreshes the query, but is not a complete refresh. This is an important distinction.

If a query loads directly to a Pivot Cache any dependent PivotTables also update. However, if the query loads to a Table, and the Table is used in a PivotTable, then the PivotTable will not refresh.


Understanding how Power Query’s background refresh works is important for getting the right behavior for your scenario.

Check this out for more information: Make Power Query refresh before PivotTables

For this we need a VBA macro.

Let’s start by ensuring the PivotTables refresh after the queries. In the Query Properties dialog box, disable the background refresh for each query.

Next, add the following code to the Workbook module of the workbook.

Save VBA code in the Workbook module
Public Time As Double
Public MacroName As String

Private Sub Workbook_Open()

'Start the refresh process when the workbook opens
Run "ThisWorkbook.RunRefreshAll"

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Stop the refresh process when the workbook closes
Application.OnTime _
    EarliestTime:=Time, _
    Procedure:=MacroName, _

End Sub

Sub RunRefreshAll()

'Apply refresh all

'Calculate the time of next refresh
Time = Now + TimeValue("00:15:00")

'Get name of macro to run
MacroName = "ThisWorkbook.RunRefreshAll"

'Schedule the macro to run at the specified time
Application.OnTime _
    EarliestTime:=Time, _
    Procedure:=MacroName, _

End Sub

In this line of code set the time interval for your requirements – the format is hh:mm:sss

Time = Now + TimeValue("00:15:00")

This is currently set to refresh every 15 minutes.

Save the workbook as a macro-enabled workbook (.xlsm).

When we open the workbook, the Workbook_Open macro starts the refresh cycle. When we close the workbook, the Workbook_BeforeClose macro stops the refresh cycle.


When a macro that changes the workbook executes, the Undo stack is cleared.  Therefore, you will not be able to use the Undo feature after the macro runs.

Unfortunately, this is standard Excel behavior, and not something we can prevent.

Auto refresh Power Query on workbook open

A common scenario is to ensure the data refreshes whenever the workbook opens. Let’s look at the options for this.

Method #1: Query refresh

If we look in the Query Properties dialog box again, we only need to check the refresh data when opening the file option.

Auto refresh when opening the file

Then, click OK.

That’s it. The next time we open the workbook the query updates.

NOTE: We may need to Enable Content on the first open.

Method #2: Full refresh

The method above refreshes queries but does not perform a complete refresh. So, if we want to refresh queries and PivotTables, we need to use a bit of VBA.

First, we need to ensure we disable the background refresh for each query in the query Properties dialog.

Next, add the following code into the Workbook module in the Visual Basic Editor.

Private Sub Workbook_Open()


End Sub

Save the workbook with a .xlsm extension.

The first time the workbook opens we will need to Enable the macros. After that, each time the workbook opens, a full refresh occurs.

Auto refresh power query when cell values change

Another option, is refreshing Power Query each time cell values change. Those cells could be parameters, or Tables containing the source data.

For this, we use a VBA Macro to trigger the refresh each time the specified cells change.

Method #1: Query refresh

Enter the following VBA code into the worksheet code module where the cell changes are to be tracked. This code must be in the worksheet module else it will not work).

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MonitorRange As Range

'The cells to monitor for changes
Set MonitorRange = Me.Range("B5,B6:C13,Data")

'Check if change occurs in the monitored cells
If Not Application.Intersect(MonitorRange, Target) _
           Is Nothing Then

    'Refresh query
    ThisWorkbook.Connections("Query - Data").Refresh

End If

End Sub

You will need to adapt the code to your requirements:

  • Change the Monitor Range to include the list of cells in the sheet to be tracked. This can be individual cells, ranges, or Table names. For example, in the code above, we monitor cells B5, B6:C13 and a Table/Named Range called Data.
  • Change the name of the query to refresh. In VBA, the name of the query must be stated as “Query – [Query Name]“.  In the example above, the query is called Data.  Therefore, the full name for VBA is Query – Data

That’s it; now the macro is ready for use. 

The Worksheet_Change event triggers every time the worksheet changes. If the change occurs in any of the specified ranges, the refresh starts.

Method #2: Full refresh

To perform a complete refresh, we use the same code above with a small change.

Change this:

    ThisWorkbook.Connections("Query - Data").Refresh

For this:


Because we are doing a complete refresh, we need to ensure background refresh is disabled.


In this post, we’ve seen 3 scenarios to auto refresh Power Query.

Since the built-in query refresh options do not provide a full refresh, we also looked at the VBA methods to achieve this.

Auto refreshing Power Query is a useful technique to ensure we always work with the correct data. I hope you can put this to good use.

Related posts:

Discover how you can automate your work with our Excel courses and tools.

Excel Academy

Excel Academy
The complete program for saving time by automating Excel.

Excel Automation Secrets

Excel Automation Secrets
Discover the 7-step framework for automating Excel.

Office Scripts Course

Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.

4 thoughts on “How to auto refresh Power Query in Excel (6 ways)”

    • Even if we unpack the XML code and change the setting to anything other than an integer, the setting disappears. So my guess is that 1 minute is the smallest unit.

  1. Interesting article. Would there also a way to do a full refresh (without opening the file) without VBA code? In a couple of weeks we are switching systems and the company wants to get rid of VBA codes/documents so I am looking to keep our files working without VBA.

    • Not at the moment.

      If Power Query comes to Excel Online, then Yes. But I don’t know if/when that that will happen.

      It may be possible to do something with a Power BI data set or a Data flow. But… that could be a rebuild of everything. So, might not be a good option either.


Leave a Comment