How to set Power Query to refresh automatically in Excel

Building a Power Query model in Excel only to refresh it manually is like buying a self-driving car and steering it by hand. While many reach for clunky VBA macros to automate the task, those scripts trigger security warnings and require constant maintenance. Here's the safer, set-and-forget way to do it.

The hidden cost of manual refreshing

Stop acting like a data operator

Relying on manual Power Query refresh is a gamble on your own memory. If you get distracted and forget to click that button, you'll spend the rest of the day making decisions based on old data. What's more, in today's fast-paced world, the time spent manually opening a file and waiting for a refresh to finish is a recurring drain on your productivity, time better spent on higher-value work. Many users try to fix this with VBA macros, but those scripts bring extra complexity, security prompts, and maintenance headaches.

Automating the refresh cycle using a built-in setting shifts your role from a data operator to a systems architect . Instead of manually maintaining the report or wrestling with complex code, you're building a self-sustaining system. This ensures anyone opening the file sees the most current data immediately, without needing to understand the underlying queries.

Here's how to get started.

Activating the native refresh engine

Spend two minutes now to save hours down the line

Assuming you've already cleaned your data and loaded your final query into a table on its own worksheet (not just the data model), you can now hand the task over to Excel's background engine.

The automatic refresh timer is available only in the Excel desktop app (Windows and Mac). When a Power Query is loaded to a worksheet table, Excel treats it as a data connection and allows it to refresh automatically at a set interval—but only while the workbook is open in the desktop app.

Here's how:

1. In the Datatab, click Queries & Connectionsto open the side pane.

2. Right-clickthe query you want to automate, and click Properties.

3. In the Usagetab of the Query Propertiesdialog, check the box for Refresh every [x] minutesand enter your desired interval (more on this below).

4. You should also check the box for Refresh data when opening the fileto ensure the first view is always current.

5. Click OK.

As soon as you click OK, the engine is live. You don't need to do anything else when you close the dialog—simply add to or remove data from your source data as you normally would, and the automation handles the update in the background.

For this timer to work, the Excel file must remain open on your machine. If you close the workbook or put your computer to sleep, the refresh cycle will pause until you reopen the file.

Try it out:Set the refresh interval to 1 minuteand start a countdown timer for the same period. Then, add a new row of data to your source table, and head back to your query table. You'll see the new row appear automatically when the refresh cycle completes.

Setting the right interval for your workflow

The auto-refresh period you set depends on your specific needs. For high-traffic dashboards used throughout the day, a five or 10-minute interval provides near-real-time updates without taxing your system. For standard daily trackers, a 60 or 120-minute cycle is usually fine.

While you can set the timer to as low as one minute, use caution. If your query takes a long time to load, a one-minute interval can leave Excel in a permanent refresh state, hogging your CPU and potentially causing you to be throttled by your data source's server. Aim for an interval at least twice as long as the query takes to finish loading.

The "background refresh" danger zone

Balance speed against data integrity

It's important to distinguish between the auto-refresh timer you just activated and the Background Refreshsetting found in the same dialog tab. While the timer tells Excel when to start the update, Background Refresh determines how Excel behaves while the update runs.

When Enable background refreshis checked, Excel fetches the data in the "background," allowing you to keep working while the query runs. If it's unchecked, Excel pauses certain interactions with the workbook until the data pull is complete, preventing you from editing parts of the file that depend on that connection.

While the background option sounds like a better user experience, it can be a trap for complex workbooks. In workbooks with multiple queries or dependent formulas, background refresh can occasionally allow other calculations to run before the data load finishes. For mission-critical reports, uncheckthis option to ensure Excel finishes the data pull before allowing any dependent objects to update.

Why Power Query auto-refresh is better than VBA

Ditch the macro-enabled liability

Moving away from macro-enabled files is a significant upgrade in many people's workflow. Standard XLSX files typically avoid the macro security warnings that XLSM files often trigger, meaning your file is more likely to open and load as expected.

What's more, these property-based timers are much more resilient than code. While the refresh timer itself requires the Excel desktop app to be open, the underlying connection properties stay with the file wherever it goes. You can hand the workbook to someone else, knowing they won't have to touch a VBA editor or deal with legacy script errors just to get the data they need.

Disclaimer: Local automation has its limits

Know when to level up

The automatic Power Query refresh setting is a feature of the Excel desktop app. Because it's a local Excel feature, it only works while the workbook is active on your machine. If you open your file in Excel for the web , the automatic timer won't trigger. That said, Microsoft confirms that "you can refresh data sources on Excel for the Web." Simply click Refresh Allin the Datatab to refresh all data sources in the workbook, or click Data > Queriesto refresh specific queries.

So, if you need a file to update while you're offline or if your team works exclusively in the browser, that is your cue to move toward Power Automate or other cloud-based automation tools to trigger refreshes. However, for most daily tasks where you have Excel open on your desktop, these built-in properties provide a simple, effective, and code-free solution that removes the need for fragile VBA workarounds.

---

You've built a great model, so stop steering it manually and let it drive itself. Moving to a native, code-free refresh means one less thing to remember and more time to get on with the things that actually matter. This shift to a more autonomous workflow is the perfect starting point for speeding up your Excel workflow , making everyday tasks flow a little more smoothly.