TO ASK A QUESTION: If you have a question or need help with Office, please feel free to use the 'Click to Contact' link at the bottom of this page. You'll get a form that you can use to email a question to me. (I had been getting a ton of spam when accepting direct emails, so only emails that use this form will get through to me.)
Please be sure to mention the version of Office you are using when you send your question.
I answer all e-mails that I receive via this form, as long as they are polite :)
Since disabling comments on this site, I'm actually hearing from more of you with questions ... so, as it seems people prefer to email rather than comment, I'm going to leave comments disabled. As always, you can ask me any Office-related questions you have. If the question is outside of my expertise, I'll try to direct you to where you can get an answer.
Linking Text Boxes, Chart Titles, and Data Labels Dynamically to Data (Excel)
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.
TrackBack URL for this entry:
http://www.arouet.net/cgi-bin/mt2/mt-tb.cgi/18
