To make external data in Excel automatically update, you need to enable refresh when the file is opened or set a timed refresh. 1. Enable refresh when file opening: Check "Open file when refresh data" in the connection properties to ensure that the data is automatically updated every time the workbook is opened. 2. Set periodic refresh interval: Set "Every Time" to refresh the number of minutes in the connection properties, which is only valid when the workbook is opened. 3. Use VBA events to trigger refresh: By writing VBA code, you can automatically refresh every 10 minutes and other custom times, and start the process when the workbook is opened. 4. Use Power Query data model: It is recommended that modern Excel users use Power Query and enable background refresh, combined with cloud services, you can achieve timed refresh in shutdown state. Notes include saving credentials, handling errors, selecting appropriate file formats, and optimizing refresh performance. After proper configuration, Excel reports can be automatically kept up to date without manual intervention.

If you're pulling data into Excel from an external source—like a database, web query, text file, or another workbook—you probably want that data to stay up to date without manually refreshing every time. Fortunately, Excel offers several ways to automatically refresh external data . Here's how to set it up.

1. Enable Automatic Refresh When Opening the File

This is the simplest method: Excel refreshes the data every time you open the workbook.

Steps:

• Select any cell in your data table connected to the external source.
• Go to the Data tab.
• Click Queries & Connections (or Connections in older versions).
• In the Workbook Queries or Connections pane, right-click your query or connection.
• Choose Properties (or Connection Properties ).
• Check the box for "Refresh data when opening the file" .
• Click OK .

? This ensures your data is currently every time you open the workbook—ideal for daily reports or dashboards.

2. Set a Regular Refresh Interval (Manual Refresh on a Timer)

Want Excel to refresh every few minutes or hours while the file is open? You can schedule periodic refreshes.

Steps:

• Go to the Data tab.
• Click Queries & Connections .
• Right-click your query → Properties .
• Check "Refresh every" and enter the number of minutes (eg, 5, 30, 60).
• Optionally, check "Refresh background" so Excel remains usable during refresh.
• Click OK .

?? Note: This only works while the workbook is open. If Excel is closed, the timer stops.

3. Use VBA to Trigger Refresh on Events (Advanced)

You can use Excel VBA to automate refreshes based on events like worksheet changes, time of day, or user actions.

Example: Auto-refresh every 10 minutes while Excel is open

1. Press ALT F11 to open the VBA editor.
2. Insert a new module or use ThisWorkbook .
3. Paste this code:

 Sub StartAutoRefresh()
    Application.OnTime Now TimeValue("00:10:00"), "RefreshData"
End Sub

Sub RefreshData()
    ThisWorkbook.RefreshAll
    StartAutoRefresh ' Reschedule the next refresh
End Sub

Sub StopAutoRefresh()
    On Error Resume Next
    Application.OnTime EarliestTime:=Now, Procedure:="RefreshData", Schedule:=False
End Sub

4. To start, run StartAutoRefresh . To stop, run StopAutoRefresh .

? Tip: Call StartAutoRefresh when the workbook opens by adding this to the ThisWorkbook module:

 Private Sub Workbook_Open()
    StartAutoRefresh
End Sub

This method gives you full control but requires macros to be enabled.

4. Use Power Query Data Model (Recommended for Modern Excel)

If you're using Power Query (Get & Transform Data), you can enhance automation:

• After loading data via Data > Get Data , go to Query Options .
• Under Data Load , ensure "Enable background refresh" is checked.
• Combine with "Refresh when opening" or scheduled refresh if using Power BI or Excel Services (in enterprise environments).

? Bonus: In Power BI or SharePoint Online , you can set scheduled refreshes in the cloud—even when your computer is off.

Things to Keep in Mind

• ? Credentials : External sources may require saved login info. Set this in Query Options > Data Source Settings .
• ? Errors : If the source is unavailable, automatic refresh may fail. Test reliability first.
• ? File Format : Save as .xlsx or .xlsm (if using macros). Some features require .xlsb for large datasets.
• ?? Performance : Frequent refreshes of large data can slow Excel. Use background refresh when possible.

Basically, automatic refresh in Excel comes down to:
? Turn on refresh at open
? Set a time interval
? Or use VBA for custom automation

With the right setup, your Excel reports can stay current with minimal effort.

The above is the detailed content of How to automatically refresh data in Excel from an external source. For more information, please follow other related articles on the PHP Chinese website!