Creating Dynamic Excel Chart Legends that Link to Worksheet Cells

Charts

Want to create Excel chart legends that show additional information? Wouldn’t it be great to have a multiline legend that has notes in it. Just add a dynamic legend that updates when worksheet data updates. It’s pretty easy to make a chart like this one that shows custom legends in a cell and that has multiple lines.

 

Create dynamic legends that link to cells and show multiple lines.

Create dynamic legends that link to cells and show multiple lines.

This chart shows the simple legends East and West that Excel creates automatically from labels over data.

Excel chart legends normally show the data label.

Excel chart legends normally show the data label.

There’s more information about a new product introduction that could be included in the legend. Cells J2 and J3 could be linked to the legend so the legend updates when the cell content changes. Cell J2 contains a formula that produces a text title, a line break (hidden in the cell), and a date that comes from cell F3.

The formula in cell J2 is,

=”East w New Product”&CHAR(10)&TEXT($F$3,”mmm d, yyyy”)

In this formula the “East w New Product” is text. The “w” is shorthand for “with”. The CHAR(10) is a function that inserts the line break character in the middle of a line of text. The CHAR(10) is normally sent to a printer to indicate a line break, but here it is used to trick the legend into creating a second line. The TEXT($F$3,”mmm d, yyyy”) function takes the date in F3 and formats using the custom date format within the quotes. The & symbol is a shorthand for the CONCATENATE function. It joins all these pieces of text together.

So J2 and J3 contain two custom legends that we want to use as replacements for the legends that Excel used from cells C7 and D7.

To link the legend titles to cells J2 and J3 just follow these steps,

1. Select the chart. If your chart does not have a legend, then choose Chart Tools Layout > Legend and select a position for the legend. The figure has the legend at the bottom so we have more space to work with.

2. With the chart selected, choose Chart Tools Design > Data > Select Data to display the Select Data Source dialog box. In this dialog box you can edit which titles are used for chart series titles, horizontal and vertical data axis.

Link the Excel chart legend to the worksheet cell.

Use the Select Data Source dialog gox to link series titles to worksheet cells.

3. Under Legend Entries (Series) select the legend heading “East” then click Edit to open the Edit Series dialog.

Link the Excel chart legend to the worksheet cell.

Link the Excel chart legend to the worksheet cell.

4. In the Series Name text box change the cell reference from $C$7 to cell $J$2, the location of the new East legend title. Click Ok.

What you have done is switched the legend from the contents of cell C7 to J2.

After switching the East title to reference cell J2 you can see the new title in the Legend Entries (Series) list.

After switching the East title to reference cell J2 you can see the new title in the Legend Entries (Series) list.

5. Repeat the same process for the West title and link it to cell J3, then click Ok.

The Excel chart now shows a custom legend linked to worksheet cells. The legend even contains an extra line so you can show additional information.

After switching the East title to reference cell J2 you can see the new title in the Legend Entries (Series) list.

After switching the East title to reference cell J2 you can see the new title in the Legend Entries (Series) list.

The chart now shows your custom titles in the legends. In addition, you’ve created an extra row in the legend for data or comments.

 

 

Share the power...