Create a PivotTable Shortcut With an Excel Macro Button

Key Takeaways

• You can record an Excel PivotTable macro to automate creation and apply consistent formatting without writing VBA code.

• Converting source data into a named Excel Table ensures your PivotTable macro works reliably regardless of how many rows you add.

• Excel offers three ways to add a macro button to your worksheets: the Quick Access Toolbar, a Form Control button or an ActiveX Control button.

• Storing your macro in the Personal Macro Workbook makes it available across all your Excel files.

Why Use Macros to Automate PivotTables

A macro is a recorded sequence of actions in Excel that you can replay with a single click. If you've ever repeated the same actions multiple times in a spreadsheet, you'll benefit from recording a macro to automate those tasks. When it comes to PivotTables, macros are especially valuable because they eliminate the repetitive formatting clicks that come with building reports from scratch each time.

Think about how often you create a PivotTable and then immediately change the subtotals, apply a corporate style or adjust the grand total settings. Those extra clicks add up. An Excel PivotTable macro captures all of those layout preferences once so you can automate PivotTable creation in Excel and get a consistently formatted report every time.

In this guide, you will learn how to create a PivotTable macro by recording your actions (no VBA coding required) and then explore three quick ways to add a macro button to your worksheets so you can run your macro with a single click.

Before You Begin: Prerequisites

Before you start recording your macro, make sure you have the following in place:

• Excel for Windows: The steps in this guide apply to Excel for Microsoft 365, Excel 2021 and Excel 2019. Mac versions of Excel have limited macro support.

• The Developer tab enabled: You will need the Developer tab on your ribbon to record macros and insert buttons. Instructions for enabling it are included in the guide below.

• Source data organized in rows and columns: Your data should have clear column headers in the first row with no blank rows or columns in the middle of your dataset.

Create a PivotTable Macro

Unless you are turning the exact same format and type of data into PivotTables over and over, it might not make sense to record a macro that only works against one spreadsheet. However, there are some ways a macro can be really helpful when adjusting your PivotTables to get them to look consistent and useful. Here are two tips to make creating and editing PivotTables easier.

Turn Your Data Into a Table

If not already, turn your data into a table instead of raw data (and give the table a name in the Properties group on the Design tab). Tables offer you the great benefit of organizing information in a way that is easily updatable. For example, if you have created a Chart based on a table and then add rows of information to the table, the chart will automatically refresh, as it has done in example A.

In example B, the same two highlighted rows of information were added to the data, but the chart did not refresh. You would have to click the Select Data button and manually add the new rows to the data range.

The same convenience applies to PivotTables. If your information is in a Table, select the Table name when you are creating it and then you won't have to constantly change the Table's range when you add rows to the data.

Determine Your Common Settings

Do you always turn off Subtotals? Do you have a custom, corporate PivotTable Style that you always select each time you create a new PivotTable?

To take advantage of macros and still leave yourself enough flexibility to use the macro on different sets and layouts of data, think through all those extra clicks you perform for almost every PivotTable you create. Then, to create your time-saving PivotTable Macro follow these steps:

1. Put your mouse cursor inside the data table before you record the macro.

2. Start the macro recording

HOT TIP! If you want the macro to be available in other worksheets, choose Personal Macro Workbook for the store destination.

3. Click Insert > PivotTable

4. Make any of your usual changes to the Create PivotTable dialog box, such as changing where it will be placed.

Note that because your data is in a table, and that you clicked inside the table before you created the PivotTable, the Table/Range defaults to the data you want, even if the area is different each time you run the macro.

5. Click OK. Excel opens the PivotTable Fields pane and puts a placeholder Report in the place you defined in the Create PivotTable dialog box.

6. BEFORE you choose fields and create your report, make the other layout changes you would like captured in your macro, first. These changes might include:

   • Changing Subtotal and Grand Total defaults

   • Turning Row and Column headers off or on

   • Choosing a PivotTable style

7. Once all of your layout changes have been captured, STOP the macro.

Now you can create your report to meet the specific needs of the data, but those common annoying clicks will be handled in a flash the next time you are ready to create a new PivotTable. Just click anywhere inside the table that contains your data, and then run your macro.

AND, because your source data is in a table, just add new rows as needed to your data, hit Refresh on your PivotTable and the new information will automatically appear.

Add a Macro Button to Your Worksheets

Once you have your PivotTable macro, how do you access it?

The easiest way is to add a macro button to your spreadsheet, for which Excel offers three straightforward methods:

• Add a button to the Quick Access Toolbar;

• Add a form control button; or

• Add an ActiveX control button.

In this example, we'll add a button for the macro titled "CreatePivotTable" since we just created that macro. Before you can use any of these methods, however, you must have the Developer tab available on the ribbon.

Show the Developer Tab

Look at the far right of your Excel ribbon. If there is no "Developer" tab, then add it:

1. From the "File" menu, choose "Options."

2. Choose "Customize the Ribbon."

3. Click the checkbox for "Developer."

Add a Button to the Quick Access Toolbar

The simplest way to add a macro shortcut is to add it to the Quick Access toolbar, which is displayed in the Excel window title bar.

1. Right-click a blank area of the ribbon.

2. Choose "Customize Quick Access Toolbar."

3. From the dialog box that appears, choose "Macros" for "Choose commands from:" and select your macro from the list.

4. Click "Add >>" and "Save."

Although this is the easiest method, it is also the least flexible. The shortcut can't appear anywhere except in the Quick Access Toolbar, and it can do nothing but run a macro.

Add a Form Control Button

More flexible than the Quick Access toolbar, a form control button can go anywhere in your workbook, and you control the position, size, text, and style.

1. Choose the Developer ribbon.

2. Choose Insert > Button (Form Control)

3. Click and drag a rectangle for the button.

4. When you release your mouse button, a macro dialog box will appear.

5. Click the name of the macro that you wish to run when the user presses the button, then choose "OK."

6. Excel will draw the button with the name "Button 1,2" etc. To edit the button text, right-click on the button and select Edit Text.

Add an ActiveX Control Button

An ActiveX control button works similarly to a Form Control button but gives you additional formatting options and event-driven programming capabilities.

1. Choose the Developer ribbon.

2. Choose Insert > Command Button (ActiveX Control).

3. Click and drag a rectangle on your worksheet to draw the button.

4. Double-click the button to open the VBA editor. Excel will create a Click event procedure for the button automatically.

5. Between the Private Sub and End Sub lines, type the following to call your macro:

   Call CreatePivotTable

   Replace "CreatePivotTable" with the actual name of your macro.

6. Close the VBA editor and return to your worksheet.

7. On the Developer ribbon, click Design Mode to toggle it off. Your button is now active and will run the assigned macro when clicked.

ActiveX buttons offer more control over appearance and behavior than Form Control buttons, but they only work on Windows and may be blocked by some organization security policies. For most users, a Form Control button is the simpler and more reliable choice.

Built-in PivotTable Keyboard Shortcuts

In addition to macros, Excel includes several built-in keyboard shortcuts for common PivotTable tasks. These PivotTable keyboard shortcuts can save you time even when you are not running a macro.

Keep these PivotTable shortcuts handy alongside your recorded macros for the fastest possible workflow.

Troubleshooting Common PivotTable Macro Issues

If your PivotTable macro is not behaving as expected, check these common issues:

• Macro does not run: Make sure the Developer tab is enabled and that your macro security settings allow macros to execute. Go to File > Options > Trust Center > Trust Center Settings > Macro Settings and select "Disable all macros with notification" so Excel prompts you to enable them when you open a workbook.
• PivotTable pulls the wrong data range: This usually happens when your source data is a plain cell range instead of a named Table. Convert your data to a Table (Ctrl+T) so the range expands automatically as you add rows.
• Macro works in one workbook but not another: Your macro is likely stored in the active workbook rather than the Personal Macro Workbook. Re-record the macro and choose Personal Macro Workbook as the storage location so it is available across all files.
• Recorded macro breaks after adding columns to source data: A recorded macro may reference specific column positions. If you add or remove columns from your source Table, the recorded steps may point to the wrong fields. In this case, re-record the macro with the updated data structure or edit the VBA code to reference field names instead of column indexes.

Take Your Excel Skills Further

You now know how to record a PivotTable macro, attach it to an Excel macro button and troubleshoot common issues that come up along the way. Automating repetitive PivotTable tasks is just one of many ways to work smarter in Excel.

If you want to deepen your skills with formulas, data analysis, dashboards and more, Pryor Learning offers live, instructor-led Excel seminars and on-demand courses designed for every skill level. Whether you are an individual looking to level up or a team leader investing in your group's productivity, explore Pryor's Excel training options to find the right fit.