Yes, Power Query in Excel can refresh data in the background, but it is not enabled by default. You control it with the “Allow data preview to download in the background” option in the Query Options dialog, and you can also choose whether to refresh your data while you continue working in the workbook. This article explains exactly how background refresh works, how to turn it on, and what it does and doesn’t do.
What Is Background Refresh in Power Query?
Background refresh means that when you trigger a data refresh (either manually or via a scheduled refresh in Power BI or Excel Online), Power Query continues to fetch and transform the data without freezing the Excel interface. In the desktop Excel app, this is most visible when you open the Power Query Editor: the preview pane loads data in the background while you can still click around the editor. For the workbook data itself, Excel 365 and Excel 2021 offer a separate setting called “Refresh data when loading a file” and a “Background refresh” option in the Data tab’s Queries & Connections pane.
In practice, background refresh lets you keep typing in cells or formatting a chart while a query pulls data from a database or a web source. Without it, Excel shows a spinning cursor and you cannot interact with the workbook until the refresh finishes.
How to Turn On Background Refresh
There are two places to control background refresh:
- For the Power Query Editor preview: Go to
File > Options > Data > Power Query Editor, and check the box “Allow data preview to download in the background.” This only affects the preview pane inside the editor, not the final data loaded into Excel. - For the workbook refresh: In the Excel ribbon, go to
Data > Queries & Connections. In the pane that opens, right-click a query and choose Properties. Under the Usage tab, you’ll see a checkbox labeled “Enable background refresh.” Uncheck it to force a foreground refresh (Excel waits until done), or check it to allow background refresh.
Note: In Excel for Mac, the Queries & Connections pane exists, but the background refresh checkbox is not available in all versions; you may only see “Refresh” options. Excel for the web does not support background refresh—refreshes happen synchronously.
How Background Refresh Works Under the Hood
When you enable background refresh, Power Query uses a separate thread to execute the M code that defines your query. The main Excel thread remains free for user interaction. Once the data is fully loaded and transformed, Excel updates the worksheet range or table with the new data. This is why you might see a brief delay between the refresh finishing and the data appearing—Excel waits for the query to complete and then writes the results.
The Power Query engine is built on the same mashup engine used by Power BI and Azure Data Factory, so it handles multiple queries in parallel when possible. However, background refresh does not mean your queries run faster; it only means the Excel UI isn’t blocked. The actual time to fetch and transform data is the same.
Choosing Between Background and Foreground Refresh
Which mode should you use? It depends on what you’re doing:
- Use foreground refresh when you need the data to be completely up-to-date before you continue, such as before printing a report, emailing a summary, or running a macro that depends on the refreshed values.
- Use background refresh when you want to keep working on other parts of the workbook while a slow query runs, such as when pulling from a large SQL database or a web API with rate limits.
A common misconception is that background refresh will automatically update your data at regular intervals. It does not. Background refresh only controls whether Excel blocks you during a manual or scheduled refresh. To schedule automatic refreshes in desktop Excel, you’d need a VBA macro with Application.OnTime or use Power BI, which has native scheduled refresh.
Common Problems and Solutions
Background refresh can cause a few issues that you might encounter:
- Data seems stale: If you check a cell immediately after clicking Refresh, you might see old values because the query is still running. Look at the Queries & Connections pane for a spinner icon. Wait for it to finish.
- “This operation is not supported” error: Some data sources, like certain OData feeds or SharePoint lists, do not allow background refresh. Excel will automatically fall back to foreground refresh for those sources.
- Refresh takes longer than expected: Background refresh can sometimes make a query slower because it competes for CPU with your other actions. If you notice this, switch to foreground refresh.
- Macros run before refresh completes: If you have a macro that triggers a refresh and then immediately reads the data, the macro might read old values. Use
ThisWorkbook.RefreshAlland then checkApplication.CalculateUntilAsyncQueriesDoneto wait for background queries.
Background Refresh vs. Power BI Scheduled Refresh
People often confuse Excel’s background refresh with Power BI’s scheduled refresh. They are different features:
- Excel desktop: Background refresh simply lets you keep working while a refresh runs. It does not schedule refreshes.
- Power BI service: Scheduled refresh lets you set a time for the dataset to refresh automatically, and Power BI does that in the cloud. There is no background/foreground distinction because the user isn’t waiting.
If you need automatic refresh in Excel, you can use Power Query to import data into a table, then set up a VBA macro to refresh every N minutes. But that’s a manual workaround, not a built-in feature.
Frequently Asked Questions
Does background refresh work with all data sources?
No. Some connectors, like certain web APIs that require authentication, may not support background refresh. Excel typically falls back to foreground refresh for those. You can check the query properties—if the “Enable background refresh” checkbox is grayed out, that source doesn’t support it.
Why is my background refresh not working?
First, make sure you enabled it in both places: the Power Query Editor option and the query’s Properties. Then check if the data source supports it. Also, if you are using Excel for the web, background refresh is not available—it only works in the Windows desktop app (and partially on Mac).
Can I refresh multiple queries in the background at once?
Yes, Excel can run multiple queries in parallel, but the number of concurrent queries is limited by your system resources and the data source’s limits. You can see the status of each query in the Queries & Connections pane.
Will background refresh improve performance?
It won’t make the query itself faster, but it will let you continue working, so the overall experience feels faster. If you have a very large dataset, the refresh might still take a long time, and you might see a delay before the data updates.
Is there a way to force a foreground refresh even if background is enabled?
Yes. In the Queries & Connections pane, right-click the query and choose “Refresh” while holding the Shift key—this forces a synchronous refresh. Alternatively, temporarily uncheck the “Enable background refresh” option in the query properties.
Does background refresh affect the data preview in the Power Query Editor?
Yes, the “Allow data preview to download in the background” option controls that. When enabled, the preview pane loads asynchronously, so you can start editing steps while the preview updates. When disabled, the editor waits for the preview to finish before allowing you to interact.
Related
- Excel Loan Amortization Calculator Template
- Excel Lookup Min Value Based on Criteria
- Free Excel Loan Amortization Template: Get One Now
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Browser Power Query: What It Is & How to UseYes, Power Query is available in Excel for the web (browser), but only for viewing and refreshing existing…
- How to Add a Heading in WordTo add a heading in Microsoft Word, put the heading text in its own paragraph, select or click…
- Serial Numbers in Excel: Automatic and Manual MethodsTo add serial numbers in Excel, enter 1 and 2 in the first two cells of a numbering…
- Excel vs Google Sheets: Which Should You Choose?Excel is usually the better choice for advanced analysis, large or complex workbooks, and specialized business tools; Google…
- Excel Conditional Formatting Based on Text: How to Set It UpYes. In Excel for Microsoft 365 on Windows, select the cells, then choose Home > Conditional Formatting >…
- How to Change Slide Size in PowerPoint to A3To change a PowerPoint presentation to A3, open the Design tab, choose Slide Size > Custom Slide Size,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.