You may have an Excel workbook that pulls data from another file, a database, or the web. When the source data changes, you need to decide when your workbook updates. This article explains the difference between automatic and manual refresh modes for linked data. You will learn how to set your preferred update method for different data connection types.
Key Takeaways: Controlling Data Refresh in Excel
- Data > Queries & Connections > Refresh All dropdown: Choose to refresh all connections now or set them to refresh automatically when opening the file.
- Data > Properties dialog box: Controls refresh behavior for a single selected query or connection, including background refresh and frequency.
- File > Options > Advanced > Workbook Calculation: Manually recalculating formulas with F9 can update some types of workbook links.
Understanding Data Connections and Refresh Modes
Excel can connect to external data sources like other workbooks, SQL databases, or web pages. These connections are often managed as Queries through Power Query. Each connection has properties that determine how it gets new data. The two primary modes are automatic and manual refresh. Automatic refresh can happen when you open the file or at set intervals. Manual refresh requires you to initiate the update. Your choice depends on data size, source stability, and performance needs. Large datasets or slow sources often work better with manual refresh to prevent delays.
Types of Linked Data
Links to other Excel workbooks use simple cell formulas. These update based on your workbook calculation settings. Data imported via Power Query or from databases uses a different connection framework. These connections offer more control over refresh timing and behavior. Web queries also fall into this category. It is important to know which type of link you have, as the control methods differ.
Steps to Set Automatic or Manual Refresh
The main control center for refresh settings is the Queries & Connections pane. Use these steps to configure your data updates.
- Open the Queries & Connections pane
Go to the Data tab on the ribbon. In the Queries & Connections group, click the Queries & Connections button. A pane will open on the right side of your window listing all connections. - Access connection properties
In the Queries & Connections pane, right-click on the name of the query or connection you want to configure. From the context menu, select Properties. The Connection Properties dialog box will appear. - Configure the refresh control
In the Connection Properties dialog, look at the Usage tab. You will see key checkboxes. Check “Refresh data when opening the file” for automatic updates on open. Uncheck it to force manual refresh only. You can also check “Refresh every X minutes” to set an interval for automatic updates while the workbook is open. - Apply and test the settings
Click OK to save the properties. To manually refresh this single connection, right-click its name in the pane again and select Refresh. To refresh all connections at once, go to the Data tab and click the Refresh All button.
Controlling Refresh for Workbook Links
For simple links to other Excel files, the control is different. These links update based on calculation settings.
- Open the workbook with links
Open the destination workbook that contains formulas linking to other Excel files. - Navigate to link settings
Go to the Data tab. In the Queries & Connections group, click the Edit Links button. If the button is grayed out, your workbook has no editable external links. - Set the update method
In the Edit Links dialog, select a source from the list. Click the “Startup Prompt” button. Choose “Don’t display the alert and don’t update automatic links” to enforce manual updates. Choose the option to update links on open for automatic behavior. Click OK to confirm.
Common Mistakes and Limitations
Background Refresh Causes Slow Performance
The Connection Properties dialog has a “Enable background refresh” option. This lets you work in Excel while data updates. For very large queries, this can still make Excel slow or unresponsive. If you experience lag, try disabling background refresh. The update will then run in the foreground, locking Excel until it completes, but it may be more stable.
Formulas Not Updating After a Data Refresh
Refreshing a connection imports new raw data. Any formulas in your workbook that use that data may need to recalculate. If you have calculation set to Manual, the formulas will show old results. Press F9 to force a full workbook calculation after a data refresh. You can set calculation to Automatic in Formulas > Calculation Options.
Missing Data After Refresh
If your refreshed table has fewer rows, check the source. The connection gets the data currently at the source. If the source query or filter changed, your results will change. This is not an error in refresh settings. Review the Power Query steps by right-clicking the query and selecting Edit.
Automatic Refresh vs Manual Refresh: Key Differences
| Item | Automatic Refresh | Manual Refresh |
|---|---|---|
| Update Trigger | On file open or at timed intervals | User clicks Refresh or presses a shortcut |
| Performance Impact | Can cause slower workbook opening | No delay on open; user controls when system is busy |
| Data Freshness | Ensures data is current at open or regularly | Risk of working with outdated information |
| Best For | Small datasets, stable sources, reports needing latest data | Large queries, volatile sources, shared workbooks |
| Control Location | Connection Properties dialog, Usage tab | Data tab ribbon or Queries & Connections pane |
You can now choose when your Excel workbook updates its linked information. Use automatic refresh for dashboards that must show current data at the start of a meeting. Use manual refresh for large models where you want to control processing time. For advanced control, explore the Data > Get & Transform Data group to edit Power Query steps and transform data before it enters your sheet.