Excel Productivity Guide

Get instant access to this free e-book fully loaded with Useful Excel Tips, 80+ Keyboard Shortcuts, and VBA Codes.

Add a Horizontal Line in a Chart

    how to add a horizontal line in an excel 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.

    Download this dynamic data table from here to follow along.

    use data table to add a horizontal line in excel chart

    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.

    steps to add a horizontal line in excel chart

    • 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.

    change chart type to add a horizontal line in excel chart

    • 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”.

    change chart type to add a horizontal line in excel chart

    • Click Ok.

    Here is your ready to rock column chart.

    How To Add Horizontal Line To Excel Chart Using Best Practices

    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.

    use line chart to add a horizontal line in excel chart

    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.

    use dynamic data table to add a horizontal line in excel 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.

    create a target line if you add a horizontal line in excel chart

    • 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.

    add a horizontal line in excel chart with target line

    • 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”.

    change line type add a horizontal line in excel chart

    • 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.

    changes to add a horizontal line in excel chart

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

    target line as add a horizontal line in excel chart

    Congratulations, your target vs achievement chart is ready.

    Sample File

    download sample excel file

    Conclusion

    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.

    Awesome Tips To Create Awesome Charts