There is a very cool way, apparently not well known, of adding ‘active’ or ‘live’ titles and other text to charts. In this way you can make a change in a worksheet and have that change reflected in a title in the chart. Follow these steps:
- Create your chart as you normally would.
- Add whatever titles, data points, axis names, etc. are desired.
- Select the title you want to modify. Small handles should appear around the title.
- In the formula bar enter the address of the cell you want to use for the title. For instance, if the title is in cell A1 of the MySheet worksheet, you would enter the following in the formula bar: =MySheet!$A$1
That’s it. Now, whenever the contents of A1 are changed Excel updates the information in the chart’s title.
Applies to Excel 97, 2000, 2002, 2003, and 2007