Many a times we want to include chart titles in our dashboards, but we don’t since they are not dynamic. If your user changes some filters, you would want your pivot chart titles to update automatically. Well, there is a way to actually do that, to make these chart labels truly dynamic. And Excel MVP Mynda Treacy is here to show us how.
Let’s get started!
1 – The Setup
As shown in the picture, here I have a pivot chart being driven by a pivot table. As you can see, the chart title seems linked to the results of the pivot table. And believe me, when we will switch the filter to US instead of UK, the chart title will automatically update.
So, once you have your pivot chart connected to a pivot table, how to make the title dynamic?
2 – The Trick
There are two steps to the trick.
- In a cell, we type the following formula:
This formula is actually spelling out the chart title. The static text can be typed out within inverted commas, e.g. “Sales to date “. The dynamic text can be created by referring to the cell containing them. H1 is actually the cell reference to the filter which says UK or US. Also, the GETPIVOTDATA(“Sale”,$G$3) appears automatically in the formula bar when the ‘Grand Total’ amount is selected. And we join all these string of texts using ampersands (i.e. “&”).
Note that the GETPIVOTDATA just fetches the unformatted ‘Grand Total’ number. We wrap it into the TEXT function to format it.
- The second part of the trick is really simple. Select the chart title, click on formula bar, type the equals sign and point to the cell containing the (dynamic) text for the chart title. It’s shown in the picture below.
3 – Text Format
You must have noticed the weird symbols “$#,##” specifying the format of the number. Well, if you don’t have a dollar figure, what code will format it according to your liking?
Well, the answer is very simple. Just right-click on the number that will be in your chart title and select ‘Format Cells…’. Now under the ‘Number’ category select ‘Custom’. Here’s you can browse through the codes while checking out the samples. And copy whichever you like.
Just keep one thing in mind: some codes have semi-colons in them, like “#,##0;-#,##0”. Before the semi-colon, the part of code is for formatting positive numbers; after the semi-colon, the part of code specifies the format of negative numbers. The TEXT function cannot take in both. Hence, you should specify the one for positive numbers only.
What’s next?
Do try this handy trick and share your experiences with us in the comments section below. Also, do not forget to spread this tip to your colleagues and friends.
- SSSVEDA DAY 7 – Every Team Needs Someone Who Understands Data - February 18, 2018
- SSSVEDA DAY 5 – When Data Analysis is Wrong - October 31, 2017
- SSSVEDA DAY 4 – Sharing the Excel Knowledge - July 18, 2017
