Shares

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.

# Add a Horizontal Line in a Chart

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.

• It is better to have a comparison to get better insights, even in excel charts.
• A baseline always drives better results.

That is why I decided to use this 30 seconds technique.

Here I have two different uses of this horizontal line.

1. Average Line
2. Target Line
Instant Access: Download this quick PDF guide to learn these steps to add a horizontal line in a chart.

## 1. Add an Average Line to an Excel Chart

The average line plays an important role whenever you have to study some trend lines and impact of different factors on trend.

### Steps to Add an Average Line to a Chart

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.

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.

• Select the dynamic data table which I have mentioned above.
• Now, insert a column chart. Go To Insert → Charts  → Column Charts → 2D Clustered Column Chart. or you can also use Alt + F1 to insert a chart.
• So, here you have a column chart in your worksheet like this.

• Next step is to change that average bars into a horizontal line.
• For this, select the average column bar and Go to → Design → Type → Change Chart Type.

• Once you click on change chart type option, you’ll get a dialog box for formatting.
• Change the chart type of average from “Column Chart” to “Line Chart With Marker”.

• Click Ok.

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.

## Add a Horizontal Target Line in Column Chart

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.

### Steps to Add a Target Line to an Excel Chart

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.

• Select the dynamic table which I have mentioned above.
• Insert a column chart. Go To Insert → Charts  → Column Charts → 2D Clustered Column Chart.
• You’ll get a chart like below.

• Now, you have to change the chart type of target bar from “Column Chart” to “Line Chart With Markers”. To change the chart type please use same steps which I have used in the previous method.
• After changing chart type your chart will look something like this.

• Now, we have to make some changes in this line chart.
• After that, make a double click on the line to open formatting option. Once you do that, you’ll get a formatting option dialog box.
• Make following changes in formatting.
1. Go to → Fill & Line → Line.
2. Change line style to “No Line”.

• Now, go to marker section and make following changes.
1. Change marker type to Built-In, a horizontal bar, & size 25.
2. Marker fill to solid fill.
3. Use white color as the fill color.
4. Make border style to a solid color.
5. And, black color for borders.

• Once you make all the changes to the line you’ll get a chart like I have below.

## Conclusion

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.

## Awesome Tips To Create Awesome Charts

• Torstein S Johnsen

Cool tricks!

• Puneet Gogia