Sometimes while presenting data with an Excel chart we need to highlight a specific point to get user’s attention there.
And the best way for this is to add a vertical line to a chart.
Yes, you heard it right.
You can highlight a specific point on a chart with a vertical line. Just look at the below line chart with 12-months of data.
Now, you know the benefit of inserting a vertical line in a chart. But the point is, which is the best method?
Well, out of all the methods, I’ve found this method (which I have mentioned here) simple and easy.
Without any further ado here are the steps and please download this sample file from here to follow along.Related: Advanced Excel Charts
Steps to Insert a [Static] Vertical Line a Chart
Here you have a data table with monthly sales quantity and you need to create a line chart and insert a vertical line in it.
Please follow these steps.
- First, you need to add a new column and name it “Ver Line” or anything you want.
- After that, enter the value “100” for Jan in “Ver Line” column.
- Now, select the entire table and insert a line chart with markers.
- Once you do that, you’ll have a chart in like below in your worksheet.
- From here, select the chart and open the “Change Chart Type” options from Design Tab.
- After that, change the chart type to the secondary axis and chart type to “Column Chart”.
- Now next thing is to adjust axis values for your column bar so double click, on the secondary axis to open formatting options.
- Next, change the maximum value to 100, as we have entered the same value in “Ver Line” column.
- In same axis options, move down to label position and select none (This will hide secondary axis, yup we don’t need it).
- Last but not least, you have to make our column bar a little thin so that it will look like a line, so, click on the data bar, go to series option and increase your “Gap Width” to 500%.
Congratulations! You have successfully added a vertical line in your chart.
You can download this sample file from here to learn more about this.
Quick Tip: Just enter 100 in the cell where you want to add a vertical line. If you want to add a vertical line in Feb instead of May, just enter the value in Feb.
Steps to Add a [Dynamic] Vertical Line in a Chart
- First, you need to insert a scroll bar and for this, you have to go to the Developer Tab ➜ Insert ➜ Scroll bar.
- After that, right-click on the scroll bar and open the format control options.
- Next, you need to link the scroll bar to a cell (Let’s say C10) so that you can have a number on moving the scroll bar, and you also need to enter the maximum value (enter 12 as we have data for 12 months).
- Now, click OK.
- From here, next, you need to use the below formula to get the value in the cell as per the position of the scroll bar,
- Now when you move the scroll bar the value moves in the “Ver Line” column.
- The next thing is to insert a line chart and apply the secondary chart for the vertical line as you have learned earlier.
In the End
As I said, adding a vertical line in a chart is useful when you want to highlight a specific data point in your chart. There are also some other ways to add a vertical line but I found this method quick and easy. I hope this charting tip will help you to get better at Excel but now tell me one thing, have you ever tried to do this before?
- Horizontal Line in a Chart
- Interactive Charts In Excel
- Dynamic Chart Range in Excel
- Dynamic Chart Title in Excel
- Copy Chart Formatting to Another Chart
- Excel Gauge Chart
About the Author
Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can ﬁnd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.