Here is a little trick to give you an easy workaround for the titles of your chart. The normal way of entering a title is right-clicking in your graph, then choosing chart options and on the tab Title changing the title of the chart. But it can be done easier and smoother directly out of the spreadsheet. Imagine that you have a table with a chart like this:
Table 1
| A | B | C | D | E | |
| 1 | Costs and Revenue January - April | ||||
| 2 | Jan | Feb | Mar | Apr | |
| 3 | Costs | 6760 | 438 | 3990 | 4394 |
| 4 | Revenue | 5358 | 1811 | 7537 | 6346 |
In the chart wizard you may have entered something like Test. That is what I have done in this example. Now simply click the title of your chart and into the formula bar simply enter the equal sign (=) and click the cell that contains your title. In our example this is cell A1 (reading: Costs and Revenue January - April). Press enter (or the green checkmark) and voilà you have the title of your chart.
The formula bar should now read "=Sheet1$A$1"
If you e. g. next month enter cost and revenue data for may and change the title in cell A1, the chart title also changes.
UPDATE
As Jon Peltier states in the comments to this post, this will also work on other chart text elements like axis titles etc. If you read through the discussion below, you will see, that there are still enhancements possible to the title. Although you cannot enter any formula, when you are selecting the chart title with the method mentioned in this posts, it of couse still is possible to enhance the title with several functions in the cell it links to. In the below mentioned "formula" - which simply is an concatenation of several cells - we have attached the two "categories" with some binding words and the first and last month we are watching.
=A3&" & "&A4&" "&B2&" - "&E2
Imagine now, that your customer / your bosses / whoever wants to have it in a different language. I work in a German company and for this example I will assume that my bosses decided that the chart should be in German instead of English (fortunately the abbreviations for the months can be "re-used" as they are nearly the same in German). I simply change the cells A3 and A4 - the names of the categories - and there I have the change in the chart title as well.
There you have it! Thank you Jon for your hint!!!
Discuss this blog post in the forums (click the blue button)
Comments
Axis Title
Data Labels
Text Boxes (and other shapes)
You can also make your chart title even more dynamic by doing something like “=chartTitle & ” ” & TEXT(TODAY();”d d-mmm-yyyy”)
thank you for your comment.
First part for me works only if you still attach the filename to the title element. Only the “defined name” is not working.
Also the second part (attaching a text to the name) seems not to be working. I tried this in different variations and it worked on normal cells but not on the title element in the charts. The ampersand seems not to be working on text elements in charts.
The formula in the chart’s text element can only be a single link, not a built up formula. However, you can construct the formula in the cell you link the text element to.
thanks again for the comment. I will update the post with your information.
Nice to have someone with prominence in Excel here
RSS feed for comments to this post.