How to add a Reference Line in charts in Microsoft Excel

Charts are used to convert the numeric data in a graphical format. Its useful to analyze the data.. It makes it easier to understand large amount of data and the association between different series.

In Microsoft Excel, when we want to present a chart to analyze the movement of data, we need to add a reference line, so that we can easily understand how the performance is & where there needs to be improvement.Let’s take an example and understand how you can add a reference line to a chart.

Let’s take an example :-

We have sales performance data, in which we have the agents name and the amount of achieved sales. To insert a horizontal average line in the Chart follow the below given steps:-

    • Select the range of data, go to the insert tab, click on column chart, click on 2D chart.

 

img2

 

    • After inserting the chart two contextual tabs will appearnamely, “Design” and “Format”.

 

img3

 

    • From the “Design” tab choosethe “Chart Style”.

 

img4

 

img5

 

    • To add the reference line in the chart you need to return the average of sales amount.
    • Write the average formula in data cell C6 =AVERAGE ($B$6:$B$15). The function will return 5.95

 

img6

 

    • Select the chart area the data will get highlighted in a blue color line, drag it till the end of the data.
    • Another way to add the data in the chart, go back & click on the chart.
    • Right click on the mouse then click on Select data you’ll see that the window will open, select the data source, click on chart data range select your data range and click ok.

 

img7

 

Conclusion:- Now you can see the average line in the above chart which is clearly showing agents whose performance is above and below the target.

image 29

 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook

We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

Users are saying about us...

  1. Very brilliant you explained.

    This is really easy for us learn the Excel from you, In a very simple way you taught us how we can add the reference line in the chart. Thanks :)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube