Macro Buttons to Add Fields to Pivot Tables

Bottom Line: Learn how to create simple macro buttons that users can click to instantly add or remove fields to Excel pivot tables.

Skill Level: Intermediate

Download the Excel File

Download the Excel file I use in the video to follow along. The file contains the VBA macros.

Adding Macro Buttons to a Pivot Table

Lauren, a member of The Pro VBA Course, had a great question on making her pivot tables easier for other users to customize.  What she needed were buttons on her spreadsheet that the users could click to add or remove fields in the pivot table.

This allows the user to quickly change the view of what is included in the report.  It's a great solution if you don't want to have multiple sheet tabs with different pivot tables, or you're creating a dashboard.

It also makes it easier for users or consumers of your files that aren't familiar with modifying pivot tables.

It's important to note the the fields in the Values area are staying the same.  The macro only changes the fields in the Rows area.  We could modify this to change the other fields though.

The Replace and Toggle Macros

I'm going to share two macros.  The first will replace all the fields in the Rows area with the field name in the button's text.

The second macro is a toggle that will add the field if it's not currently in the rows area, and vice versa.

1. Macro to Replace Fields in the Rows Area

The first macro will remove all existing fields from the Rows area, and then add the single field based on the button the user clicked.

The nice part about this macro is that we only need to use one macro for all the different buttons.  Here is an image that explains more about how the macro workers.  I also explain this in detail in the video above.

Note: To access the VB Editor just press Alt+F11 or click on the Visual Basic button on the Developer tab.

Application.Caller Property

The macro is assigned to the shapes on the sheet.  The Application.Caller property returns the name of the shape that was clicked by the user to run the macro.

This is similar to caller ID on your phone.  Application.Caller tells us who (which object) is calling the macro from the Excel application.

It's a very powerful feature because we use the text in the button to reference the field name that is added to the pivot table.

The TextFrame.Characters.Text property of the Shape object returns the text within the shape.  This is set to a variable (sField), and reused in the references for the PivotFields property.

It's important to note that each shape must have a unique name in order for the macro to work.

The VBA Code to Replace the Pivot Table Fields

Here is the VBA code for the macro to replace the pivot fields.  You can copy/paste it to your VB Editor.  The code is also included in the example Excel file in the downloads section above.

Sub Add_Row_Field()
'Remove all Row fields and add the Row field to the pivot table.
'The field is determined by the button text that calls the macro.

Dim pt As PivotTable
Dim pf As PivotField
Dim sField As String

  'Set variables
  Set pt = ActiveSheet.PivotTables(1)
  sField = ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text
  
  'Remove existing fields
  For Each pf In pt.RowFields
    If pf.Name <> "Values" Then
      pf.Orientation = xlHidden
    End If
  Next pf
  
  'Add field that button was clicked for
  pt.PivotFields(sField).Orientation = xlRowField

End Sub

Adding More Macro Buttons

The nice part about using Application.Caller is that you can use it for as many buttons as you need.  All we have to do is set the text in the shape to match any of the fields in the pivot table.  We can quickly make a duplicate copy of the shape and change the text to add more options to our report.

Here are the instructions to add more macro buttons:

1. Hold the Ctrl key while clicking on any of the existing buttons.  Using the Ctrl key will select the shape, instead of running the macro.
   
2. Hover the mouse over the edge of the shape.  Left-click and hold, then press Ctrl+Shift while dragging the button to a new area.    Release the mouse button, then the keyboard keys.  This duplicates the button and keeps it aligned with the other shape.
   
3. Left-click the shape and change the text to match one of the other pivot table fields.
   

The button is already assigned to the macro.  When you press the button it will add that field to the pivot table.

2. Macro to Toggle Pivot Table Fields

This macro allows the user to add (or remove) multiple fields to the pivot table.  It can also change the order (position) of the fields.

The macro is similar to the first one.  The main difference is that we use an If statement to determine if the field is already in the pivot table.  If not, the macro adds it.  If it is in the pivot table then the macro removes it.

The macro also changes the fill color of the shape to make it look enabled/disabled using the Brightness property.

Here is a screenshot with an explanation of the macro.

With this macro, users can add more than one field into their pivot tables by just clicking on the buttons they want.  The order (position) of the fields is determined by the order the buttons are clicked.

The VBA Code for the Toggle Pivot Fields Macro

Here is the VBA code for the macro to toggle the pivot fields.  You can copy/paste it to your VB Editor.  The code is also included in the example Excel file in the downloads section above.

Sub Toggle_Row_Field()
'Add/Remove the field to the pivot table.
'The field is determined by the button text that calls the macro.

Dim pt As PivotTable
Dim pf As PivotField
Dim sField As String
Dim shp As Shape

  'Set variables
  Set pt = ActiveSheet.PivotTables(1)
  Set shp = ActiveSheet.Shapes(Application.Caller)
  sField = shp.TextFrame.Characters.Text
  
  'Toggle field
  'If visible then hide it
  If pt.PivotFields(sField).Orientation = xlRowField Then
    pt.PivotFields(sField).Orientation = xlHidden
    shp.Fill.ForeColor.Brightness = 0.5
  Else 'Add to Rows area
    pt.PivotFields(sField).Orientation = xlRowField
    shp.Fill.ForeColor.Brightness = 0
  End If

End Sub

Arranging Reports in an Instant

At the push of a button, you can rearrange the data in your pivot tables in a multitude of different ways.

In this first example, we pressed the Region button first, and then the Salesperson button.  This shows the Sum of Revenue for the Salesperson in each Region.

In this next image, we first selected Salesperson and then Customer Name.  This shows us the Sum of Revenue for the Customers for each Salesperson.

You can create buttons for all of the fields that might be relevant to the metric or calculation in the Values area.

The  Macros Work with Slicers and Pivot Charts

The macros also work when slicers or charts are connected to your pivot table.

Only the fields included in the pivot table are being modified by the macro.  Slicers can still be used to filter the pivot tables and any Pivot Charts connected to the pivot table.

Make Your Reports Easier to Use

These macros simplify the user experience and allow your users to explore the data.  It's a great feature if you plan to share your workbooks with others who may not be as Excel savvy as you are. 😉

We can also modify the macros to change the fields in the Columns, Filters, or Values area.  Leave a comment below if you are interested in those variations.

Please leave a comment below with any questions or suggestions.  Thank you! 🙂

Additional Resources

• Video series on getting started with Macros & VBA
• 3-part video series on Pivot Tables & Dashboards
• Free webinar on the Modern Excel Power Tools
• Automatic number formatting for pivot tables
• Create Dynamic Pivot Chart Titles with a VBA Macro