21

Create Dynamic Pivot Chart Titles with a VBA Macro

Bottom line: Learn how to automatically create more descriptive and dynamic chart titles that display the filter criteria with VBA macros.

Skill level: Intermediate

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

Pivot Chart Titles Default to Total

A chart’s title should describe the data that is presented in the chart.

When inserting a new pivot chart, the chart’s title usually defaults to the words “Total” or “Chart Title”.  This means we must always take extra steps to modify the chart title to make it more descriptive.

Pivot Chart Title Defaults to Total in Excel

So, I wrote a few macros to automate this process, and also create dynamic titles that list the filter criteria.

Note: The macros just do the setup work, and are NOT actually needed for the interactive subtitles.  More on that below.

If you're new to creating pivot tables and pivot charts, then checkout my free 3-part video series on pivot tables and dashboards.

The Automatic Chart Title Macros

The following macros create descriptive chart titles and dynamic subtitles for our Pivot Charts.  The macros use the names (captions) of the fields used in the pivot chart to create the titles and subtitles.

Download the Excel File

You can download the Excel file that contains the macros below.  The macros can be added to your Personal Macro Workbook.

Dynamic PivotChart Title Macro.xlsm (266.0 KB)

Macro #1: Auto Title PivotChart

The first macro creates a simple chart title based on the fields in the Values, Rows, and Columns areas of the pivot chart (pivot table).

PivotChart Title Before and After Simple Macro

The macro loops through each PivotField in the Values (DataFields), Rows (RowFields), and Columns (ColumnFields) of the PivotChart.  It creates a string of text that joins together each field name (caption).

Note: The macro uses the Caption property of the pivot field, which is the field name that is displayed in the pivot table and chart.  The Caption can be different from the Name property.  The Name is the source (column) name from the source data.

The text uses separators between the values and rows/columns fields, which can be changed in the code.  Here is an example of the structure of the title.

[Values Field Caption] by [Rows Field Caption] by [Columns Field Caption]

Here is an example of an actual title

Total Revenue by Salesperson by Quarter

The macro will include multiple fields in any of the areas, and list them in order of the field position.  Here is an example.

Chart Title Includes Multiple Field Names Used in Pivot Table

[Values Field Caption] by [1st Rows Field Caption] by [2nd Rows Field Caption] by [Columns Field Caption]

If an area has multiple fields in the Values area, then the ampersand & is used to separate the field names.

Again, this is a simple macro that just saves time from having to type the chart title manually.

If you accidentally toggle the Chart Title checkbox on & off, then the chart title is reset to “Total”.  You typically have to re-type the title, but this macro will save time with that too.

Toggling Chart Title Checkbox Resets Title to Default Total

Macro #2: Dynamic Pivot Chart Titles Textbox

The second macro creates a dynamic subtitle to include the fields in the Filters area, and their filter criteria.  This macro will save you a lot of time with manual setup work.

How the Dynamic Pivot Chart Title Macro Works

Click to Enlarge

How the Macro Works

This macro is a bit more advanced.  Here is a screencast of the macro in action.

Dynamic Pivot Chart Title Macro GIF

Click to Enlarge

It first loops through the fields in the Filters Area of the pivot chart’s pivot table and creates cell formula. The macro actually creates a string of text that references the cells in the Filters Area LabelRange (the cells in the pivot table that contain the filter fields and filter values (criteria).

The macro then displays an InputBox and prompts the user to select a blank cell for the formula.  The formula is input in the sheet.

When the formula evaluates it will display a string of text that contains the filter fields and their criteria.  Here is an example.

Filters: Year: 2014 | Quarter: Qtr1, Qtr2 | Region: East

A Textbox (shape) is added to the pivot chart for the subtitle.  The textbox is linked to the cell that contains the formula.  This is considered a dynamic title because the text in the textbox will change as the result of the formula changes.

Display Multiple Filter Items

In this example I'm also using another technique to display multiple filter items in a comma separated list.  Checkout this post 3 Ways to Display (Multiple Items) Filter Criteria in a Pivot Table to learn more.

When filters are applied to the pivot table/chart, the formula displays the filter criteria and so does the textbox.

Finally, the macro formats the textbox to match the text color of the chart’s title.  It also resizes the Plot Area of the chart to prevent the textbox from overlapping it.

The macro does a lot of work to create the chart’s dynamic subtitle.  The good news is that you don’t have to do any of this manual setup work.  Just run the macro!

It's important to note that the macro only does the setup work.  Once the setup is complete the macro is no longer needed.  The formula in the cell updates when the filters are applied, and the linked textbox in the chart just displays the cell's contents.  This means that you do NOT need to store macros in the files that you run this macro on.  This is nice if you are sending the files to other users and you don't want the file to contain macros.

Macro #3: Dynamic PivotChart Titles

The third macro just calls macro #1 and #2.  You can use the macros independently if you only want to create a title or subtitle.  Or, you can run them together to create both titles at the same time.

Add Macro Buttons to a Custom Ribbon

In the video above I run the macros from a custom “My Macros” ribbon.  I created macro buttons that call the macros.

The macros are stored in my Personal Macro Workbook.  This means I can run the macros on any pivot chart in any workbook I have open.

Automatic Chart Title Macro Buttons on Custom Ribbon in Excel

Once this is setup, you just have to select the chart, then press one of the macro buttons in the custom ribbon.

Checkout my 4-part video series on the Personal Macro Workbook to learn how to set it up and create the custom ribbons and buttons.

How Will You Use These Macros?

You can modify the macros to include additional formatting, title placement, etc.  Checkout my Chart Alignment Add-in for more details on how to quickly move & align the chart elements.

Please leave a comment below with any questions or suggestions on how you would modify these macros.  Thank you! 😊

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 21 comments
Margreet - October 31, 2018

Dear Jon,

I opened your Excel file on my Computer, I read the instructions and selected the Chart on the Try It worksheet, I click on the “Run the …” button, and Excel instructs me to select a Pivot Chart first. Why doesn’t the macro work?

Best regards,

Margreet

Reply
Dick - June 30, 2018

Jon when I run the macro for the subtitle all I get is the = sign. When I monitor the macro it seems to skik the lines that setup the info for th text box. Any thoughts?

Reply
Steven Shepperson - May 28, 2018

Hi Jon, This is great, is there a way of inputting the sheet name into the chart title?

Many thanks

Steve

Reply
    Jon Acampora - May 29, 2018

    Thanks Steve! Yes, here is a simple macro that will set the active chart’s title to the sheet name.

    Sub Chart_Title_Sheet_Name()
    'Create PivotChart title based on used field names

    Dim cht As Chart
      
      'Check if PivotChart is selected
      'Can be changed to reference a specific chart or loop through charts
      On Error Resume Next
        Set cht = ActiveChart
      On Error GoTo 0
      
      If cht Is Nothing Then
        MsgBox "Please select a chart first."
        Exit Sub
      End If
      
      'Change chart's title
      cht.HasTitle = True
      cht.ChartTitle.Caption = cht.Parent.Parent.Name
      
    End Sub

    The following line will return the sheet name that the chart is on.

    cht.ChartTitle.Caption = cht.Parent.Parent.Name

    The parent of the chart is the shape that the chart is in, and the parent of the shape is the sheet that the chart is in.

    I hope that helps. Thanks again! 🙂

    Reply
      Steven Shepperson - June 6, 2018

      Hi Jon,

      Will this format work for a table?

      Many thanks

      Steve

      Reply
        Jon Acampora - June 8, 2018

        Hi Steve,

        Tables are called List Objects in VBA. You could do something similar to set the sheet’s name to the name of the Table.

        Sub Set_Sheet_Name_to_Table_Name()

        Dim ws As Worksheet
        Dim lo As ListObject

          For Each ws In ActiveWorkbook.Worksheets
            
            'Checks if there is a Table on the sheet and sets reference
            'to the first table on the sheet
            On Error Resume Next
              Set lo = ws.ListObjects(1)
            On Error GoTo 0
          
            'Change the sheet name to match the table name
            If Not lo Is Nothing Then
              ws.Name = lo.Name
            End If
          
          Next ws

        End Sub

        I hope that helps.

        Reply
Edil - May 25, 2018

Awesome Jon. I enjoyed this one very much, thx!

Reply
Sheldin - May 25, 2018

The lesson was very helpful. Thank you

Reply
Peter - May 25, 2018

Brilliant! Many thanks…sadly I’m stuck in 2010 so I’ll have to investigate your Concatenate macro fix! So much to learn, so little time…but better used thanks to you!

Reply
    Jon Acampora - May 29, 2018

    Thanks Peter! I thought of a workaround for this that does not require the Concatenate macro. Here is a link to the file that contains the solution.

    https://www.excelcampus.com/filedownload/Display-Multiple-Items-in-Pivot-Table-Filter-Alt-Solution.xlsx

    I don’t have any written instructions on it yet, besides what is in the file. There is a formula starting in cell D7 that adds the text for each additional row as the formula is copied down. You just have to copy the formulas in C7:D7 down to cover as many possible rows as you would have for items in the pivot field.

    Then there is an INDEX/MATCH formula in D5 that will find the last cell with data in column D and return that as the filter criteria in a comma separated list.

    You can then create a reference to that cell in the cell to the right of the filter drop-down menu in the Filters area of the pivot table. Then use the macro on this page to create the chart titles.

    I hope that helps. 🙂

    Reply
Daniel - May 24, 2018

The Dynamic Title Macro doesn’t seem to work on Excel 2010 when Multiple Items are selected in the filter. Is there a way to fix this for 2010?

Reply
    Jon Acampora - May 24, 2018

    Hi Daniel,
    The multiple filter item technique uses the TEXTJOIN function, which was introduced in Excel 2016 (Office 365). For older versions of Excel you can use the CONCATENATE function or my CONCATENATE macro to create the comma separated lists. I hope that helps.

    Reply
      Daniel - May 25, 2018

      Hey Jon,

      When I use your example file in Excel 2010, the Dynamic Subtitle works great. I tried to use one of my own files and it won’t work. What could it be that I am doing wrong?

      Reply
      Brenda - June 3, 2018

      Hi, Jon

      But how would you modify the textjoin formulas for the 3 filters in Col. C? And hopefully that’ll resolve the #NAME? error when you pick multiple items in a slicer which of course affects H1 and the title because of the error it generates.
      Life is a jungle, ain’t it? lol

      Reply
Afif - May 24, 2018

Thx; goo tuto

Reply
Dawn - May 24, 2018

THANK YOU!!!! I’ve been looking for a way to do this.

Reply
Stanley Runyon - May 24, 2018

Brilliant, Jon! I can’t wait until I’m able to afford to take your course on VBA and Macros.

Reply
    Jon Acampora - May 24, 2018

    Thank you Stanley! I look forward to having you join us. 🙂

    Reply

Leave a Reply: