Yes. In Excel for Microsoft 365 on Windows, you can put a clickable shape on a worksheet and assign it a VBA macro that runs RefreshAll; clicking it refreshes the workbook’s connections, including its Power Query queries. For a button that refreshes only one query, assign a macro that calls that query’s workbook connection instead.
What a refresh button does
Power Query imports and transforms data through queries. A refresh reruns a query against its source and updates the results loaded to the workbook, subject to the query’s settings and the source being available. It does not rewrite the steps in the query editor: it runs those steps again against current source data.
Excel already provides refresh commands on the Data tab, including Data > Refresh All. A worksheet button is useful when a workbook is used as a dashboard or report and you want an obvious control beside the results. It is a shortcut to a refresh command, not a separate type of Power Query operation.
Decide what the button should refresh before creating it. A button assigned to RefreshAll can affect more than Power Query: Excel’s Refresh All method refreshes external data ranges and PivotTable reports as well as workbook connections. If the file contains other connections or reports, consider whether refreshing them all is intended.
Choose between refreshing everything and one query
Use a workbook-wide refresh when the workbook is designed to update as a unit—for example, when several queries feed related tables and a report. Use a single-query button when the workbook has independent data sources and users should update only one section. A query may depend on another query, so a refresh of a dependent query can also involve its upstream data; the query design matters.
- Refresh All: easiest to maintain and suitable for a report whose connections should be updated together.
- One query: more targeted, but requires the correct connection name and may not update unrelated queries or report elements that need a separate refresh.
- Built-in commands: best if users do not specifically need a button placed on the worksheet.
If you are unsure, start with Excel’s built-in Refresh All command and check what changes. It is better to make the scope explicit than to give a button a vague label such as “Update” when it might refresh several sources.
Add a worksheet shape as the button
A shape is a straightforward way to make a button without turning on the Developer tab. First create and save the macro described in the next section. Then add and assign the shape:
- Choose Insert > Shapes, select a rectangle or rounded rectangle, and draw it on the worksheet.
- Type a clear label such as Refresh data or Refresh all queries. Use the second label only if the macro really refreshes everything.
- Right-click the shape and choose Assign Macro.
- Select the refresh macro from the list and choose OK.
- Save the workbook, then click the shape to run the macro. If Excel displays a security warning, only enable macros when you trust the file and its source.
You can change the shape’s fill, outline, size, and position using the Shape Format tab. Keep it near the data or report it controls, and give it a descriptive label. A shape is a visual control; it does not automatically indicate whether a long refresh is still running, so users may need to check Excel’s status bar or query pane.
Use a macro for Refresh All
To create the general-purpose macro in Excel for Windows, press Alt+F11 to open the Visual Basic Editor. On a Mac, the Visual Basic Editor shortcut is generally Option+F11 (some keyboards require the Fn key); you can also open it from the Developer tab. In the editor, choose Insert > Module and paste this code into the module:
Sub RefreshPowerQueryData()
' Refresh workbook connections and external data ranges.
Dim wb As Workbook
Set wb = ThisWorkbook
wb.RefreshAll
End Sub
ThisWorkbook means the workbook containing this macro. The call to RefreshAll is convenient, but it is not limited to Power Query. Assign RefreshPowerQueryData to the worksheet shape using the steps above. If the macro does not appear in Assign Macro, confirm that it is a public procedure in a standard module, not code stored in a worksheet or ThisWorkbook object.
Target just one Power Query connection
For a single query, use its workbook connection name in place of the example name below. This example assumes the query is named Sales and its connection is listed as Query - Sales; actual names can differ. In Excel, open Data > Queries & Connections and inspect the Connections tab to identify the connection name. Then use:
Sub RefreshSalesQuery()
' Replace the sample connection name with the exact name in this workbook.
Dim wb As Workbook
Dim conn As WorkbookConnection
Set wb = ThisWorkbook
Set conn = wb.Connections("Query - Sales")
conn.Refresh
End Sub
Assign RefreshSalesQuery to a shape just as you would the Refresh All macro. If Excel reports that it cannot find the connection, check the spelling and spaces in the name shown in the Connections list. If the query has been renamed, the connection name may not match the label you expect. A single-connection macro is more precise, but it is also more dependent on the workbook’s structure than the general Refresh All macro.
Refresh timing and query loading
A click does not always mean the data has finished updating immediately. Some connections refresh in the background, allowing you to continue working while Excel retrieves data. The query may also take time because of a large source, slow network, authentication prompt, or multiple dependent queries. Avoid repeatedly clicking the button while a refresh is underway; that can start overlapping or redundant work.
Power Query results can be loaded to a worksheet table, the Data Model, or both, depending on the query’s load settings. A query configured as connection-only may not create a visible table to watch, even though it can be used by another query or by the Data Model. To review a query’s destination and settings, use the Queries & Connections pane and the query’s load options. Refreshing a query does not necessarily refresh every separate PivotTable or formula-driven report in the way a user expects, so verify the workbook’s full update sequence.
For a report where users need to know whether data is current, add context such as the source, expected refresh time, or a visible “last updated” value maintained by the workbook’s design. Do not assume the button itself proves the source returned the latest data: caching, source-side delays, credentials, and query filters can all affect what appears.
Button alternatives without a worksheet macro
If a button on the sheet is not essential, use Data > Refresh All. For a specific query, open Data > Queries & Connections, right-click the query, and choose Refresh. These built-in options avoid adding VBA and are often preferable for workbooks shared with people who cannot run macros.
You can also put a built-in command on the Quick Access Toolbar. Open its customization options, choose More Commands, set the command list to All Commands, select Refresh All, and add it. This makes Refresh All accessible from the toolbar without placing a control in the worksheet. The exact customization dialog can vary slightly with Excel’s interface, but the command itself is built in.
On Excel for the web, VBA macros cannot be created or run. The worksheet shape may still be visible, but it will not serve as a VBA refresh button there. Web refresh support also depends on the query’s data source and the capabilities available for that workbook. If users need a reliable refresh workflow in a browser, check that their specific query can refresh in Excel for the web and provide the supported built-in workflow rather than relying on a macro.
Save, share, and protect the workbook
A workbook containing VBA must be saved in a macro-enabled format, such as Excel Macro-Enabled Workbook (*.xlsm). If you save it as an ordinary .xlsx file, Excel does not retain the VBA macro. Recipients may also have macro policies that block or warn about macros, so a button that works on the author’s computer may not run for everyone.
Macros can run code, so do not enable them simply because a workbook displays a button or a security prompt. Use a trusted, approved workbook and follow your organization’s security rules. Before running a new or modified macro, save a copy of the workbook. VBA actions cannot be undone with Ctrl+Z; a refresh can also replace query output with new source data, so preserving a recoverable copy is sensible.
On Excel for Mac, VBA is supported, but the Developer tab and control menus can differ from Windows. A worksheet shape assigned to a macro is generally a simpler cross-platform design than relying on a particular form control. Test the saved workbook in the Excel versions your readers actually use, especially if the file will be shared through OneDrive or opened in a browser.
Common problems and practical checks
- The shape does nothing: confirm that the correct macro is assigned, macros are permitted, and the workbook is saved as
.xlsm. - The macro is missing: check that it is in a standard module and that it is not marked
Private. - The query does not update: try the query’s built-in Refresh command and look for a credentials, privacy, source-path, or network error in the Queries & Connections pane.
- The wrong data changes: inspect the macro.
RefreshAllrefreshes more than one query when other refreshable connections or external data ranges exist. - The button seems stuck: allow time for background refresh to finish and check Excel’s status or query pane before clicking again.
Also check the query’s own properties and source access. For example, a query that reads a local file can fail on another person’s computer if the file path is different, even though the macro is assigned correctly. A button triggers a refresh; it cannot repair a broken path, grant credentials, or make an unavailable source accessible.
Frequently Asked Questions
Can I add a Power Query refresh button without VBA?
Not as a worksheet shape that directly runs a refresh command through the method described here. Use Data > Refresh All, right-click a query in Queries & Connections and choose Refresh, or add Refresh All to the Quick Access Toolbar if you want a convenient built-in control.
Does Refresh All refresh only Power Query?
No. Excel’s Refresh All command refreshes workbook connections and external data ranges, and can refresh PivotTable reports. Use a connection-specific macro when you need a narrower scope, and confirm its exact connection name first.
Can one button refresh just one query?
Yes. Assign a macro that calls Refresh on that query’s workbook connection. Replace the sample connection name in the code with the exact name shown in the workbook’s Connections list.
Will the button work in Excel for the web?
A VBA-assigned shape will not run its macro in Excel for the web. Use Excel’s supported web refresh options if the query and source support them, or open the macro-enabled workbook in desktop Excel.
Why does my query button ask me to enable macros?
The worksheet button relies on VBA, and Excel may block macros from untrusted files. Only enable them for a workbook and source you trust, and follow your organization’s security policy.
Related
- Combine Excel Spreadsheets: Easy Methods for Your Data
- Dynamically Resize an Excel Table as You Add New Data
- Dynamic Dependent Dropdowns in Excel: Setup and Tips
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Indent in Microsoft Excel: Cells, Text, and ListsTo indent content in Excel, select the cell or range, then choose Home > Increase Indent in the…
- Weekly Expense Tracker Spreadsheet: Build One in ExcelA weekly expense tracker spreadsheet is a simple Excel worksheet for recording purchases by date and category, then…
- How to Clear Cache in Outlook CalendarTo clear the local cache for a calendar in classic Outlook for Windows, right-click the calendar, choose Properties,…
- Excel Shortcut Keys for Fill Color: Windows and MacIn Excel for Microsoft 365 on Windows, press Alt, then H, then H to open the Fill Color…
- Excel AutoFilter Dates Before TodayTo show dates earlier than today in Excel, select your data and choose Data > Filter, then open…
- Excel PivotTable Dates: How to Ungroup Them EasilyTo ungroup dates in an Excel PivotTable, click a grouped date label, then choose PivotTable Analyze > Ungroup…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.