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!”
