Link Pivot Chart Title to Report Filter

functionalartRecently, I enrolled in an online Infographics and data visualization course, and the classes started last week.

The instructor is Alberto Cairo, who wrote The Functional Art: An Introduction to Information Graphics and Visualization.

He uploaded the first two chapters of his book for us to read during week one, and I really enjoyed it. There was some history, some theory, and plenty of graphics, to illustrate the text.

Improve the Infographic

One of the assignments this week was to suggest improvements to an infographic on Social Web Involvement. There are 16 countries in the infographic, overlaid on a world map, and it’s difficult to read or understand the data.

Here is a small section of the graphic, showing data for Canada. Do you have a headache yet, just from looking at that tiny section?

infographicsocialweb

Create a Pivot Table

To experiment with the data, and see how I could improve on the presentation, I put data for a few of the countries into a table in Excel.

infographicsocialweb02

Next, I created a pivot table from the Excel table, with:

  • Country as a Row label
  • Activity as a Report Filter
  • Percentage in the Values area (formatted as Percentage, with 1 decimal).

infographicsocialweb03

Create a Pivot Chart

Based on the pivot table, I created a bar chart, that shows the total percentages for each country.

infographicsocialweb04

To improve its appearance, I made the following changes:

  • Click the Ribbon’s Layout tab, click Legend, and click None
  • On the Ribbon’s Analyze tab, click Field Buttons, and turn off the Axis and Value buttons – leave the Report Filter button on

infographicsocialweb05

Then, instead of showing all the activities lumped together, I could select a specific activity from the drop down list.

infographicsocialweb06

However, the chart doesn’t show which activity has been selected – the chart title just says “Total”.

infographicsocialweb07

Update the Chart Title

Instead of showing “Total”, I’d like the chart title to change when I select one of the activities. To fix that, I’ll link the chart title to the Report Filter cell.

  1. Click on the chart title, to select it
  2. Click in the Formula bar, and type an =
  3. Click on the Report Filter cell – B1 in this example
  4. Press Enter, to complete the formula

chartlinktitle

Chart Title Updates Automatically

Now, if I select an activity in either the pivot table or the pivot chart, the chart title updates automatically, to show which activity has been selected.

If you’d like to show a special heading if (All) or (Multiple Items) is shown in the Report Filter cell, you can create a formula, and link the chart title to that cell.

In the screen shot below, the following formula is in cell E1, and the chart title is linked to E1.

=IF(B1=”(All)”,”All Activities”,IF(B1=”(Multiple Items)”,”Multiple Activities”,B1))

infographicsocialweb08

And now I’d better get back to my homework!

________________

You may also like...

31 Responses

  1. Philippe BÉRARD says:

    It’s exactly the question I tried to solve last week !
    And to add to your presentation, it also works with a named range.

    Philippe

  2. CCH says:

    BRILLIANT!!!
    This was exactly what I needed, works great!
    Thanks!

  3. Jw says:

    What if your row labels are years, and you want to reflect the active/chosen range (i.e. 2012 – 2014) or if 2013 – 2014 are chosen… then reflect that range

    • ALL says:

      Have you found a solution to this? I am also trying to figure out a way to display exactly which filter values are active when multiples have been selected (eg. Apple Orange Pear, or 2012 2013 2014). The dynamic chart label works great until you select more than one filter value – the label becomes nearly meaningless when it just displays “multiple values”.

  4. TK says:

    Can you append to this formula in some way? I would like mine to say =”This is my chart’s name: ” & “link to charttablecell answer”

    i.e.

    This is my chart’s name: Banana
    This is my chart’s name: Orange

    where Banana and Orange reference the slicer information I’m pulling from the table to make my pivot chart

  5. Mark says:

    Perfect. I was looking at several other solutions that involved macros. Simple and sweet.

  6. Fernando says:

    Great Debra! Excel made it for me the first time, but I was unable to reproduce it up to reading your post.

    I’m still wondering why I should move the chart back to the pivot table sheet to make the title configuration and back move it again to an independent sheet as I wanted to be (no way to make the reference from the independent worksheet, same behavior as using sliders, can´t use it if I’m working in an chart in a new sheet) Curious, isn’t it? ´

    Again, thanks for your post!

  7. Mark says:

    Nice post. I was doing something similar with the title of a Pivot Chart that lived in a chart sheet. You can link the title to a cell on another sheet as long as the target cell does not have a range name associated with it – Excel complains with an error. Strange. I thought maybe it had something to do with the scope of the range name but it had Workbook scope. Once I removed the range name from my cell containing the string formula for my chart title, I could link the chart title to that cell.

  8. Amir says:

    Thanks a lot, it saved my time and helped me in implementing my dashboard in more elegant way!

  9. Emilie says:

    Thanks so much. Exactly what I was looking for!

  10. apunc1 says:

    Was this done in Excel 2013? I’m using 2007 and I can’t figure out how to get the report filters ON the pivot chart. My Analyze ribbon looks slightly different than yours.

    I have everything working for linking the chart title for automatic updating (nice IF function, BTW) but how can I add text to the title along with the cell reference? I did this a long time ago on an old file but when I look at the chart title, I can’t figure out how I did it as only the cell reference is visible. I can’t seem to get the syntax right.

    It should be something like =”Current vs. Previous Month for “&Sheet2!$D$1

  11. Claire says:

    Thanks, so clear and precise, just what I was looking for. I tend to do the 5 second scan through instructions once and hope for the best, and yours worked for me first time – the visuals were great. I thought I would use the time saved to send you a thanks :)

  12. Hi,

    great work however I want to add some static text at the end of the dynamic value within the chart title

    example “October Stats” or “November Stats” where the month is the dynamic value in the chart title and the word stats is the static value

    • Mary Beth says:

      Claudia, if you put if your month cell reference is in cell B7, your formula would be =B7 &” Stats”. I used a space at the beginning of Stats so it puts the space between the month and Stats. It should read October Stats now.

  13. Bill Ritzel says:

    I just tried this technique on a Pivot Chart in Excel 2013 and I got “That function isn’t valid.” I reduced it to “=if(true,”A”,”B”)” and got the same result. Has Microsoft done something to make this not work on pivot chart titles in 2013? Does anybody have any ideas/workarounds? Thanks in advance.

    • Alastair Bishop says:

      I don’t know if this will help, but I used a separate cell on another worksheet to calculate the required title text, then set the formula for the pivot chart title to equal the value of that cell. That enabled me to use more complex formulas that didn’t work when I entered them directly on the pivot chart.

      • Steven Fowler says:

        Thanks Alastair – that has solved the problem! The Chart Title didn’t seem to like the code.

        And thanks Debra for the original instructions. Exactly what I was looking for :)

  14. MariUSD says:

    I can’t see in Ribbon’s Analyze tab, the splitted icon “Field Buttons”…just “Field List” and “PivotChart Filter”.
    I have filters only in “PivotChart Filter Pane” but I want to see the fields and filters in ChartArea like in Excel 2003. Now I have Excel 2007.
    Thank you.

  15. Waqar Ahmed Shaikh says:

    Thanks a lot, I was looking for Update the Chart Title automatically & found it here with perfect illustration. :)

  16. Doug Brashear says:

    Debra – This worked great for me on one spreadsheet but no longer works. Both were Excel 2013. Now I follow your very concise :-) instructions but the Formula bar just doesn’t react by putting the text in the title. On my other sheet, I even have it putting the dynamic text in Axis titles (cool!). What could I be missing?

  17. Doug Brashear says:

    No, text boxes didn’t work at all. I was trying to use them to put some disclaimer text in the chart — to no avail. I guess I’ll try to start over with a new Excel file. This one may be haunted.

  18. Joyita Nandi says:

    Thank you… was trying for a long time

  19. Patrick says:

    Thanks exactly what I needed! The animated gif is brilliant!

  20. Sadiq says:

    Thanks a lot for sharing this, Debra. I had previously assumed linking the Pivot chart title to the filter was impossible until I remembered that Excel was quite versatile and then found this. Thanks again.

Leave a Reply

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