Charts & Dashboards, Macros & VBA

Create Dynamic Pivot Chart Titles with a VBA Macro

May 23, 2018
Jon Acampora
25 comments

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

Skill level: Intermediate

Video Tutorial

Watch on YouTube & Subscribe to our Channel

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.zipDownload

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! 😊

25 comments

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

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

    Reply

  • 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

    • 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

      • 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

      • 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

  • 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

    • 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/wp-content/uploads/2022/07/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

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

    Many thanks

    Steve

    Reply

    • 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

        • 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

  • 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

  • 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

  • Brilliant! Many thanks
    I am Using Excel 2016. I have copy pasted your macro and placed buttons in the ribbon.

    In Sub-Titles, I am getting only the Name of the filtered field but not the selected items.

    Filters: Division: | Block:

    Where may be the problem?

    Reply

  • Works amazing, thanx a lot! Is there any way I could have the dynamic pieces show in the title area? And also just the filter criteria without any other things. Say you would filter on region and quarter it would just show “East Q1”?

    Reply

  • Hi Jon, very big thanx for this! IS there any way it could show the dynamic part in the title area? And just the filter criteria without anything else? Say you would filter on region and quarter it would only show “East Q1”?

    Reply

  • Hi Jon,

    The auto title pivot chart and dynamic pivot chart titles macros’ would make a great addition to your PivotPal Add-in as one-click buttons.

    Thanks,
    Colin

    Reply

More results...

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter