How to add vertical line to Excel chart: scatter plot, bar chart and line graph

The tutorial shows how to insert vertical line in Excel chart including a scatter plot, bar chart and line graph. You will also learn how to make a vertical line interactive with a scroll bar.

In the modern versions of Excel 2013, Excel 2016 and Excel 2019, you can add a horizontal line to a chart with a few clicks, whether it's an average line, target line, benchmark, baseline or whatever. But there is still no easy way to draw a vertical line in Excel graph. However, "no easy way" does not mean no way at all. We will just have to do a little lateral thinking!

How to add vertical line to scatter plot

To highlight an important data point in a scatter chart and clearly define its position on the x-axis (or both x and y axes), you can create a vertical line for that specific data point like shown below:
Add a vertical line to a scatter plot.

Naturally, we are not going to "tie" a line to the x-axis because we don't want to reposition it every time the source data changes. Our line will be dynamic and will react to any data changes automatically.

To add a vertical line to Excel scatter chart, this is what you need to do:

  1. Select your source data and create a scatter plot in the usual way (Inset tab > Chats group > Scatter).
  2. Enter the data for the vertical line in separate cells. In this example, we are going to add a vertical average line to Excel chart, so we use the AVERAGE function to find the average of x and y values like shown in the screenshot:

    Enter the data for the vertical line in separate cells.

    Note. If you'd like to draw a line at some existing data point, extract its x and y values as explained in this tip: Get x and y values for a specific data point in a scatter chart.
  3. Right-click anywhere in your scatter chart and choose Select Data… in the pop-up menu.
    Right-click the scatter chart and choose Select Data.
  4. In the Select Data Source dialogue window, click the Add button under Legend Entries (Series):
    Click the Add button.
  5. In the Edit Series dialog box, do the following:
    • In the Series name box, type a name for the vertical line series, say Average.
    • In the Series X value box, select the independentx-value for the data point of interest. In this example, it's E2 (Advertising average).
    • In the Series Y value box, select the dependenty-value for the same data point. In our case, it's F2 (Sales average).
    • When finished, click OK twice to exist both dialogs.

    Select x and y values for the vertical line.

    Note. Be sure to delete the existing contents of the Series values boxes first - usually a one element array like ={1}. Otherwise, the selected x and/or y cell will be added to the existing array, which will lead to an error.
  6. Select the new data point in your chart (orange in our case) and add the Percentage error bars to it (Chart Elements button > Error Bars > Percentage).
    Add the Percentage error bars to the data point of interest.
  7. Right-click on the vertical error bar and choose Format Error Bars… from the context menu.
    Format Error Bars…
  8. On the Format Error Bars pane, switch to the Error Bar Options tab (the last one) and set Percentage to 100. Depending on your needs, set Direction to one of the following:
    • Set Direction to Both if you'd like the vertical line to go upwards and downwards from the data point.
    • Change Direction to Minus for the vertical line to go only downwards from the data point.

    Set Percentage and Direction for the vertical error bars.

  9. Click the horizontal error bar and do one of the following:
    • To hide the horizontal error bars, set Percentage to 0.
    • To display a horizontal line in addition to the vertical line, set Percentage to 100 and choose the desired Direction.
  10. Finally, switch to the Fill & Line tab and choose the Color and Dash type for the currently selected error bar. You can also make the line thinner or thicker by changing its Width.
    Customize the appearance of the vertical line.

Done! A vertical line is plotted in your scatter graph. Depending on your settings in steps 8 and 9, it will look like one of these images:
A scatter plot with a vertical line

How to add vertical line to Excel bar chart

If you'd like to compare the real values with the average or target you wish to achieve, insert a vertical line in a bar graph like shown in the screenshot below:
Add a vertical line to a bar chart in Excel.

To create a vertical line in your Excel chart, please follow these steps:

  1. Select your data and make a bar chart (Insert tab > Charts group > Insert Column or Bar chart > 2-D Bar).
  2. In some empty cells, set up the data for the vertical line like shown below.

    X Y
    Value / formula 0
    Value / formula 1

    Since we are going to draw a vertical average line, we calculate the X value as the average of cells B2 through B7:

    =AVERAGE($B$2:$B$7)

    This formula is inserted in both X cells (D2 and D3). Please notice that we use absolute cell references to ensure that the formula copies into a second cell without changes.
    Set up the data for the vertical line.

  3. Right click anywhere in your bar chart and click Select Data in the context menu:
    Right click the bar chart and click Select Data.
  4. In the popped-up Select Data Source dialog, click the Add button:
    In the Select Data Source dialog, click the Add button.
  5. In the Edit Series dialog box, make the following changes:
    • In the Series name box, type the desired name (Average in this example).
    • In the Series values box, select the cells with your X values (D2:D3 in our case).
    • Click OK twice to close both dialogs.

    Select the x values for the vertical line series.

  6. The new data series is now added to your bar chart (two orange bars). Right click it and pick Change Series Chart Type in the pop-up menu.
    Change Series Chart Type
  7. In the Change Chart Type dialog window, do one of the following depending on your Excel version:
    • In Excel 2013, Excel 2016, Excel 2019 and later, select Combo on the All Charts tab, choose Scatter with Straight Lines for the Average series, and click OK to close the dialog.
    • In Excel 2010 and earlier, select X Y (Scatter) > Scatter with Straight Lines, and click OK.

    Change the chart type to combo chart.

  8. As the result of the above manipulation, the new data series transforms into a data point along the primary y-axis (more precisely two overlapping data points). You right-click the chart and choose Select Data again.
    Choose Select Data again.
  9. In the Select Data dialog, select the Average series and click the Edit button.
    Select the Average series and click the Edit button.
  10. In the Edit Series dialog box, do the following:
    • For Series X values, select two X cells with your Average formulas (D2:D3).
    • For Series Y values, select two Y cells containing 0 and 1 (E2:E3).
    • Click OK twice to exit both dialogs.
    Note. Before selecting the cells with your X and Y values, please remember to clear the corresponding box first to prevent errors.

    Select cells with X and Y values for the vertical line.
    A vertical line appears in your Excel bar chart, and you just need a few finishing touches to make it look right.

  11. Double-click the secondary vertical axis, or right-click it and choose Format Axis from the context menu:
    Right-click the secondary vertical axis and choose Format Axis.
  12. In the Format Axis pane, under Axis Options, type 1 in the Maximum bound box so that out vertical line extends all the way to the top.
    Set the Maximum bound to 1 to extend the vertical line to the top of the chart.
  13. Hide the secondary y-axis to make your chart look cleaner. For this, on the same tab of the Format Axis pane, expand the Labels node and set Label Position to None.
    Set Label Position to None to hide the secondary y-axis.

That's it! Your bar chart with a vertical average line is finished and good to go:
Excel bar chart with a vertical average line

Tips:

  • To change the appearance of the vertical line, right click it, and select Format Data Series in the context menu. This will open the Format Data Series pane, where you can choose the desired dash type, color, etc. For more information, please see How to customize the line in Excel chart.
  • To add a text label for the line like shown in the image at the beginning of this example, please follow the steps described in How to add a text label for the line.

How to add vertical line to line chart in Excel

To insert a vertical line in a line graph, you can use either of the previously described techniques. For me, the second method is a bit faster, so I will be using it for this example. Additionally, we will make our graph interactive with a scroll bar:
An interactive line chart with a vertical line and scroll bar

Insert vertical line in Excel graph

To add a vertical line to an Excel line chart, carry out these steps:

  1. Select your source data and make a line graph (Inset tab > Chats group > Line).
  2. Set up the data for the vertical line in this way:
    • In one cell (E1), type the text label for the data point at which you want to draw a line exactly as it appears in your source data.
    • In two other cells (D3 and D4), extract the X value for the target data point by using this formula:

    =IFERROR(MATCH($E$1,$A$2:$A$7,0), 0)

    The MATCH function returns the relative position of the lookup value in the array, and the IFERROR function replaces a potential error with zero when the lookup value is not found.

    • In two adjacent cells (E3 and E4), enter the Y values of 0 and 1.

    Set up the data for the vertical line.

    With the vertical line data in place, please follow steps 3 - 13 from the bar chart example to plot a vertical line in your chart. Below, I will briefly walk you through the key points.

  3. Right-click anywhere in the chart, and then click Select Data… .
  4. In the Select Data Source dialogue box, click the Add button.
  5. In the Edit Series window, type any name you want in the Series name box (e.g. Vertical Line), and select the cells with X values for the Series values box (D3:D4 in our case).
    Select cells with X values for the Vertical Line series.
  6. Right click anywhere in the chart and choose Change Chart Type from the pop-up menu.
  7. In the Change Chart Type window, make the following changes:
    • On the All Charts tab, select Combo.
    • For the main data series, choose the Line chart type.
    • For the Vertical Line data series, pick Scatter with Straight Lines and select the Secondary Axis checkbox next to it.
    • Click OK.

    Make a Line and Scatter combo chart.

  8. Right-click the chart and choose Select Data…
  9. In the Select Data Source dialog box, select the Vertical Line series and click Edit.
    Select the Vertical Line series and click Edit.
  10. In the Edit Series dialog box, select the X and Y values for the corresponding boxes, and click OK twice to exit the dialogs.
    Select the X values and Y values for the vertical line.
  11. Right-click the secondary y-axis on the right, and then click Format Axis.
  12. On the Format Axis pane, under Axis Options, type 1 in the Maximum bound box to ensure that your vertical line extends to the top of the chart.
  13. Hide the right y-axis by setting Label Position to None.

Your chart with a vertical line is done, and now it's time to try it out. Type another text label in E2, and see the vertical line move accordingly.
Excel line chart with a vertical line

Don't want to bother typing? Fancy up your graph by adding a scroll bar!

Make a vertical line interactive with a scroll bar

To interact with the chart directly, let's insert a scroll bar and connect our vertical line to it. For this, you will need the Developer Tab. If you don't have it on your Excel ribbon yet, it is very easy to enable: right-click the ribbon, click Customize Ribbon, select Developer under Main Tabs, and click OK. That's it!

And now, perform these simple steps to insert a scroll bar:

  1. On the Developer tab, in the Controls group, click the Insert button, and then click Scroll Bar under Form Controls:
    Insert a scroll bar in Excel.
  2. On top or at the bottom of your graph (depending on where you want the scroll bar to appear), draw a rectangle of the desired width using the mouse. Or simply click anywhere on your sheet, and then move and resize the scroll bar as you see fit.
  3. Right click the scroll bar and click Format Control….
    Right click the scroll bar and click Format Control…
  4. Link your scroll bar to some empty cell (D5), set the Maximum Value to the data points total and click OK. We have data for 6 months, so we set the Maximum Value to 6.
    Link the scroll bar to some cell and set its maximum value.
  5. The linked cell now shows the value of the scroll bar, and we need to pass that value to our X cells in order to bind the vertical line to the scroll bar. So, delete the IFERROR/MATCH formula from cells D3:D4 and enter this simple one instead: =$D$5

Bind the vertical line to the scroll bar.

The Target month cells (D1 and E1) are not needed any longer, and you are free to delete them. Or, you can return the target month by using the below formula (which goes to cell E1):

=IFERROR(INDEX($A$2:$A$7, $D$5, 1), "")

That's it! Our interactive line chart is completed. That took quite a bit of time, but it was worth it. Do you agree?

Interactive line graph with a vertical line

That's how you create a vertical line in Excel chart. For hands-on experience, please feel free to download our Excel Vertical Line sample workbook. I thank you for reading and hope to see you on our blog next week!

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard