Link Pivot Chart Title to Report Filter

Recently, 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?

 

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.

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

Create a Pivot Chart

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

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

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

However, the chart doesn't show which activity has been selected – the chart title just says "Total".

 

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

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))

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

Excel Dashboard Course Recommendation

For a limited time, Mynda Treacy from My Online training Hub is opening her Excel Dashboard course, and if you sign up by 8 pm Pacific time on Nov. 7th, you can get the course for 20% off.

The course is video based, delivered online and is available 24/7. You'll receive comprehensive workbooks and sample dashboards to keep, and there’s even an option to download the videos.

The previous classes were very successful, and you can read the glowing reviews from the students, who loved all the techniques that they learned in the course, and are using them to impress their colleagues.

Click here to find out details of the course, read the student comments, and watch the 'behind the scenes' video that shows you what you'll receive as a member.

Remember, if you sign up by November 7th, you can get the dashboard course for 20% off.

________________