Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.
10000+ Copies Already Downloaded
You can add a horizontal line in an Excel chart to use as a target line.
All you need, 30 seconds to add a horizontal line to an excel chart.
And, without much efforts.
You know what, my company’s CEO told me these things a year back.
That is why I decided to use this 30 seconds technique.
Here I have two different uses of this horizontal line.
The average line plays an important role whenever you have to study some trend lines and impact of different factors on trend.
Before I tell you about these steps let me show how I am setting up the data to add a horizontal line to an excel chart. Here I am using a dynamic chart to show you that how this will help you to make your presentation super cool.
Download this dynamic data table from here to follow along.
In above data tables, I am getting data from raw table to the dynamic table by using a combination of VLOOKUP and MATCH. Every time when I change the year in the dynamic table it will automatically change the sales values and the average will be calculated on those sale figures.
And, I am changing year value by using an option button.
Now let’s move on to the steps.
Here is your ready to rock column chart.
One of my colleagues uses this same method to add a median line.
You can also use this method to add an average line in a line chart. Steps are totally same, you just have to insert a line chart instead of a column chart. And, you will get something like this.
Important Note: Average which I have used to insert a horizontal line, is dynamic. It will change when sale figures change.
One more method which I often use in my charts is adding a target line. There are several other ways to create a Target Vs. Achievement chart, but target line method is simple & effective.
So let’s get started.
First of all, let me show you the data which I am using to create a target line in the chart. Here again, I am using a dynamic chart.
I have used above table to get the target and actual figures from the month wise tables. Download sample file from here to follow along.
Congratulations, your target vs achievement chart is ready.
So what do you think about this technique to add a horizontal line in excel chart.
Have you ever added a horizontal line in a chart? If you have never used it before then I am sure you’ll get some inspiration from here to use it.
I bet, these steps will not take more than 30 seconds.
Now here is a challenge for you. Apply these steps and tell me how much time it takes you to add a horizontal line in a chart. Please share your results with me in the comment box.