How to Add a Vertical Line to a Horizontal Bar Chart

Turning the Series into a Vertical Line

We’ll now see the bars from the Average series on the chart, but we want the Average value to be a vertical line. To do this, right-click on one of the bars and choose Change Series Chart Type.

Change Series Chart Type

In the Change Chart Type dialog that appears, choose the X Y (Scatter) option that shows straight lines with no marks.

Change Chart Type

Excel isn’t smart enough to display the line the way we want so we need to go back and edit the series again. Select the chart, and choose Select Data from the Chart Tools’ Design menu again.

Select Data

In the Select Data Source dialog, choose the Average series and click Edit.

Edit Series Button

We need to specify Series X and Series Y values for our vertical line. Series X Values should be the place on the x-axis where we want the vertical line. In the example, this is the Series X Value:

=Sales!$C$18:$C$19

Series Y Values just need to be two values that are separated by a set interval. The numbers don’t matter so we can use 0 and 1. In the example, this is the Series Y Value:

=Sales!$B$18:$B$19

Edit Average Series

Click OK and you will see the vertical line at the average value on the chart.

Average Line Graph

To make the line span the entire chart, we need to adjust its y-axis. Double-click the secondary axis on the right side of the chart.

Secondary Axis

In the Format Axis dialog under Axis Options, set the Minimum and Maximum as the numbers specified in the average value series (in the example, the Minimum is 0 (zero) and the Maximum is 1).

Format Axis

Once you’ve adjusted the secondary y-axis limits, you can delete it from the chart. Select the numbers on the right side and press Delete. You’ll be left with a clean vertical line on the horizontal bar chart.

Clean Average Line

Now, you can customize the colors and design of the chart to emphasize the data you want to focus on. Your results might look something like this:

Final Chart

Get the latest Excel tips and tricks by joining the newsletter!

Andrew Roberts ThumbnailAndrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it.

Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!

24 thoughts on “How to Add a Vertical Line to a Horizontal Bar Chart

  1. Great guide, thanks so much. I had almost given up on being able to make the report in Excel the way I’d been asked to!

    1. Hi Bilo!

      The article is broken up into steps to make it easier to follow (it’s very long otherwise!). Please click on the link to page 2 at the bottom or go to step 2 in the Quick Navigation box at the top to get to the next step in the process!

  2. Bilo:

    No, it’s not just you. At the top of the page in the “Quick Navigation Box” there are links to several pages. The first, “1. Building a Basic Horizontal Bar Chart” shows how to create a chart. In order to add a horizontal line, you need to go through all of the links, from 1 to 5.

  3. This is really.. Been pulling my hairs to combine a horizontal bar and line chart for a long long time.. Thanks for sharing

  4. hi, currently, i’m using your method to have 2 lines to have a “selected area”. do you know if its possible to do a box instead of 2 lines? thank you

  5. Great post. Not at all obvious or easy, but however you worked it out, well done, and thank you for sharing.

Leave a Reply

Your email address will not be published. Required fields are marked *