Excel Macro Refresh All: How to Update Workbook Data

VBA & macros · Updated November 14, 2026

In Excel for Microsoft 365 on Windows, use the VBA method ThisWorkbook.RefreshAll to request a refresh of the workbook’s refreshable external data connections and PivotTable reports. Run it from a macro, or use Data > Refresh All when you do not need automation; note that some background refreshes may continue after the macro call returns.

What Refresh All means in Excel

Refresh All is a command to update data brought into a workbook from supported external sources. Depending on the workbook, that can include Power Query results, other workbook data connections, and PivotTables connected to external data or a PivotTable cache. It is useful when a report combines several sources and you want to request updates without refreshing each query or report one at a time.

It does not mean “update every part of the file.” It does not repair a broken connection, supply missing credentials, or guarantee that a source has newer data. It is also different from recalculating formulas: Excel normally recalculates dependent formulas according to its calculation settings, but Refresh All is primarily about refreshing data connections and reports. If a workbook uses manual calculation, formula results may need a separate calculation command.

Refresh All also should not be confused with updating links to other workbooks. Workbook links have their own update controls, and their behavior depends on the link and security settings. If a value comes from a linked file rather than a query or connection, check the workbook-link controls as well as the refresh command.

Add and run a Refresh All macro

For a workbook-level macro, use ThisWorkbook so the command targets the file that contains the code. In Excel for Microsoft 365 on Windows, open the VBA editor with Alt+F11, choose Insert > Module, and paste in the procedure below. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon and select Developer.

Option Explicit

Public Sub RefreshAllWorkbookData()
    Dim wb As Workbook

    Set wb = ThisWorkbook
    On Error GoTo RefreshError

    ' Requests a refresh for the workbook's refreshable data.
    wb.RefreshAll
    Exit Sub

RefreshError:
    MsgBox "Refresh request failed: " & Err.Description, vbExclamation
End Sub

Save a copy of the workbook before running a macro. VBA actions generally cannot be undone with Ctrl+Z, and a refresh can replace values in tables or reports. Save a macro-enabled workbook as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain VBA code. To run the procedure, use Developer > Macros, select RefreshAllWorkbookData, and choose Run. Alt+F8 opens the macro list on Windows.

The error handler displays an error if Excel raises one while making the request. It does not certify that every source returned current data: a connection can fail later, require credentials, or complete asynchronously. Check the refresh status and resulting data when the workbook’s accuracy matters.

What the macro can refresh

The exact scope depends on the workbook’s connections and the type of source. A workbook may contain queries, connection-only queries, tables loaded from queries, PivotTables, or a mixture of these. Refresh All requests updates across refreshable items; it does not create a connection to a source that is absent from the file.

  • Power Query: Queries that load data to a worksheet or the Data Model can be refreshed through the workbook refresh command, subject to the query’s source, credentials, and connection settings.
  • External data connections: Connections may reach databases, text files, web sources, or other supported providers. Availability and authentication depend on the connection and installed provider.
  • PivotTables: PivotTables can refresh from their source or cache. A PivotTable based on a local range reflects the range’s current contents after it is refreshed; one backed by an external source also depends on that source being reachable.
  • Formulas: Formulas that refer to refreshed cells usually update under automatic calculation. Formula recalculation is a related consequence, not a substitute for refreshing the source.

A workbook can contain items that are not refreshable, are disabled, or are configured to refresh under separate conditions. For example, a query with expired credentials may need an interactive sign-in. Refresh All cannot bypass source permissions or an organization’s access controls.

Background refresh and knowing when it is finished

Some connections refresh in the background. In that case, RefreshAll starts the refresh process but can return control to VBA before every result is ready. A macro that immediately reads a query output, copies it elsewhere, or sends a completion message may therefore act too early. This timing issue is a frequent reason a refresh macro appears to work while a later step uses old data.

Excel’s Application.CalculateUntilAsyncQueriesDone method waits for pending asynchronous OLE DB and OLAP queries. It can help in workflows that use those query types, but it is not a universal completion test for every connector or background operation. Do not assume that adding it guarantees completion for every Power Query source or custom connection. For a dependable sequence, identify the connection type and use a completion method appropriate to it, or configure the connection to run without background refresh where that option is available.

To inspect connection options, open Data > Queries & Connections, then inspect the relevant query or connection’s properties. The available settings vary by connection type and Excel build. Some connection properties include options for background refresh or refreshing data when the file opens. Changing those settings affects future refresh behavior; it does not fix a source that is offline or inaccessible.

Macro versus the Refresh All button

For a one-time update, the built-in command is simpler and avoids adding executable code. On Windows, choose Data > Refresh All or press Ctrl+Alt+F5. A macro is useful when refresh is one step in a repeatable process—for example, updating a monthly report before exporting it, or providing a button that refreshes a workbook’s data for other users.

A macro does not make data fresher than the built-in command. Its value is automation, consistency, and the ability to combine actions. If you add later steps, such as saving or exporting, account for asynchronous refresh and failures before those steps run. A single Refresh All call is not proof that all source data successfully updated.

Common refresh problems and checks

If the macro runs but the displayed data does not change, first separate a VBA problem from a connection problem. Run Data > Refresh All manually and look for prompts, status messages, or errors. Then check whether the source itself has changed and whether the query is loading to a location you are viewing.

  • Credentials or permissions: Sign in with an account that has access to the source. A macro cannot legitimately bypass a password, authentication prompt, or permission requirement.
  • Unavailable source: Confirm the database, file path, network location, or web service is reachable from the computer running Excel. Paths that work for one user may not work for another.
  • Stale PivotTable: Check that the PivotTable uses the expected source or cache and refresh the relevant report. If a query feeds a PivotTable, verify both the query output and the report after refresh.
  • Refresh still running: Look for an active refresh in Queries & Connections. A macro’s next action may have started before a background query finished.
  • Macro did not run: Confirm the file is saved as .xlsm, the procedure is in a standard module, and macros are permitted by your organization’s security policy.
  • Unexpected old results: Check query filters, parameters, source-side caching, and the workbook’s calculation mode. A successful refresh can still return the same result if the source data or query conditions have not changed.

For repeatable reporting, record which connection failed and when, rather than relying only on a generic “done” message. If the workbook will be used by other people, document its data sources and the credentials or access they need. This makes it easier to distinguish an Excel issue from a source-system issue.

Security and safe use

Macros can change workbook content and perform other actions, so only run code from a source you trust. Do not enable all macros globally just to make one workbook work. Follow your organization’s policy, and use a trusted or digitally signed macro when required. A refresh can also send a request to an external service using the workbook’s configured connection, so consider the source and data-sharing implications before running it.

Keep a clean backup before modifying a macro or refreshing a workbook that contains important outputs. Test changes on a copy, particularly if the refresh overwrites a worksheet table or if later VBA steps save, delete, or export data. A refresh request itself is not a substitute for a backup or a review of the resulting report.

Windows, Mac, and Excel for the web

The VBA example targets desktop Excel for Microsoft 365 on Windows. Excel for Mac also supports VBA, but some external data sources, providers, and connection features differ from Windows. On a Mac, use the Developer tab’s Macros command to run the procedure; the exact keyboard shortcut can depend on the keyboard and Excel configuration. The Data tab’s refresh controls are available where the connection type is supported.

Excel for the web does not run VBA macros. A workbook containing VBA can be opened in the web app, but the macro must be run in desktop Excel. Excel for the web has its own data-refresh capabilities and limitations, which depend on the source; they are not a way to execute this VBA procedure. If users must automate in a browser, evaluate Office Scripts and the supported connectors for that workflow rather than assuming a VBA macro will transfer unchanged.

Frequently Asked Questions

What is the VBA code for Refresh All in Excel?
Use ThisWorkbook.RefreshAll inside a VBA procedure to request a refresh of the current macro-containing workbook’s refreshable data connections and PivotTables.

Does RefreshAll wait until every query finishes?
Not necessarily. Background refresh can continue after the method returns. The appropriate way to wait depends on the connection type; Excel’s asynchronous-query wait method covers pending OLE DB and OLAP queries, not every possible connector.

Does Refresh All recalculate formulas?
It refreshes data sources and reports, while formula recalculation follows Excel’s calculation settings. With automatic calculation, formulas that depend on refreshed values normally recalculate; manual calculation may require a separate calculation command.

Can an Excel macro refresh data in a closed workbook?
A VBA macro must run in desktop Excel with the workbook open. A separate automation process may open and refresh a file, but that requires an appropriate Excel or cloud automation setup and source access.

Can I run a Refresh All macro in Excel for the web?
No. Excel for the web does not execute VBA. Open the workbook in desktop Excel to run the macro, or investigate a supported browser-based automation approach for the data sources involved.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.