Creating a dynamic chart title in Excel 2010 is a very useful trick to learn, it only takes a few minutes so read on to learn how to create dynamic chart titles…
Dynamic Chart Titles in Excel 2010
One of the best features of Excel 2010 is the ability to quickly visualize your data in chart format but often you will see reports with multiple charts that have missing titles, out of date titles or even the unforgivable “Chart 1” as the title!
The best way to make sure your charts remain fresh and up-to-date is to use dynamic chart titles which basically are about linking your chart title to a cell value.
Take the following spreadsheet as the example, a sales report with a new chart just inserted into the worksheet and we decide to make the chart title dynamic and link it to cell A3 (highlighted) which conveniently happens to be our data table title:
At the moment the default of the chart is to have no title, so we can quickly add a title to the chart using the options bar along the top.
First click on the chart, then select “Layout” from the Options bar along the top of the worksheet, from that select “Chart Title” and add a title “Above Chart”:
Excel will automatically insert a chart title called, well “Chart Title”!
Now this is the important part.
You’ll notice that when the Chart Title is first inserted it has a blue highlighted box around it; this means that the Chart Title is the selected object on the worksheet. If you click on any other cell it will lose this in focus, but to get it back just click on the “Chart Title” area to highlight it again.
With the chart title highlighted Click on the formula bar so the flashing cursor appears inside it:
With the flashing cursor in the formula bar type an equal sign, =, into the formula bar then click on the cell which contains the title you want on your chart, in this example case A3:
The formula bar will now update with our cell reference, in this case it reads =Sheet1!$A$3. Note that it refers to the sheet name and the cell location. If you would rather type the location into the formula bar than click on the cell then you must always use the sheet name in your reference. If you had just typed “= A3” into the formula bar an error message would get returned.
When you have the reference is in the formula bar just press the ENTER key on your keyboard:
Straight away you can see that your Chart Title now says the same as the text in Cell A3, if you change the text in cell A3 it will automatically update the chart title, there is no need to refresh anything so you have created a dynamic chart title.
This is a great automation technique, but it is made even better when you automate the titles that feed your chart, that way you create a truly dynamic chart title and get one step closer to full automation.
To learn how to automate your titles check out my simple guide here.