Dynamic Chart Title with Slicers

Here’s the situation. We have created a PivotTable and related PivotChart, and, since we are nice, we have also provided a Slicer so that the user can easily make selections. But, we’d like the report titles to dynamically update based on the selections made. As with anything in Excel, there are multiple ways to accomplish this. In this post, we’ll use a helper PivotTable.


Here is our beautiful PivotTable and related PivotChart.

Both display sales by Item. In order for the user to be able to pick a region or regions to include in both reports, we have created a slicer, as shown below.

When the slicer is on the same worksheet as the reports, the user can easily see which regions are selected even when printed. However, the slicer may be located on a different worksheet or control several reports throughout the workbook. We’d like each report to clearly let the reader know which regions are included in the report. So, we’ll just set up a dynamic report title, something along these lines:

We’ll use dynamic report titles for the PivotTable and the PivotChart.

I’ve created a video and written narrative below that walks through the details.



We’ll work through the following steps:

  • Helper PT
  • Helper formula
  • Pull into reports

Let’s get to it.

Helper PT

The first step is to create a helper PivotTable. The helper PivotTable has one job: display the items that the user selected in the slicer. To do this, we first create a PT based on the same data source and insert the field used for the slicer into the Columns layout area. At this point, all items in the field are displayed in the report, as shown below.

In order to have the slicer update this helper PT, we need to select the slicer and then click Slicer Tools > Report Connections. In the resulting Report Connections dialog, we check the checkbox for our helper PT, which is stored on the Settings worksheet as shown below.

Now, the slicer selections impact the helper PT as well. Since East and West are selected in the slicer, the PT is updated accordingly, shown below.

The final step is to remove the Grand Total label, which can easily be done by clicking the PivotTable Tools > Grand Totals > Off for Rows and Columns command.

Nice, the updated PT is shown below.

At this point, selections made in the slicer will be displayed in the PT. So far so good. Our next step is to create a helper column to combine the values.

Note: In this post, I’ve placed the field into the columns layout area so that the selections will appear on a single row, but, you could easily adapt the steps presented if you prefer to place the field into the rows layout area instead. The technique will work either way.

Helper formula

We’ll write a helper formula that combines the PT labels (which are the same items selected in the slicer). Probably the easiest way to do this is with the TEXTJOIN function.

If the helper PT column labels were displayed on row 24, we could use the following formula:

=TEXTJOIN(", ",TRUE,24:24)

Essentially, this tells Excel to combine the values in row 24 (the PT labels), using a comma and space to separate the values, and to ignore any empty cells. The results of this formula are shown below:

East, West

With our helper formula complete, it is time to pull it into the reports.

Note: depending on your version of Excel, you may not have the TEXTJOIN function. Instead, you could use the SUBSTITUTE, TRIM, and CONCATENATE functions. For example, if the helper PT labels appear in B23, C23, D23, and E23, you could use something like this formula (which is included in the sample file below for reference): =SUBSTITUTE(TRIM(CONCATENATE(B23,” “,C23,” “,D23,” “,E23)),” “,”, “).

Pull into reports

Let’s say that we want to manually enter a report title, such as Sales by Item into a cell and then use that value as the primary report title. We want the slicer selections to be in the subtitle. We just set up the cells accordingly. The title is a manual input cell (C17), and the subtitle is created by the helper formula written above (C18). Like this:

To pull the title and subtitle into the reports, we just use a formula with a direct cell reference. For example, for the PivotTable report, we could just write two formulas in cells above the PivotTable to retrieve the values from C17 and C18. Like this:


The updated PT is shown below.

We can do the same thing with the PivotChart. To do so, select the chart title and begin the formula by typing = into the formula bar, as shown below.

Then, either type the formula or navigate to the worksheet and select the cell and press Enter. The title will now pull the value from the cell.

We can do the same thing for the subtitle. Insert a Textbox into the chart, just under the chart title, by clicking Insert > Textbox and clicking into the chart area. Enter = into the formula bar, navigate to and select the subtitle helper formula cell, and hit the Enter key on your keyboard. You can format the color and size of the title and subtitle as desired. The updated chart is shown below.

Now, as the user makes selections in the slicer, the chart updates accordingly.

Yay…we did it!

The sample file includes a working example of the steps presented.

If you have any other approaches to dynamic report titles, please share by posting a comment below … thanks!


Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.


  1. JoAnn Paules on November 9, 2018 at 6:48 am

    Thank you for adding an alternative formula for those of us who are not using Office 365. I’ve been using dynamic chart titles for years. I even did something similar to your video but I didn’t know how to handle multiple selections. Very cool!

  2. AC Miller on March 15, 2019 at 1:47 pm

    Thank you for this! It’s exactly what I needed. The instructions were super simple to follow and I was able to execute a dynamic chart title very quickly.

    • Jeff Lenning on March 15, 2019 at 1:48 pm

      Welcome 🙂

  3. Mark Batten-Carew on April 20, 2019 at 9:56 am

    I am trying to combine your excellent solution to “How to have dynamic chart titles via slicers”, with a second solution “How to sort a slicer with another slicer” from here https://www.excelcampus.com/pivot-tables/how-to-sort-a-slicer-with-another-slicer/

    As long as I just have two slicers and 1 pivot table, the second slicer nicely re-sorts empty options to the bottom.

    But when I go to attach the slicers each to a secondary pivot table, purely to get their texts, so I can use their texts to make a chart title, the minute I attach the sub-slicer to its secondary table, it stops resorting itself. Undo the sub-slicers’s 2nd report connection to the 2nd table, and the sub-slicer resumes sorting.

    Do you have any solution that allows both a dynamic sub-slicer AND dynamically updating a chart title based on the two slicers?


  4. Kristoffer Sørensen on August 26, 2019 at 9:36 am

    This is very interesting and thank you for your post.

    However, i start to wonder.
    If i create a pivot table from data from a data model and then create a slicer to control the pivot table, is there a way to create a label from the slicer without creating a second pivot-table?
    The slicer is creating a reference which i can use in formulars, but i dont know how to extract what is selected… :/

  5. Tony Styles on September 11, 2020 at 10:09 am

    Great video. Spot on! One small cloud on the horizon. I have everything set up as suggested and it works fine. I have a field with my dynamic title. Problem is that I can’t get it into the chart title. I type ‘=’ and click on the relevant cell but the title only shows ‘=’. If I type in the cell reference it shows ‘=B47’. Same happens with text boxes. I’m sure the answer will embarrassingly simple but it’s eluding me at the moment.

    Kind regards


  6. Tony Styles on September 11, 2020 at 10:35 am

    I knew that almost as soon as I hit the submit button the answer would leap out at me and so it has. Sorted.

    • Jeff Lenning on September 11, 2020 at 10:36 am

      Excellent … glad you got it 🙂

Leave a Comment