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.
Worth $20, Absolutely Free
While creating a chart in Excel, you can use a horizontal line as a target line or an average line.
It helps you to compare achievement with the target you want to meet or an average you need to maintain.
Let’s say you have an average value which you want to maintain in your sales throughout the year or a constant target which you to show in a chart for all the months.
In this case, you can insert a straight horizontal line to present that value.
But here’s the thing:
This horizontal line can be a dynamic one which will change its value or a line with a fixed value.
And in today’s post, I’m going to show you exactly how you can insert a horizontal line to a chart in Excel using simplified steps.
As I said there are two different uses of a horizontal line i.e. Target line or an average line.
An 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.
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 VLOOKUP 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.
Now let’s move on to the steps.
Here is your ready to rock column chart with a average line. Download this sample file from here learn more.
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.
This is 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.
First of all, let me show you the data which I am using to create a target line in the 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.
Now let’s start with the steps.
Congratulations! your chart is ready.
Download this sample file from here to follow along
So that’s the whole story about a horizontal line.
And, now if someone asks you “how do I add a horizontal line to an excel chart?” you can show them two different methods and also help them to understand why it’s important.
I'm sure this technique will help you to get better at charting.
Now tell me one thing. Have you ever added a horizontal line in a chart? Or, do you have an alternate method for this? Please share with me in the comment section, I would love to hear from you.
And, please don’t forget to share this tip with your friends.