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.

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

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

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

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.

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.

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

AXE
June 2, 2015 at 6:55 am

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 🙂

Reply ↓