Excel chart legends need not be static text

How to Show a Formula Result in an Excel Chart Legend

Question

I have used this table for a chart and want the legend to show '1998 - Average 6.50' and '1999 - Average 2.25'. The years and the averages in the legend must follow any changes that will occur in the table. Is there a way of doing this?

Spreadsheet


Answer

Here is one way of making your legend follow the years in row 1 and the averages in row 14.

First of all, create this formula in a blank cell, eg. A17:

=TEXT(B1,"0000")&" - Average "&TEXT(B14,"0.00")

Then create this formula in say A18:

=TEXT(C1,"0000")&" - Average "&TEXT(C14,"0.00")

These two formulae generate the legend you want to achieve, making use of the TEXT function to determine the formatting of the numbers involved.

Now go to your chart and choose the Source Data command from the Chart menu. In the Source Data dialog box, make sure the Series tab is selected.

Select the first series in the Series list. Click the Collapse Dialog button to the right of the Name box. Click on cell A17 in the worksheet. Click the Collapse Dialog button again to return to the Source Data dialog box. Repeat the process for your second series.

OK the dialog box and you should have a legend that looks like this:

Chart Legend


Disclaimer

DMW Consultancy Ltd does not accept any liability for loss or damage to data to which any techniques, methods or code included in this website are applied. Back up your data; test thoroughly before using on live data.