To add a button on an Excel worksheet that refreshes a PivotTable, insert a shape, assign it a short VBA macro, and save the workbook as an Excel Macro-Enabled Workbook (.xlsm). For a button that refreshes just one PivotTable, the macro can call that table’s RefreshTable method; if you only need a quick command, Excel already provides Refresh on the PivotTable Analyze tab.
What a PivotTable refresh does
A PivotTable summarizes data from a source range, an Excel table, or an external connection. Refreshing asks Excel to reread that source and update the PivotTable’s cache and displayed summary. This can bring in new rows, reflect changed values, and update groups or totals, provided the source itself includes the relevant data.
A refresh button does not automatically expand every kind of source range. If a PivotTable uses a fixed range such as A1:D500, adding records below row 500 may leave them out. Converting the source data to an Excel table before creating the PivotTable is often a better setup: tables generally expand when you add adjacent rows, and a refresh can then include those records. A refresh also does not fix incorrect source data, change a field’s meaning, or rebuild a PivotTable with a different layout.
Use Excel’s built-in Refresh command first
If a permanent worksheet control is not essential, use the command Excel already provides. Click inside the PivotTable and select PivotTable Analyze > Refresh. The contextual PivotTable Analyze tab appears when a PivotTable cell is selected. You can also right-click inside the table and choose Refresh.
On Windows, Alt+F5 refreshes the selected PivotTable. Ctrl+Alt+F5 runs Refresh All, which refreshes workbook connections and PivotTables. Keyboard shortcuts can vary with keyboard layout and Excel configuration; the ribbon and context-menu commands remain available. On Mac, use the PivotTable Analyze ribbon or the right-click menu; Windows Alt shortcuts do not map directly to the Mac keyboard. These built-in commands need no VBA and work in macro-free workbooks.
Check the PivotTable name and source
Before writing a macro, identify the exact worksheet and PivotTable it should target. Click inside the PivotTable and look at the PivotTable Name box on the PivotTable Analyze tab. Names such as PivotTable1 are common defaults, but a workbook may have been renamed or may contain several tables. You can give it a descriptive name, such as SalesByRegion, in that box.
Also confirm the worksheet tab name and the source definition. The example macro below expects a sheet named Report and a PivotTable named PivotTable1; change those strings to match your workbook. Names in VBA must match the actual sheet and PivotTable names, including spaces and punctuation. If the source is a query or an external connection, check that it is available and that its refresh permissions or credentials are in place.
Add a worksheet button with a shape
A shape is an easy worksheet button because it can be formatted and labeled like other report elements. In Excel for Microsoft 365 on Windows:
- Choose Insert > Shapes, then select a rectangle or another suitable shape.
- Drag on the worksheet to draw it near the PivotTable. Type a label such as Refresh sales summary.
- Right-click the shape and choose Assign Macro.
- Select the macro you will create, then choose OK.
If you have not created the macro yet, you can draw the shape first, then assign it after the code is ready. To change its appearance, use the Shape Format tab; the label can explain what the button refreshes, especially if a report contains more than one PivotTable. The shape is only a trigger: the code assigned to it determines what refreshes.
Another option is a Form Control button: enable the Developer tab through File > Options > Customize Ribbon if it is hidden, then choose Developer > Insert > Button (Form Control), draw the button, and select a macro in the Assign Macro dialog. Avoid ActiveX controls for a simple refresh button; they add complexity and are not supported in Excel for Mac.
Create and assign the VBA macro
On Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the following code into the module. Replace Report and PivotTable1 with your actual names. The macro uses ThisWorkbook, meaning the workbook that contains the code, and refreshes only the specified PivotTable.
Sub RefreshSalesPivot()
Dim ws As Worksheet
Dim pt As PivotTable
' Change these names to match your workbook.
Set ws = ThisWorkbook.Worksheets("Report")
Set pt = ws.PivotTables("PivotTable1")
pt.RefreshTable
End Sub
Save the file as an Excel Macro-Enabled Workbook (*.xlsm), rather than .xlsx, so the VBA code is retained. Return to the worksheet, right-click the shape, choose Assign Macro, select RefreshSalesPivot, and click OK. Click outside the selection handles, then click the button to run it. Save a copy of the workbook before running a macro: macro actions cannot be undone with Ctrl+Z.
On Mac, Excel’s desktop app supports VBA, though the editor and ribbon interactions differ. Open the Visual Basic Editor from the Developer tab or Excel’s Tools > Macro menu, add a module, and use the same code after confirming the sheet and PivotTable names. A function-key shortcut may require the Fn key depending on the Mac keyboard. Assign the macro to a shape or supported Form Control using the Mac interface; do not rely on ActiveX controls.
Choose one PivotTable or refresh the whole workbook
A button assigned to the sample macro is deliberately narrow: it refreshes one named table. That is useful on a dashboard where users should update a specific report without also starting unrelated connection refreshes. If several PivotTables share a source or cache, refreshing one can also affect other reports that use that shared cache; design and test the workbook accordingly.
For a workbook-wide update, use Data > Refresh All rather than a one-table macro. You can also create and assign a macro that calls the workbook’s Refresh All method, but that may refresh connections and queries as well as PivotTables. Some external or query refreshes can continue in the background, so a button click does not always mean every result has finished updating immediately. For a report with a single PivotTable, the direct RefreshTable approach is usually clearer about scope.
If you want a persistent command outside the worksheet, Excel’s Quick Access Toolbar is another choice. You can add commands there for repeated use, but the toolbar is part of the Excel interface rather than a clearly labeled control embedded beside a particular report. Choose a worksheet shape when the button needs to travel with the report layout.
Common refresh-button problems
- The button does nothing: confirm that the correct macro is assigned, the workbook is saved as .xlsm, and macros are permitted by your organization’s security settings. A shape may also be in editing mode; click away from its handles before using it.
- Excel reports that it cannot find the PivotTable: check the worksheet and PivotTable names in the code. Renaming a sheet or table after creating the macro means the code may need updating.
- New rows do not appear: inspect the PivotTable’s source range. If the range is fixed and does not include the new rows, refreshing cannot add them. Consider using an Excel table as the source, or update the source through PivotTable Analyze > Change Data Source.
- Values remain unchanged: check whether the source data itself has been updated and whether an external connection has completed. For connection-backed data, a PivotTable refresh and a query refresh are related but not always identical operations; Refresh All may be needed.
- The macro works on one computer but not another: the second computer may have macros disabled, may use different sheet or PivotTable names, or may be opening the workbook in Excel for the web. VBA macros cannot be created, edited, or run in Excel for the web; open the file in desktop Excel.
Security and automatic refresh options
VBA buttons require a macro-enabled file and a security decision from the person opening it. Do not enable macros in an unfamiliar workbook merely to make a button work. Follow your organization’s policy, and use a trusted, signed, or otherwise approved workbook when required. If the file will be shared with people who cannot use macros, include instructions to select the PivotTable and use the built-in Refresh command instead.
If the goal is to have updated data whenever someone opens the workbook, a button may not be necessary. Select a PivotTable, open PivotTable Analyze > Options, and look on the Data tab for Refresh data when opening the file. The setting applies to that PivotTable; it does not guarantee that every external source will refresh successfully, because access, credentials, network availability, and connection settings can still matter. A manual button is more explicit when users need to choose when a report updates.
Frequently Asked Questions
Can I add a PivotTable refresh button without VBA?
Not as a custom worksheet shape that runs Excel’s refresh command. You can use the built-in PivotTable Analyze > Refresh command, the right-click Refresh command, or a ribbon/toolbar command without VBA. A clickable worksheet shape assigned to a refresh action generally requires a macro.
Can one button refresh every PivotTable?
Yes. You can assign a macro that refreshes all PivotTables, or use Data > Refresh All for a workbook-wide refresh. Refresh All may also update queries and other connections, so it can take longer and do more than refreshing one report.
Will refreshing include rows I just added?
Only if the PivotTable’s source includes those rows. A fixed source range may need to be expanded; an Excel table is often easier to maintain as records are appended. After fixing the source, refresh the PivotTable.
Can Excel refresh a PivotTable automatically?
A PivotTable can be set to refresh when its workbook opens using the option on the PivotTable Options dialog’s Data tab. This is different from refreshing continuously as source cells change, and external connections may still require access or additional refresh steps.
Does the button work in Excel for the web?
Excel for the web does not run VBA macros, so a shape assigned to a VBA refresh macro will not operate there. Open the workbook in desktop Excel to run the macro, or use the web app’s available refresh controls for the PivotTable or connected data.
Related
- Elton John at Xcel Energy Center: Date and Details
- Elton John Xcel Energy Center Parking: What to Know
- Editing a Group of Sheets in Excel: Steps and Limits
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Radar Chart in Google Sheets: How to Create and Read OneTo make a radar chart in Google Sheets, put category names in the first column and each data…
- Excel Pivot Table Calculated Fields: Formulas ExplainedTo add a calculated field in an Excel pivot table, go to PivotTable Analyze > Fields, Items &…
- Progress Doughnut Chart in Excel: Build and Format OneTo make a progress doughnut chart in Excel for Microsoft 365 on Windows, enter a completed percentage and…
- Convert an Excel List to an Org Chart in Microsoft 365To convert an Excel list to an org chart, arrange the people in a reporting hierarchy, then use…
- Traffic Light Chart in Excel: Build a Status DashboardA traffic light chart in Excel is a visual status indicator that uses green, yellow, and red symbols…
- Excel Trendline vs. Linear Forecast: Key DifferencesAn Excel linear trendline draws a best-fit straight line on a chart; FORECAST.LINEAR calculates a predicted value in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.