10

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

Watch on YouTube and give it a thumbs up.YouTube Thumbs Up LikeYouTube Subscribe Logo Excel Campus

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

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 10 comments
Karthik - October 3, 2018

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

Reply
Ishwar Singh - September 25, 2018

Mr Jon,

The way you explain the subject is outstanding & very useful in daily office works.

Thanks for your dedication.

Reply
Doug - September 24, 2018

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.

Reply
Sandeep Kothari - September 22, 2018

Great post!

Reply
tony ghent - September 21, 2018

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.

Reply
Greg - September 21, 2018

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.

Reply
Justin - September 21, 2018

Great stuff! Thanks for sharing.

Reply
Dan - September 21, 2018

Hello Jon,

thank you for your very interesting and useful posting.
Your Pivot Macros helps me in my daily work.

Dan

Reply
Rita - September 21, 2018

Jon, this is brilliant! I already have a use for this. Thank you!!

Reply
    Jon Acampora - September 21, 2018

    Awesome! Thanks Rita. I’m happy to hear you’ll be putting it to good use. 🙂

    Reply

Leave a Reply: