Create Dynamic Chart Titles in Excel
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
My new, secure onlie shopping website – http://shop.thecompanyrocks.com – is now open. I invite you to vist and get more information about the products that I offer for sale including, “Learn how to “Master Excel in Minutes – Not Months!”
September 5th, 2011 at 5:22 AM
Just what I needed! You provide a great service.
A million Thanks
September 5th, 2011 at 6:46 AM
Thank you Sharon -
I am pleased that I could help you.
October 9th, 2011 at 8:56 PM
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
February 9th, 2012 at 1:16 AM
that works if you are using a filter to select…how do you do it if you are using a slicer