by Svetlana Cheusheva, updated on
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, 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!
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:
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.
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.
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:
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:
| 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.
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.
That's it! Your bar chart with a vertical average line is finished and good to go:
Tips:
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:
To add a vertical line to an Excel line chart, carry out these steps:
=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.
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.
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!
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:
=$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?
That's how you create a vertical line in Excel chart. For hands-on experience, please feel free to download our sample workbook below. I thank you for reading and hope to see you on our blog next week!
Excel Vertical Line - examples (.xlsx file)
Table of contents
