dmw | consultancy
tel 01732 833085
tonbridge · kent · UK

How to Show a Formula Result in an Excel Chart Legend.

Last updated on 2018-09-12 by David Wallis.


Question

“I have used this table for a chart and want the legend to show ‘2017 - Average 6.50’ and ‘2018 - 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?”

Excel chart data


Response

Here’s one way of making your legend follow the years in row one 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")

Excel chart formulae

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 to make the chart respond to them:

  1. Click the chart
  2. Click one of the bars in the 2017 figures
  3. In the formula bar you will see =SERIES(Sheet4!$B$1,Sheet4!$A$2:$A$13,Sheet4!$B$2:$B$13,1)
  4. Make the first argument in the SERIES function reference cell A17, instead of the $B$1
  5. Repeat for a 2018 bar, pointing the first argument of its SERIES to cell A18.

Now you should have a legend that looks like this:

Excel bar chart


Give DMW’s Tips Your Support

ContributionIf you intend making use of Tips in any of your professional or commercial work and would like to make a donation in recognition of the time I spend publishing them, then please click Contribution and tell me how much you wish to donate. In response I’ll email you an invoice for payment by PayPal or card.

Thanks, in anticipation.


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.

“I may be a living legend, but that sure don't help when I've got to change a flat tire.

Roy Orbison (1936 to 1988)