Creating Links to Cells

You can add cell links to various elements of a chart. Adding cell links can make your charts more dynamic. You can set dynamic links for chart titles, data labels, and axis labels. In addition, you can insert a text box that links to a cell.

Adding a Chart Title Link

The chart title is normally not linked to a cell. In other words, it contain static text that changes only when you edit the title manually. You can, however, create a link so a title refers to a worksheet cell.

To create a linked title, first make sure the chart contains the chart element title that you want. (Use the controls in the Chart Tools Layout Labels group.) Next, select the title, type an equal sign, click the cell that contains the title text, and press Enter. The result is a formula that contains the sheet reference and the cell reference as an absolute reference (for example, =Sheet1!$A$1). Figure 17-3 shows a chart in which the chart title is linked to cell A1.

Figure 17-3: The chart title is linked to cell A1.

Adding Axis Title Links

The axis titles are optional and are used to describe the data for an axis. The process for adding a link to an axis title is identical to that described in the previous section for a chart title.

Adding Links to Data Labels

You probably know that Excel enables you to label each data point in a chart. You do this by using Chart Tools Labels Data Labels. Unfortunately, this feature isn't very flexible. For example, you can't specify a range that contains the labels.

You can, however, edit individual data labels. To do so, click any data label to select them all, and then click a second time to select the single data label. When a single data label is selected, you can add any text you like. Or, you can specify a link to a cell by typing an equal sign and clicking a cell to create a reference formula (such as =Sheet1!$A$1).

New 

Excel 2007 no longer supports linked text boxes in charts.

Adding Text Links

You can also add a linked text box to a chart. The process is a bit tricky, however. Follow these steps exactly:

1. Select the chart and then choose Insert Text Text Box.

2. Drag the mouse inside the chart to create the text box.

3. Press Esc to exit text entry mode and select the text box object.

4. Click in the formula bar and then type an equal sign (=).

5. Use your mouse and click the cell that will be linked.

6. Press Enter.

You can apply any type of formatting you like.

Tip 

After you add a text box to a chart, you can change it to any other shape that supports text. Select the text box and choose Drawing Tools Format Insert Shapes Edit Shape Change Shape. Then choose a new shape from the gallery.