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

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:

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:
   

   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.
   
4. In the Select Data Source dialogue window, click the Add button under Legend Entries (Series):
   
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.

   

   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).
   
7. Right-click on the vertical error bar and choose Format Error Bars… from the context menu.
   
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.

   

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.
    

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:

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:

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 to a second cell without changes.
   

3. Right click anywhere in your bar chart and click Select Data in the context menu:
   
4. In the popped-up 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.

   

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.
   
7. In the Change Chart Type dialog window, do one of the following depending on your Excel version:
   • In Excel 2013 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.

   

8. In 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.
   
9. In the Select Data dialog, 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.

    

    A vertical line appears in your Excel bar chart, and you just need to add 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:
    
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.
    
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.
    

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

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.

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.

   

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

   

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

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

Make a vertical line interactive with scroll bar

To interact with the chart directly, you insert a scroll bar and connect your vertical line to it like shown below.

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.

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:
   
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….
   
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.
   
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

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 has taken quite a bit of time, but it's worth it. Do you agree?

Practice workbook for download

Excel Vertical Line - examples (.xlsx file)