How to refresh Power Query automatically in Excel
How-to · By Tahir Nas · Examples run in our simulator · Updated
9 min read
To refresh Power Query automatically in Excel, open the query's connection properties and tick Refresh data when opening the file, Refresh every n minutes, or both. Both settings work while the workbook is open in Excel; for a refresh at a fixed time you can add a short VBA macro, and for a refresh with nobody at a computer the queries need to move to a service that can schedule them.
The options at a glance
| Option | Set up in | Needs the workbook open | Fits a Data Model query |
|---|---|---|---|
| Refresh when the file opens | Connection properties, Usage tab | Yes, it runs on opening | Yes |
| Refresh every n minutes | Connection properties, Usage tab | Yes | Yes, but without background refresh |
| Timed refresh with VBA | A macro and Application.OnTime | Yes, with Excel running | Yes, through RefreshAll |
| Scheduled refresh in a service | Power BI, after moving the queries | No | Not applicable: the model lives in the service |
If you are new to refreshing queries in Excel, the guide to managing Power Query queries in Excel shows where Refresh All, the Queries & Connections pane and the Properties dialog are.
Option 1: refresh when the workbook opens
Microsoft's article on refreshing an external data connection in Excel gives these steps:
- Select Data > Queries & Connections and open the Connections tab.
- Right-click the query and select Properties.
- On the Usage tab, under Refresh control, tick Refresh data when opening the file.
Repeat for each query that should refresh. The same tab also has Remove data from the external data range before saving the workbook, which saves the query definition without the data. That keeps the file small, but anyone who opens it without refreshing sees an empty table, so use it only when refresh on open is also ticked.
Refresh on open can be blocked by Excel's security settings. Microsoft's article explains that external data may be disabled on a computer, and that to refresh when a workbook opens you must enable the data connections from the Trust Center bar or keep the workbook in a trusted location. If the setting seems to do nothing, check for the yellow security bar first.
Option 2: refresh every few minutes
On the same Usage tab, tick Refresh every and enter the number of minutes between refreshes. This suits a workbook left open on a shared screen, or one that reads a source updated during the day.
Keep the interval longer than the refresh itself takes. A query that needs four minutes should not be set to refresh every two.
Background refresh
Enable background refresh, also on the Usage tab, returns control of Excel to you while the query runs. Microsoft notes two limits: background refresh is not available for OLAP queries, or for any connection that retrieves data for the Data Model. A query loaded to the Data Model always refreshes while you wait. The post on choosing whether to load to a table or the Data Model covers that trade-off.
Option 3: refresh on a timer with VBA
When the result must be ready at a fixed time, a macro can do the scheduling. Excel's Application.OnTime method runs a procedure at a given time of day or after a given delay. The workbook must be saved as a macro-enabled .xlsm file, and Excel must stay open. If the macro does more than refresh, Power Query vs VBA macros helps decide what stays in VBA.
Put this in a standard module:
Public Sub ScheduleRefresh()
Dim nextRun As Date
nextRun = Date + TimeValue("07:00:00")
If nextRun <= Now Then nextRun = nextRun + 1
Application.OnTime nextRun, "RefreshQueries"
End Sub
Public Sub RefreshQueries()
RefreshQueryTables
ScheduleRefresh
End SubScheduleRefresh books the next 07:00, today if it is still to come and tomorrow otherwise. RefreshQueries runs the refresh and then books the following day. Call ScheduleRefresh once, for example from Workbook_Open in the ThisWorkbook module, to start the cycle. RefreshQueryTables is defined in the next section; it is used instead of ThisWorkbook.RefreshAll for a reason.
Catch failed refreshes
Workbook.RefreshAll has a catch that matters for unattended runs. Microsoft's reference says that if a Power Query query loading to a worksheet table fails, RefreshAll raises no run-time error: the other connections still refresh and the failed table keeps its old data. A report can then look current when it isn't.
The same page gives the fix: refresh each table's query separately with QueryTable.Refresh and BackgroundQuery set to False, and handle any error it raises.
Public Sub RefreshQueryTables()
Dim ws As Worksheet, lo As ListObject, qt As QueryTable
Dim logSheet As Worksheet
Set logSheet = ThisWorkbook.Worksheets("Refresh log")
For Each ws In ThisWorkbook.Worksheets
For Each lo In ws.ListObjects
Set qt = Nothing
On Error Resume Next
Set qt = lo.QueryTable
Err.Clear
If Not qt Is Nothing Then
qt.Refresh BackgroundQuery:=False
If Err.Number <> 0 Then
logSheet.Cells(logSheet.Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(1, 3).Value = Array(Now, lo.Name, Err.Description)
Err.Clear
End If
End If
On Error GoTo 0
Next lo
Next ws
End SubThe macro writes one row per failure (time, table name, error message) to a sheet called Refresh log and carries on with the next table, so one bad source does not stop the rest. Add a sheet with that exact name before the first run: if it is missing, the macro stops with a run-time error before anything refreshes. Tables that Power Query did not load have no QueryTable, so the loop skips them.
The loop only covers queries loaded to worksheet tables. Queries loaded only to the Data Model, or only to a PivotTable, have no worksheet table, so they are not refreshed by it. If the workbook has those, keep a ThisWorkbook.RefreshAll call for them, and remember that it will not report their failures either.
When nobody opens the workbook
Every option above needs Excel running with the workbook open. If the report must refresh overnight on its own, desktop Excel is the wrong host for the schedule. For acting on refreshed data rather than refreshing it, Power Query vs Power Automate explains where a flow fits. Two common routes are:
- Keep a computer signed in with Excel open and use the
OnTimemacro. This works, but it depends on that machine staying on and the macro staying enabled. - Move the queries to Power BI, where the service can refresh a published semantic model on a schedule. The lesson on refresh in Excel, Power BI Desktop and the service explains how refresh differs between those hosts, and the guide on moving a query between Excel and Power BI shows how to copy the M across.
In Excel for the web, Microsoft's page on using Power Query in Excel for the web describes refreshing with Data > Refresh All or from the Queries pane. It does not describe a timed or on-open refresh setting there.
Make the query safe to refresh unattended
An automatic refresh runs your query against data you have not seen. Two failures are common enough to guard against before you switch it on.
A column disappears
If a source drops or renames a column, any step that names it fails the whole query (handling changing column headers covers the defences):
let
Source = #table({"Date", "Region", "Units"}, {{"2026-10-01", "North", 4}, {"2026-10-02", "South", 6}}),
Kept = Table.SelectColumns(Source, {"Date", "Region", "Units", "Channel"})
in
KeptResult: an error
Expression.Error: The column 'Channel' of the table wasn't found.
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The simulator raises Expression.Error: The column 'Channel' of the table wasn't found. When an optional column may be missing, tell the step what to do instead:
let
Source = #table({"Date", "Region", "Units"}, {{"2026-10-01", "North", 4}, {"2026-10-02", "South", 6}}),
Kept = Table.SelectColumns(Source, {"Date", "Region", "Units", "Channel"}, MissingField.UseNull)
in
KeptResult
| Date | Region | Units | Channel |
|---|---|---|---|
| 2026-10-01 | North | 4 | null |
| 2026-10-02 | South | 6 | null |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Now both rows come through with Channel as null. Use this only for columns that are genuinely optional; a missing required column should still stop the refresh. The lesson on MissingField options covers when to use each.
A value will not convert
A text value in a number column becomes an error cell after a type change. The query still loads, but the error cell loads into Excel as an empty cell, so a total can quietly come out too low. A small check query lists the bad rows:
let
Source = #table({"Date", "Region", "Units"}, {{"2026-10-01", "North", 4}, {"2026-10-02", "South", "n/a"}}),
Typed = Table.TransformColumnTypes(Source, {{"Units", Int64.Type}}),
Problems = Table.SelectRowsWithErrors(Typed, {"Units"})
in
ProblemsResult
| Date | Region | Units |
|---|---|---|
| 2026-10-02 | South | Error: DataFormat.Error: We couldn't convert to Whole Number. |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
It returns the one South row, where Units holds the conversion error (the simulator reports DataFormat.Error: We couldn't convert to Whole Number.; Excel's wording can differ slightly). Excel also flags error cells after a load with an errors link next to the query in the Queries & Connections pane, but a separate check query keeps the bad rows in view. Load a query like this to its own sheet and glance at it after each refresh. The refresh-proof checklist lists other checks worth adding.
Show when the data was refreshed
A one-cell query that records the refresh time tells readers whether they are looking at today's numbers. It needs the real clock, so it only runs in Excel:
let
Refreshed = #table(type table [#"Last refreshed" = datetime], {{DateTime.LocalNow()}})
in
RefreshedLoad it to a table near the report title. Because it refreshes with the other queries, a stale time means the refresh did not run.
Practise refresh-proof queries
The refresh and performance exercises on Power Query Exercises practise the habits that make an automatic refresh trustworthy, and the challenge debug a query that works today and breaks on refresh is a good test of them.
Frequently asked questions
Why doesn't refreshing in the Power Query Editor update my sheet?
Microsoft's refresh article explains that refreshing inside the editor updates the preview and Power Query's local cache, not the table in the worksheet or the Data Model. Close the editor and use Data > Refresh All, or Query > Refresh on the loaded table.
Can I stop one query from refreshing with Refresh All?
Yes. In the same Properties dialog, clear Refresh this connection on Refresh All. The query then refreshes only when you refresh it directly.
How do I stop a refresh that is taking too long?
Press Esc. For a background refresh, Microsoft's article says to double-click the status bar message and select Stop Refresh.
Sources
- refreshing an external data connection in Excel (support.microsoft.com)
- Application.OnTime (learn.microsoft.com)
- Workbook.RefreshAll (learn.microsoft.com)
- using Power Query in Excel for the web (support.microsoft.com)
About the author
Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.
Keep reading
- Power Query vs VBA macros: when to switchWhen to replace a VBA macro with Power Query: which data jobs move across, which still need VBA, and how to migrate a macro without breaking the report.
- Why your Excel report breaks every month, and how Power Query fixes itMonthly Excel reports break when the export changes shape. See the usual causes and the Power Query steps that let a report survive each refresh.
- Power Query in Excel for Mac: what works and what doesn'tWhat Power Query in Excel for Mac can do, per Microsoft's documentation and Excel team posts: the editor, data sources, file paths, sharing with Windows.
Practise Power Query in the browser with graded exercises checked against fresh data.
Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.