Add Vertical Line in a Chart

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. Please download this sample file from here to follow along.

How to Insert a Vertical Line in a Chart

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.

  • Enter a new column beside your quantity column and name it “Ver Line”.
  • Now, enter a value “100” for Jan in “Ver Line” column.
insert column to add a vertical line in excel chart
insert line chart with markers to add a vertical line in excel chart
  • You’ll get a chart like this.
default line chart to add a vertical line in excel chart
change to secondary axis to add a vertical line excel chart
  • Now, next thing is to adjust axis values for your column bar.
  • Double click on your secondary axis to open formatting options.
  • 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 & select none (This will hide secondary axis, Yup we don’t need it).
hide secondary axis to add a vertical line excel chart
  • Last but not least, we have to make our column bar little thin so that it will look like a line.
  • 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 excel chart.
how to add a vertical line in a chart in excel

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.

Check this out ➜ Add Horizontal Line in a Chart

Steps to Add a [Dynamic] Vertical Line in a Chart 

Now, it’s time to level up your chart and make dynamic vertical name.

Follow these simple steps.

add scrollbar to add a vertical line excel chart
  • Right click on your scroll bar and select format control.
  • Link your scroll bar to cell C10 & enter 12 for maximum value. Click OK.
format control scrollbar to add a vertical line excel chart
  • Now, go to your data table and insert following formulas to the cell C2 & drag down up to the last cell of the table.

=IF(MATCH($A2,$A$2:$A$13,0)=$D$1,100,””)

enter formula to add a vertical line excel chart

Now you can use the scroll bar to navigate your vertical line in your chart.

use scrollbar to navigate to add a vertical line excel chart

Download this sample file from here to learn more about this

Check this out ➜ Advanced Excel Charts

Conclusion

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.

😃

Now tell me one thing. Have ever tried to do this before with your chart? Please share your views in the comment section I would love to hear from you.

And, please don't forget to share this tip with your friends.

  • Asif Hafeez

    Thanks Puneet, nice sharing and I second your statement it is an easy method.

    • Puneet Gogia

      Hey Asif,
      Thanks for your words.

  • mma173

    Thanks’ I make use of this.

    • Puneet Gogia

      Welcome.
      I’m so glad you found this post helpful!

  • Novzar

    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?

    • Puneet Gogia

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