How to Show a Formula Result in an Excel Chart Legend.
Last updated on 2018-09-12 by David Wallis.
“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?”
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")
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:
- Click the chart
- Click one of the bars in the 2017 figures
- In the formula bar you will see =SERIES(Sheet4!$B$1,Sheet4!$A$2:$A$13,Sheet4!$B$2:$B$13,1)
- Make the first argument in the SERIES function reference cell A17, instead of the $B$1
- 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:
Your Support for dmw TIPS
If you’re going to make use of Tips in any of your professional or commercial work, then please consider making a payment in recognition of the time spent publishing this website.
To make a contribution by PayPal in GBP (£ sterling) —
To make a contribution by PayPal in USD ($ US) —
To say how much you want to pay and to receive
a tax invoice for a GBP (£ sterling) contribution —
Thanks, in anticipation.
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.