How to Add a Vertical Line in a Chart in Excel

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.

how to add a vertical line in a chart in excel

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.
    insert-column-to-add-a-vertical-line-in-excel-chart
  • Now, select the entire table and insert a line chart with markers.
    insert-line-chart-with-markers-to-add-a-vertical-line-in-excel-chart
  • Once you do that, you’ll have a chart in like below in your worksheet.
    default-line-chart-to-add-a-vertical-line-in-excel-chart
  • 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”.
    change-to-secondary-axis-to-add-a-vertical-line-excel-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.
    change-axis-value-to-add-a-vertical-line-excel-chart
  • In same axis options, move down to label position and select none (This will hide secondary axis, yup we don’t need it).
    hide-secondary-axis-to-add-a-vertical-line-excel-chart-e1477564590467
  • 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%.
    change-gap-width-to-add-a-vertical-line-excel-chart

Congratulations! You have successfully added a vertical line in your chart.

how to add a vertical line in a chart in excel

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.
    add-scrollbar-to-add-a-vertical-line-excel-chart
  • 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.
    format-control-scrollbar-to-add-a-vertical-line-excel-chart
  • 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,
    =IF(MATCH($A2,$A$2:$A$13,0)=$D$1,100,””)
    (This formula basically takes the value for the position of the scroll bar and then match it with the month list and return the value “100”. So if the scroll bar is at 5th position it will return the value 100 in the cell which adjacent to the May month in the “Ver Line” column).enter-formula-to-add-a-vertical-line-excel-chart
  • Now when you move the scroll bar the value moves in the “Ver Line” column.
    dynamic-data-to-create-dynamic-chart-with-vertical-line.gif
  • The next thing is to insert a line chart and apply the secondary chart for the vertical line as you have learned earlier.
    use-scrollbar-to-navigate-to-add-a-vertical-line-excel-chart.gif

SAMPLE FILE

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?

Make sure to share your views with me, I’d love to hear from you, and please, don’t forget to share this post with your friends, I am sure they will appreciate it, and if you liked this article, then please subscribe to our YouTube Channel for Video Tutorials.

Related Tips

  1. Horizontal Line in a Chart
  2. Interactive Charts In Excel
  3. Dynamic Chart Range in Excel
  4. Dynamic Chart Title in Excel
  5. Copy Chart Formatting to Another Chart
  6. 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 find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

14 thoughts

Leave a Comment

Your email address will not be published.

  1. Thanks a lot. This method works better and faster. More than that, I love the comment section of your articles because the answers provided gives me an added lesson that I would one day live to confront and them solve.
    Thanks again

    Reply
  2. I try to drag the formula in C2 to add dynamic vertical line.But in the cell the message come as #Name ?.Pl help

    Reply
  3. Hi, I’ve had a play with this and the concept makes a lot of sense. This is handy. However, I’ve now got another issue for mine.

    What if my graph also includes negative values? This works well when all values are positive, but my graph actually includes negatives! Thanks in advance.

    Reply
    • This solution works for the dynamic version of the chart:

      In that case, I believe you should have a condition if the value in the selected cell (which you are selecting with a scroll bar) has a negative value then the value for the vertical line also be negative.

      Reply
  4. Ingenious and Intersting!
    I could have used it had I known it a year earlier! When I was updating numerous charts on a weekly basis to depict various facets of my project. My X-Axis was always fixed to span entire 18 month project life span. And I was using an actual vertical line to depict the Status Date and would move it physically on all the charts. The scroll button would have been more professional!
    Is it possible to move the line on multiple charts using the same scroll button?

    Reply
    • Yes you can. Let’s say if you have 1 to 12 scroll bar numbers for the first chart then you can use 13 to 24 for the second chart.

      Reply