A viewer wrote to ask for my help in creating dynamic chart titles in Excel. He has created a series of pivot table reports and pivot charts. He wants the titles for his charts to update dynamically. That is, as he selects a new filter for his pivot table, he wants the title in his pivot chart to match the value label in his filter.
- Dynamic Chart Titles
Note:
This technique will work for all Excel Charts and Chart Types. It is not restricted to Pivot Tables.
The solution is rather simple: You link the Chart Title to a formula.
Link Chart Title to Formula
Select a cell that is outside the range of your Pivot Table – let’s say cell H1. An example formula is:
=”Sales for Fiscal Year “&D1
In this case, cell D1 contains the label for the value that you are selecting to filter your Pivot Table Report and Chart – e.g. 2008 as your Fiscal Year.
Notice that in the formula, I include the “TEXT” inside ” ” (double quotation marks. I also use the & (ampersand) to join the text to the cell reference (D1).
Finally, select the Chart Title and then, in the formula bar type =H1 (where H1 is the cell that contains the formula that we just wrote).
As a finishing touch, you can “hide” the formula in cell H1 by changing the FONT COLOR to match the background color for that cell.
It is that easy! Try it yourself after you watch this short (6 minutes) video lesson.
Invitation to Visit My New Online Shopping Site
Shop for The 50 Best Tips for Excel 2007
My new, secure, online shopping website – http://shop.thecompanyrocks.com – is now open. I invite you to stop by for a visit and get more information about the many resources that I offer for sale including, “Learn how to “Master Excel in Minutes – Not Months!”
Just what I needed! You provide a great service.
A million Thanks
Thank you Sharon –
I am pleased that I could help you.
Hi,
That was a great example using pivot charts, however I wonder if you could assist me in a dynamic chart query.
I have approximately 40 sets of data, each of varying values (from 100s to 1000000s) and each set is returned in weekly values.
I’m trying to set up a dynamic chart where once a user selects the name of the dataset, the length of time to display the data then excel will produce a dynamic chart. I can do this for each set, but trying to get excel to dynamically change the dataset is driving me spare….
I know it can be done, but just how escapes me… I don’t want to be in a situation where I’m running off 40 plus charts a week when I’m sure excel can bring this down to one chart – albeit with a lot of behind the scenes magic
regards
dave
that works if you are using a filter to select…how do you do it if you are using a slicer
WOW so easy and works like a dream, I put it the other way round with the text following the inset word!
Thanks
Thanks for adding your comment Mike!
I am pleased that you discovered this Tip on my website!
Danny Rocks
The Company Rocks
nice !!! thanks buddy
Thank you Dipak – I am glad that you enjoyed my video tutorial!
Danny Rocks
The Company Rocks
I saw comment above asking if this will work with slicers, but don’t see the response to how to make it work. Can you help with this?