totn Excel

MS Excel 2011 for Mac: Refresh multiple pivot tables with a button

This Excel tutorial explains how to refresh multiple pivot tables with the click on a button in Excel 2011 for Mac (with screenshots and step-by-step instructions).

Question: In Microsoft Excel 2011 for Mac, is it possible to create a button that will refresh/update multiple pivot tables?

Answer: Yes, you can refresh multiple pivot tables with a button. To do this:

Select the Developer tab from the toolbar at the top of the screen (follow these instructions if the Developer tab is hidden). In the Form Controls group, click on Button and click on the button icon (currently highlighted in picture below) under Form Controls from the popup menu.

Then click on your spreadsheet where you would like the button to appear.

Microsoft Excel

After creating the button, the Assign Macro window should appear. Click on the "New" button.

Microsoft Excel

Then paste in similar code as below in the Button_Click event:

Sheets("Sheet1").Select
ActiveSheet.PivotTables("PivotTable1").RefreshTable
Sheets("Sheet2").Select
ActiveSheet.PivotTables("PivotTable2").RefreshTable

Microsoft Excel

You will need to replace the Sheet1 and Sheet2 with the names of your sheets and PivotTable1 and PivotTable2 with the names of your pivot tables.

To find out the name of a pivot table, right-click on the pivot table and select PivotTable Options from the popup menu.

Microsoft Excel

When the PivotTable Options window appears, the Name is displayed at the top of the window. In this example, the pivot table is called PivotTable2.

Microsoft Excel