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.

Pivot Table Fields Macro Buttons.xlsm (124.5 KB)

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.

Macro Buttons to Add Remove Fields to Pivot Tables

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.

Macro Button to Replace All Pivot Table Fields in Rows Area

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.

Macro code to create macro buttons for pivot table

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.

Hovering over Application.Caller reveals the name of the shape

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.

Hovering over sField Variable reveals name of field we want to add

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.
    Press Ctrl while clicking on the button you want to duplicate
  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.
    Press Ctrl and Shift while dragging the button to duplicate
  3. Left-click the shape and change the text to match one of the other pivot table fields.
    Macro Button Shape Text Must Match Field in Pivot Table Fields List

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.

Toggle Pivot Table Fields with Macro Buttons - Add Remove from Rows Area

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.

code to create toggle fields for macro buttons

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.

Pivot Chart Region then Salesperson

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.

Pivot Chart Salesperson then Customer name

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.

Macro Buttons for Pivot Fields still work with Slicers and Pivot Charts

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

26 comments

Your email address will not be published. Required fields are marked *

  • Hi Jon, thank you for this!

    I am currently using this solution, however i have encountered one issue. I have used these buttons, to create a dashboard on my excel. There are 3 charts, and each has its own pivot table and macro button assigned to it. I have edited the code very slightly, to reflect the PT that i am referring to as there are multiple PT that sits on the same sheet which is hidden by my dashboard.

    However each time i assign the macros to the correct pivot table and save, when i reopen the file the buttons are not longer assigned to the right pivot table and when i click the button, another chart is updated instead. May i know how i can rectify this?

  • <<>>
    Can you please help in accomplishing the same for the columns values?
    I am trying to have a region as row and sales number on the values/column. and toggle between sales, revenue, profit etc.
    Your help is much appreciated!
    Thank you!

    • All,
      This is pretty simple. Just create a different module (I called mine “Toggle_Column_Field”), then copy and past the VBA code you have for the row toggle into this new “column” module. Then replace the word “Row” with “Column” every place in the new column module VBA code (there is only a couple of places to do so). Then link the shape buttons you create for your column variables to the column module by right clicking on the shape, selecting “Assign Macro”, then pick the column module you created. Note that this does not change the “Values” the pivot table calculates, but rather it toggles on-off column variables. I’ve got a file with 5 row buttons, two column buttons and three slicers. It works fine.

  • Hi Jon –

    Great work on the macro. Can you please show how to modify the code for use with multiple pivot tables on the same sheet? I have 2 pivot tables and two sets of buttons on the same sheet and would like for them to work independently of each other, even though the buttons have the same names.

    Thanks,

    Brent

  • Great example, thanks.

    What change is necessary so that instead of having a specific text field tied to a button, the button is tied to a drop-down list?

  • Dear Jon,

    Thanks for sharing and it was very useful. It has saved lot of time and the way we present.

    I need one more help. Guess not asking for too much.
    Just in case, we can a macro to change two different pivot tables ( on the same sheet or active sheet) using single button, where we have the same fields.

    Thanks in advance..

    Rgds

    • I struggled for hours trying to work out why it wasn’t working for my power pivot model. In the end I recorded a macro that added in a field. When I looked at this macro it used CubeField instead of PivotField and the field name was in a [Table].[Field] format. When I changed PivotField to CubeField in Jon’s macro and changed the button text it worked!

      Hope this helps
      Chris

      • Chris,

        I tried this change in my power pivot model, but it did not work. Can you post your specific code that worked in your power pivot model.

        thanks,
        Chris W

  • Hi Jon, thanks a ton for sharing the knowledge and ideas. Its making my job very interesting 🙂
    In reference to 1st macro, whenever i click on macro its adding the Data values on the next column instead of replacing the values, for eg: Sum of Revenue is getting added onto next column.
    not sure if others are facing similar issue.
    Your help would be much appreciated.
    Regards, Karthick

    • I am having the same issue. When I edit the “RowField” to be “DataField” and toggle the button the report adds another column every time I click the button, instead of toggling like expected. Please help! Thank you!

    • Hi Karthik, Can you please help me understand how did you do for columns/values? It would be very helpful if you could share your VB here or to my email. Thanks!

  • Jon, briefly explain how you created the “buttons”. I tried using Insert/Controls/Form Controls/Button, but that doesn’t allow you to change the colors, etc. If I right click on the “Buttons” in the sample file, I can tell these aren’t Active X controls either as the pop up menu options for ActiveX controls aren’t there when you right click on the Button.

  • I like the free video so much I would be happy to pay especailly for the info below
    The way you explain the subject is outstanding. What do you have available on pivots and also using tables with 800,000 rows that does not freeze

    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.

  • Nice Jon. I’m building a dashboard at work and your awesome macro solution appeared out of nowhere. I’m definitely going to implement it. Thanks Jon.

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly