To prevent a Power Query from refreshing in Excel, you can disable background refresh, turn off the query’s refresh on file open, or remove the query entirely from the workbook. For a single query, right-click it in the Queries & Connections pane and uncheck “Refresh on file open” or set “Background data” to off. For all queries, go to Data > Queries & Connections > Queries tab, then select a query and use the properties to control refresh timing. This article explains the options, why Power Query refreshes automatically, and how to take full control.
Why Power Query Refreshes Automatically
Power Query loads data into Excel as a table or a pivot cache, and by default, Excel refreshes those queries when you open the workbook. This behavior is controlled by the query’s load-to worksheet properties. When you create a query from a file, database, or web source, Excel sets the refresh option to “Refresh data when opening the file” by default. Additionally, if you have background refresh enabled, Excel may refresh the query in the background while you continue working, which can cause unexpected updates if the source data changes.
The refresh behavior is not a single switch. It depends on three separate settings:
- Refresh on file open: Controls whether the query runs when the workbook is opened.
- Background refresh: Allows the query to run asynchronously, so Excel doesn’t wait for it to finish.
- Refresh every X minutes: A timer that automatically refreshes the query at set intervals (only if you enable it).
Understanding these three settings is key to preventing unwanted refreshes.
How to Disable Refresh for a Single Query
To stop a specific query from refreshing when you open the workbook, follow these steps in Excel for Microsoft 365 on Windows:
- Go to the Data tab and click Queries & Connections (or press Alt + F5 on Windows, Option + F5 on Mac).
- In the Queries & Connections pane, right-click the query you want to control and select Properties.
- In the Query Properties dialog, go to the Usage tab.
- Uncheck Refresh data when opening the file to stop the automatic refresh on open.
- If you also want to prevent background refresh, uncheck Enable background refresh.
- Click OK.
This method works for queries that load to a worksheet table or to the data model. On Mac, the steps are identical because the Queries & Connections pane is available in Excel for Mac 2016 and later.
How to Disable Refresh for All Queries at Once
If you have many queries and want to stop all of them from refreshing on open, you can change the workbook-level settings. However, Excel does not have a single global switch to disable all query refreshes. Instead, you must change each query’s properties individually, or you can use VBA to loop through all queries and set the RefreshOnFileOpen property to False.
Here is a simple VBA macro that disables refresh on file open for all queries in the current workbook. Remember to save a copy of your workbook before running a macro, because macros cannot be undone with Ctrl+Z.
Sub DisableAllQueryRefresh()
Dim q As WorkbookQuery
For Each q In ThisWorkbook.Queries
q.RefreshOnFileOpen = False
Next q
End Sub
To use this macro, press Alt + F11 to open the VBA editor, insert a new module, paste the code, and run it. On Mac, press Option + F11 to open the VBA editor.
How to Stop a Refresh That Is Already Running
If a query is currently refreshing and you want to cancel it, you have a few options:
- Click the Cancel button in the status bar (the small X next to the spinning icon).
- Press Esc on your keyboard to interrupt the refresh.
- If background refresh is enabled, you can also go to Data > Queries & Connections and click the Stop button (a square icon) in the pane’s toolbar.
Note that canceling a refresh may leave the query in a partially loaded state, and you might need to refresh again to get consistent data.
Common Problems and Misconceptions
A common misconception is that disabling the query’s load to worksheet will stop the refresh. That is not true. If you uncheck “Load to worksheet” but leave the query in the workbook, it will still refresh if “Refresh data when opening the file” is checked. The query loads to the data model, and the refresh still occurs.
Another issue is that even after you uncheck “Refresh data when opening the file,” the query might still refresh if you have a pivot table or chart connected to it. Pivot tables have their own refresh behavior. To stop pivot table refresh, you need to disable the pivot table’s “Refresh data when opening the file” option in its PivotTable Options (right-click the pivot table > PivotTable Options > Data tab).
Also, if you use the Excel.CurrentWorkbook() function in Power Query, it may trigger a refresh if you have circular dependencies. Always check for circular references in your query steps.
Comparing Refresh Control Methods
There are three main ways to control query refresh: manual, scheduled, and event-driven. Manual refresh means you press Ctrl + Alt + F5 (Windows) or Command + Option + F5 (Mac) to refresh all queries. Scheduled refresh uses the “Refresh every” option in query properties to set an interval (e.g., every 60 minutes). Event-driven refresh is triggered by opening the workbook or by a VBA macro. To prevent refresh, you disable the event-driven options and rely on manual or scheduled refresh as needed.
Each method has its trade-offs. Manual refresh gives you full control but requires you to remember to refresh. Scheduled refresh automates updates but can cause performance issues if the source is large. Disabling refresh entirely is best when you want a static snapshot of data.
Safety and Performance Considerations
Preventing refresh can be safe, but it has implications for data accuracy. If you disable refresh on file open, the data in your workbook becomes a snapshot from the last time you manually refreshed. This is useful for reporting and archiving, but it means you must refresh manually to get current data. Always check the last refresh time (visible in the Queries & Connections pane) to avoid presenting stale data as current.
Performance-wise, disabling refresh on open speeds up workbook load time, especially for queries that pull large datasets from external sources. However, if you later need to refresh, the operation may take longer because Excel has to re-query the source. Also, be aware that some data sources (like databases) may have connection limits, and frequent refreshes can strain the source server.
Frequently Asked Questions
Can I prevent a Power Query from refreshing when I open the workbook without deleting it?
Yes, you can. Right-click the query in the Queries & Connections pane, select Properties, and uncheck “Refresh data when opening the file.” The query remains available for manual refresh.
Why does my Power Query refresh even after I disabled it?
Check if the query is loaded to a pivot table or data model. Pivot tables have their own refresh setting. Also, verify that you changed the correct query’s properties, and that no VBA macro is forcing a refresh.
How do I stop a Power Query from refreshing in the background?
In the query properties, uncheck “Enable background refresh.” This forces the query to refresh synchronously, meaning Excel waits for it to finish, but it also gives you the option to cancel it.
Can I set a specific time for a Power Query to refresh automatically?
Yes, in the query properties, you can check “Refresh every” and set a number of minutes. This is a scheduled refresh, but note that it only works when the workbook is open.
Is there a way to prevent all queries from refreshing with one click?
No built-in one-click option exists. You can use the VBA macro provided above to disable refresh for all queries, or you can manually change each query’s properties.
Does disabling refresh affect the data already loaded in my worksheet?
No, disabling refresh only stops future refreshes. The data currently in the worksheet remains unchanged until you manually refresh or change the query.
Related
- Free Excel Mileage Log Templates: 7 Real Options
- Excel Lowercase: Convert Text to Lowercase
- Excel Loan Amortization Calculator Template
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Cell Service in Jurong East: What to CheckIf you mean a business called “Excel Cell Service” in Jurong East, I can’t reliably confirm that such…
- Excel MAC Address Format: Display, Clean, and ValidateIn Excel, store a MAC address as text, then display it in a consistent form such as 00:1A:2B:3C:4D:5E.…
- How to Change Gmail View to Look Like Outlook on DesktopTo make Gmail look more like Outlook on a computer, turn on the right-side reading pane, choose Compact…
- Choose a Date in Excel: Pickers and Date ListsIn Excel for Microsoft 365 on Windows, you can enter a date directly in a cell, or restrict…
- Heat Map Examples in PowerPoint: Ideas and How to Make ThemHeat map examples in PowerPoint include a sales-by-region matrix, a weekly activity calendar, a survey response grid, and…
- How to Set an Out of Office in OutlookTo set an out-of-office email in Outlook, open Automatic replies, turn them on, write your message, and—if available—choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.