A very common question I am asked is, “How do I link text on a chart to text in a cell?” This is really not too difficult, but it’s also not intuitive. Except for axis tick labels, text elements in an Excel chart can be linked to worksheet cells. These text elements include the chart title, axis titles, data labels, and shapes.
To link a text element to a cell, you first must create the text element, using a temporary unlinked label. (Technically a data label is linked to something outside the chart if you use one of the standard options, such as series name, values, or labels, but these are not formulaic links.)
The screen shot below shows a chart, the chart source data range B15:C22, and several labels in columns D and E which will be applied to the chart. You can also see the Name Box in the top left, which usually identifies the selected object, and the Formula Bar to the right of the Name Box.
Link Chart and Axis Titles
Start by linking the title to a cell. A chart with one series uses the series name as the chart title. Select the chart title, and the Name Box shows Chart Title.
Click in the Formula Bar, type =, then click on the cell you want to link to, or type the fully referenced address of the cell. Actually the click in the formula bar is unnecessary, as Excel activates the formula bar when any key is clicked. The link formula appears in the Name Box, and the linked cell is highlighted with the marching ants border.
Press Enter. The chart title updates to show the contents of the selected cell. If the cell contents are changed, the chart title will change accordingly.
Use the same protocol twice more to customize the X and Y axis titles.
Link Data Labels
If you are going to link one or two labels, use the protocol shown here. If you are going to link whole series at a time, this protocol becomes tedious very quickly. Fortunately this can be automated through VBA, and there are several utilities around the internet that do this for you. Two good ones (and free ones) are Rob Bovey’s Chart Labeler and . They install quickly and interface smoothly with Excel.
Add a data label to the first point in the series. Select the series with one click, then select the point with another click. Press CTRL+1 (numeral one) to open the Format Point dialog. Select any of the label options, such as Series Name.
The label has been moved from its default Right position to Above. Select the series of labels with one click, then select the specific label with another click. If the Name Box were wider it would display "Alpha" Point "1" Data Label.
Click in the Formula Bar (optional), type =, and click on the cell you want to link to the data label. The link formula appears in the Name Box, and the linked cell is highlighted with the marching ants border.
Press Enter and the data label updates. Add a data label to the last point in the series, and link it to a cell using this process.
Linking a shape to a cell is a bit trickier. Start by adding a shape to the chart (here the shape is an oval).
Type =, click on the cell that contains the text you want to display in the shape. The link formula appears in the Name Box, and the linked cell is highlighted with the marching ants border.
Press Enter, and if you’re using Excel 2007, the shape picks up the link. Here’s where there’s a bug in earlier versions of Excel. Instead of the selected shape picking up the link, Excel adds a new textbox with the link. Delete the textbox.
A link can be added to the shape using VBA, so I wrote a short procedure to make it happen. Copy this code and paste it into a regular code module (see How To: Use Someone Else’s Macro if you are not familiar with this). Select the shape and run the following procedure.
Sub LabelSelectedShape() Dim rng As Range Set rng = Application.InputBox(Type:=8, _ Prompt:="Select the cell with the intended label for the selected shape.") With Selection .Formula = "=" & rng.Address(External:=True) End With End Sub
Select a cell and click Okay. The oval has been linked to the cell.
Any formula in a chart text element must be a link formula, that is, the formula can only link to a cell or range (including a named range). You cannot write a formula that uses any functions. If you have text in cells A1 and B1, and you want your text element to use the formula ="the fraction is "&A1&"/"&B1, you have to commandeer another cell, say, cell C1, and enter your formula in that cell, then link the text element to the cell with the formula.