« The Whole Truth About Metadata in Office Documents | Main | Finding the Simplest Solution is What Office Is All About! »

The subjects of editing chart titles and custom data labels has come up a few times in recent days... so I thought a post was warranted.

In case you're not familiar with this little Excel gem, you can link any chart title, chart axis title, chart data label, or even a text box drawn on the chart dynamically to a cell in your data, rather than either typing in text or being limited (in the case of data labels) to the provided Chart Options for what data is displayed. It takes a second to do and updates automatically whenever the relevant data changes.

For example, you might want to show just the sum of all data points in a column of a stacked bar chart as the data label value -- in which case, you can just create the sum function for the data on your data worksheet, and then follow the instructions below to link each desired data label to the cell containing the sum function for the correct column... Here goes:

Link a chart title, chart axis title, or data label to data on a worksheet:

1. Select the chart title, axis title, or first data label that you want to display a worksheet cell's content (for a data label, click to select the series of data labels, then single-click once more on the individual label you want to select).

2. With the title or label selected, type an equal sign in the formula bar.

3. Browse to and select the cell you want to link, then press Enter. The content of the cell will now appear in the title or label box ... and will change anytime the linked cell's content changes, just as your chart's data series do.

Create linked text boxes:

If you're already using your chart titles and labels for other purposes, or you need dynamically updating text on the chart that doesn't belong in a title or available label, you can create a text box and link it exactly as per the instructions for titles and labels, above.

1. Click the Text Box icon on the Drawing toolbar and then click on the chart where you want the text box to appear (you can move it later, of course).

2. With the text box either active or selected, follow steps 2 and 3 above.

3. If the entire content of your linked cell doesn't appear in the text box, click and drag on the text box's handles to stretch it. Note that you can double-click on the text box to open the Format Text Box dialog box -- where you can change its formatting, including even the orientation of the text.

TIPS: To Easily move a text box on a chart, select the text box and then press F2, Escape ... then you can use the arrow keys on your keyboard to move the text box precisely.

Note, to easily select text boxes on your chart, click the white arrow (the Select Objects) icon on the Drawing toolbar ... then you can click and drag around all or part of the chart to select just graphic objects on top of the chart, rather than chart elements. Press Escape twice or deselect the same toolbar icon to once again select chart elements instead of text boxes and other drawing objects.

Posted by Stephanie

TrackBack URL for this entry:
http://www.arouet.net/cgi-bin/mt/mt-tb.cgi/15