How To Add a Vertical Line to an Excel XY Chart

Normally, one turns to the line tool in Excel to manually draw vertical lines on a chart. The issue is that these lines aren’t bound to the x-axis, so any change to the chart is usually accompanied by manual line repositioning. Thankfully, there’s a “hack” that can eliminate the hassle of drawing vertical lines while allowing you to instantly change their position with a few mouse clicks.

Step 1: Create an XY (Scatter) chart…

For this example, theclosetentrepreneur.com feed subscribers for the month of May is being used.

location.gifUsing the Chart Wizard, create an XY (Scatter) chart with time on the x-axis and Generic cialis sale your numeric data on the y-axis. If you want to follow the example in this tutorial, you can download the Excel spreadsheet *HERE*. Be sure to follow all the Chart Wizard steps and choose the “as object in” option to place the chart in your worksheet (we can change the location of the chart later).

Step 2: Add the vertical line data to your chart…

In a separate area on your worksheet, add the info for the vertical line data. Be sure to follow the same data structure that you used for your original data. For this example, the date when theclosetentrepreneur.com was featured on dumblittleman.com is being used as the vertical line data. It doesn’t matter what you name your y-axis data (you will see why “featured on dumblittleman.com” is being used in a moment) or what value you use as long as it is in the neighborhood of the data that has been plotted.

vert_data.gif

Once you’ve entered your vertical line data, select it and place your mouse over the select border until you see the “four arrow” cursor appear. At this point, click the selected data and drag it to your chart.

click_n_drag.gif

A “Paste Special” dialog box will appear, be sure that the options match those shown in the picture below:

paste_special.gif

Step 3: “Hacking” the error bars to create a vertical line…

format_series.gifAfter dropping the vertical line data in your chart, you should see a singe point appear representing your vertical line data. Click on the data point once to select it, then right click on the data point and choose “Format Data Series…”. In the “Format Data Series…” dialog box, click the “Axis” tab and select “Secondary axis”. Then click on the “Y Error Bars” tab and select the “Minus” option under Display. Change the error amount to percentage and enter 100% for the value. Click “OK” and your data point will now appear on a secondary axis with a vertical line underneath it which was created by the “minus” y-error bar. Changing the date and Online pill viagra value of the vertical line data will move the line around as you wish, and the vertical line will remain in place when resizing or adding more data to the chart.

chart.gif

Tips on formatting your chart…

You’re basically done at this point and although this technique for adding a vertical line is useful, it’s not easy on the eyes. So to improve the appearance of your chart, you can format the vertical bar by clicking on it once to select it, then right clicking on the bar and selecting “Format Error Bars…” and click the “Patterns” tab to change the look of the error bar as you wish.

In addition, you can hide the secondary axis by clicking on it once to select it, then right clicking the secondary axis and select “Format Axis…”. Choose the “Patterns” tab and you can choose “None” for all the line and tick mark labels to hide the axis on the chart. In addition, you can change the scale of the axis to expand or contract the vertical line as you wish.

And last but not least, you can access the “Format Data Series” dialog box for the single vertical line data point (as was discussed above) and add the y-axis column label to your line by selecting the “Data Labels” tab and checking “Series name” under the Data Labels section. In this example, doing so will add “featured on dumblittleman.com” to our line. With a little additional formatting, you can end up with something like this:

final.gif

In conclusion…

This technique may seem like a lot of work, yet it’s easy to get the hang of once you’ve done it yourself. Plus, you can change the chart any way you like and not have to worry about manually moving vertical drawn lines around to compensate for any changes. Also, the vertical line location is accurately displayed, which isn’t necessarily the case with a manually inserted line. Ultimately, there are numerous ways to insert vertical lines into an Excel chart and hopefully this technique gives you a different perspective on the process. If you have any questions or suggestions, feel free to leave a comment below!

Sincerely,
The Closet Entrepreneur

» This entry was filed under Excel and tagged with: , ,

70 Comments

  1. Great tip. This is the kind of thing I’ll never be able to remember how to do, but will remember that it can be done. The power of bookmarks.

  2. TOMAS

    Hi Ed, I’m glad you enjoyed the tip. BTW, your site looks great!

  3. Mik

    Do you know how to do that with the chart type “line graph”?

  4. TOMAS

    @Mik: The process is very similar, just a bit more cumbersome. Follow the instructions through Step 2 where you drag the vertical line data to the chart (four arrows appear and then drag to chart). The difference is that you won’t get the “Paste Special” dialog box, instead the data will automatically be added and Buy clomid online australia everything in the chart will change to a single point because your x-values will be swapped to the vertical line data’s x-values.

    To make the chart look normal, click on the single point that represents your original data to select it. This should highlight the data on the sheet and show how the x-values for your original data is now coming from the vertical line data. You can drag the box to the original data’s x-values. Once you do so, your vertical line data’s x-values are also switched (this is why using an x-y scatter is so much easier). To fix this, click on the vertical line data point to select it, then right click and choose “Format Data Series…”. Go to the “Axis” tab and select “Secondary Axis” and click “Ok”. Now click on the vertical line data point again to select it, and the data should be highlighted on the sheet. From here, you can drag the x-values to the correct vertical line data x-value. Now to finish everything, select an empty cell to deselect all point and select the entire chart. Right click and choose “Chart options” and click on the “Axes” tab. From there, place a check mark next to “Category (X) axis” and your vertical line data should finally appear in the right place. From here, you can follow step 3 to format the line and make it appear correctly.

    I know it’s a lot of work, but let me know if you need any help or run into any problems (or just use an x-y scatter chart :) )

  5. Thanks! This really helped me at work!

    Sean

  6. TOMAS

    @Sean – Glad to know that you were able to make use of the tutorial. Nice blog by the way!

  7. Yep, I read a lot of great tips everyday that easily slips my mind, but when the time comes, I’ll find my way to them and that’s when I put them into good use. Thanks.

  8. Ari

    Splendid! Thanks so much for these instructions. One would surmise that Microsoft could build in a vertical line function with some ease, but obviously they have other, more important things to do. Like taking five years to come out with a sub-par, top-heavy OS that no one buys. Makes me even more glad to be a Mac person…

  9. Andrea

    Thank you, thank you, THANK YOU! I have been struggling to create this type of chart for days. (Microsoft tutorials are useless!) I have put together a real kick-ass analysis report for my boss and look the hero again! Book marked for future reference.

  10. TOMAS

    @Spongeblog, Ari, and Andrea: Glad you were all able to put the tutorial to good use! There’s a statistical software called JMP that has a vertical line feature but the interface is a bit clunky, but you would think that Excel users at one point or another would find a need for such a feature. Oh well, for now we’ll just have to hack away. :)

  11. stephanie

    Fantastic

    I am a PhD student and really needed this information. Itís allowed me to compare a mineral against a library of known peaks.
    You also explained it very nicely and it was easy to follow.
    I just finished all my graphs and Buy cialis without prescription i can now impress my boss on Monday

    Thank you

  12. Adrian

    Great tip, I have bookmarked this, thank you.

  13. TOMAS

    @Stephanie: Thanks for the feedback and I hope your boss was impressed!

    @Adrian: Glad to know that the DIY was worthy of being bookmarked! ;)

  14. Cristiano

    After I’ve seen your suggestion I started experimenting with other possibilities and found the following one, which might be helpful in some cases (Excel 2003):
    Using your example, create an additional column of data, all of them empty for all dates, except for 5/16/07 where you put half the value of the largest number that will appear on the Y axis of the graph. In in this example 50. Create a Line Graph that includes all data sets.
    Once you have the graph, klick on it and locate the single point with the mouse. This was a bit tricky for me because it didn’t show on my graph. I had to hover the mouse pointer over the region where the point should be (at 5/16/07 x 50 in this example). Once you find it, right click and select “Format Data Series”. In the new window select “Y Error Bars” and there “Display Both”. In the field for “Fixed Value” type again the value you you chose for the data point, in this case 50. Click OK.
    There’s the vertical line at exactly the point you want it to be, with the hight you want it to have!

  15. TOMAS

    @Cristiano: Hey, looks like it would definitely work – thanks for the tip!

  16. Adriaan

    Check out the article on this site:
    http://peltiertech.com/Excel/Charts/BarLineCombo.html

  17. TOMAS

    @Adriaan – Thanks for the link!

  18. Anand

    Hey Tomas..This tip is really awesome and saved so much of work for me… I need more than one line in the same graph, I tried the same with my second line and It didnt work… do you know anyother way I can get more than one line?

    Thanks so much for your help.

    Anand

  19. TOMAS

    @Anand – I’m assuming you want more than 1 vertical line in your graph correct? If so, then simply repeat the instructions for a second data set, just be sure that the new list/data is not located under any other lists/data or it will merge the data points and mess up your graph. Check your email as I’ve sent you a crude spreadsheet with two vertical lines inserted.

    ;)

  20. Lena

    Thank you so much for this great tip !
    I got literally mad using manually drawed vertical lines that always moved any time I changed the data. This tip saved me a lot of wasted time.
    I am going to spread it along to my statistician collegues :-)

  21. TOMAS

    @Lena – Yeah, having the drawn lines move infuriated me to the point that I had to figure out something before I threw my laptop out the window! Hope your colleagues put the tip to good use.

    :)

  22. Tyler

    For some reason this isn’t working for me. I followed all the steps and the graph recognizes the secondary x axis but for some reason when I change the secondary x axis value the vertical line doesn’t move. It seems to always go to the first point on the primary x axis. I double checked that the vertical line series is marked secondary axis. Thanks for any help.

  23. @Tyler – As you can imagine, it’s difficult to diagnose what might be wrong without seeing the spreadsheet. Would you happen to have a sample file that you can send me? If so, feel free to send it to tomas(at)theclosetentrepreneur.com.

  24. Jamie

    I’ve been tying to find a solution to this for months, because I have to do a ton of graphs for reports at work with multiple condition change lines, and it gets very annoying manually moving the vertical lines around as the data changes. I tried this method at home, however, and can’t get the “four arrow” cursor to appear. I’m assuming this may be because I’m using Excel 2008 for a Mac. Any suggestions?

  25. @Jamie – I just bought a copy of Excel 2008 for Mac but haven’t had time to install it on my Mac since I’ve been uber-busy trying to get ready for the holidays. Give me a day or two to get it installed and I’ll be sure to let you know when I figure it out!

    :)

  26. @Jamie – I finally got around to installing Excel 2008 for Mac and figured out a workaround. Since the four arrow cursor doesn’t appear (I didn’t for me either), you will have to manually add the second data series to your chart instead of just drag-and-dropping it like before.

    To manually add the second data series, right-click on the chart and select “Select Data…”. In the Select Data Source option box that appears, click the “Add” button located under the Series box (located in the lower left side of the option box). Once you do this, you should see a new series appear with the Name and Cialis canadian pharmacy X values blank and {1} for the Y values.

    For some reason, trying to select the second data series by using the “Chart data range:” field kept messing up my chart so I had to use the individual Name, X values, and Y values fields to add the second data series.

    To do this, simply click inside the “Name:” field then move your cursor to the Y columns name; using the example given in the tutorial above, I would select cell E1 or the cell with “featured on dumblittleman.com” in it. You should see something like “=Sheet1$E$1″ show up after you’ve done this, and of course your formula might be different. Do the same with the remaining X values and Y values fields and select the cells containing your X and Y data (btw, you will have to overwrite the {1} in the Y values field), then click “OK” when you’re done.

    If you’re lucky, you should see a new data point appear in your chart which you can then use to create a vertical line as explained in the tutorial.

    For some reason, the X-Axis on my chart went a little haywire after I added the new data point so I had to undo the changes and write down the values of the X-Axis prior to adding the point; I ended up having to use these values to correct the change in the X-Axis, but other than that everything worked.

    Anyway, I hope this works out for you! :)

  27. Susan

    Finally I can fix the graphs in my article! Thank you so much for (indirectly) helping me out with this great tip.

  28. @Susan – Glad to hear that you were able to fix your graphs using the tutorial, and thanks for leaving a comment! :)

  29. Great guide!!

    It was really helpful for me. Congrats!

  30. @ignacio – Glad to hear you could put the tip to use! :)

  31. Jacco

    Tomas,

    Thanks a million. I was googling this solution in dutch and couldn’t find it after trying it for 2 hours so I almost decided to just draw a line on top of my chart… Big hands up, you helped me a lot with this awesome article!

    Jacco

  32. @Jacco – Thank you, it’s great to know that the article helped you out! :)

  33. ali

    I can\’t get to step 3. When I paste the data for the vertical line, no singe point appears.

    Please help.

  34. @ali – Are you pasting the data into the chart, or selecting it and dragging it over to the chart with your mouse? Also, what type of chart are you using (e.g., line graph, xy scatter) and what version of Excel are you using? I’m hoping to help you out, but just want to see what type of setup you’re using.

    :)

  35. Johnn

    Fantastic. Microsoft should pull their little cotton socks up and put in a vertical line button. Shame on them but glory for you

  36. Pelle

    Tomas,

    Thanks for the tip. One problem remains for me, however: I want the legend to show up for my original data-series, but not for the vertical line. Do you how to achieve this?

    Thanks,
    Pelle

  37. Jack

    I am trying to establish a horizontal line to the y axis and a verticle line to the x axis that will move with the pointer as I traverse the line graph. This will allow a direct reading of the two points for any position on the graph. any suggestions??

    Jack

  38. Lemlem

    Thank you so much.It is very helpful!

  39. Andy

    Awesome tip! You can also do the same thing with a line graph by adding an extra data source. I have a graph for which I wanted vertical lines to delineate each quarter. I added a second data source and Viagra cialis online pharmacy used the Y error bar without having to do any of the “paste special”. The trick here is that I sized the graph exactly. I know it is 16 high, so I can set each error bar (high and low) to 8 and it works. May not work on more fluid charts.

  40. @Johnn, @Lemlem – Thank you for the positive comment! ;)

    @Pelle – I believe that you can choose to show the legend in your options, and then click the data series for the vertical line in the legend and hit delete to get rid of it. Sometimes you have to click the legend twice—the first time will select the entire legend, then the second click to select data series for the vertical line. I’m hoping this works for you. ;)

    @Jack – Unfortunately I’m not aware of a specific method to help accomplish what you’re looking to do, but please share any findings you come across in the comments as I would love to see if someone is able to pull off such an effect! :)

    @Andy – Awesome and thank you for the tip! ;)

  41. derek wu

    Hi

    When I drag in the vertical line data into the chart and click on OK in the paste special box, all the scatter points jumble into 1 vertical section. I can’t get the line to appear around the points. What am I doing wrong here.?

    thanks
    derek

  42. derek wu

    I actually figure it out. This same method doesnt seem to work when your chart is an area chart. Only works if its X-Y scattered. How do I fix it up so that it appears in the area as well?

    thanks
    derek

  43. Bri

    This worked for me the first time really easily. But after that, not so much. Now the point won’t line up with the correct data points. It si automatically going to the first data point. I need to make 27 different graphs but they all need verticle lines. Do I need a new sheet everytime for this to work?

  44. Rob K

    Thanks so much for this tip. I have several spreadsheets that are updated at least weekly so the graph is very dynamic. This automation was extremely helpful and saves me a lot of time.

  45. V Hunter

    This is really good information. I’m new to your site, and glad I found you! Do you have any general information on how to fix a chart whose lines have moved, once sent to someone else. Only two of the several lines actually moved, and this only happens when the person we’ve sent the file to (.xlsx) opens their file.

  46. Mario

    Can this type of line be added to a pivot chart?

  47. Javier

    Hey!This is good information, but i need something else, is there any way to know Yvalues from a chart if you dont have those values in the table?I know I can make an interpolation but it doesnt work, i need exactly the same points that excel puts in the chart

  48. Fantastic article – Thanks !

  49. Suzie

    Could you send me the sample worksheet with two vertical lines, which you mentioned above? Every time I try to repeat the instructions for the first line (which works), nothing new appears anywhere. Thanks

  50. Judy

    Hi,

    I am not sure if anyone can help me on this. If my x axis value(eg. date) is layouted horizontally in the spreadsheet instead vertically in the tutorial. Is the method still useable?
    Thanks.

    Date 01/11/10 02/11/10 03/11/10 04/11/10
    Value 25 75 13 7

  51. Kevin

    I agree, this informatin is great, and this process works perfectly for office 2003, however in 2007 this funtion doesn’t seem to work. Has anyone identified a work-a-round?
    Thanks!

  52. Cristiano

    @ Kevin

    Have a look at my suggestion from March 22nd, 2008 above (look for Cristiano) and see if it works in Excel 2007. Hope it does.

  53. Krzysiek

    Same here, Excel 2011 does not support this feature.

  54. johnq

    Hi,

    I have a couple of questions. Hope you don’t mind answering.

    1) as I drag the ‘vertical line data’ to the chart (plot area), the ‘Paste Special’ dialog won’t show. Instead, the data will hide behind the chart and nothing happens. Is your Excel a version of 2007?

    2) can you do the same thing (a vertical line) on a Pivot Chart?

    Thanks a lot in advance.

    JohnQ

  55. sh0s

    this works in office 2007, just add a new series with the one point.

  56. Rebecca

    Three years after your post this is still helping people – great tip, and with some tweaks worked perfectly in Excel 2011 for the Mac!

  57. Patrick Millner

    Thanks, this is a neat trick but I need to take it a step further. Now that I have my vertical line I would like to shade the area of the chart to the left of the line. Do you have any tips for doing this?
    Many thanks.

  58. Jordan

    Hi, thanks for posting!

    My problem with using Scatterplots is formatting the x-axis. It won’t let me format it as dates, so I have to convert to numbers which is fine except that the intervals are all screwy when leap years hit

    For example, my axis says 1/6/2006, 1/6/2007, 1/5/2008…which just looks bad. Any suggestions?

  59. Jordan

    Hi, thanks for posting!

    My problem with using Scatterplots is formatting the x-axis. It won\’t let me format it as dates, so I have to convert to numbers which is fine except that the intervals are all screwy when leap years hit

    For example, my axis says 1/6/2006, 1/6/2007, 1/5/2008…which just looks bad. Any suggestions?

  60. Guadelupe

    Hi, I have used this from the website you provided (www.peltiertech.com). It works great. Only thing if I want to show the data table it will not work. I have tried other options before but there the additional data will also show on the data table. Does this mean that it is not possible?
    Regards, Guadelupe

  61. Tony

    @Rebecca

    Hey I got excel 2011 for Mac and the vertical line is just not working for me…as I insert for the X axis for the second serie the X axis for first series follow changes as well! Do you have any tips for this one?

    Much appreciated!

    Tony

  62. Tony

    Hey again, just figured it out how to do that, but the problem is remains as fact that the vertical line always at the starting point of the X axis while what I want is to put it in the middle of the graph. doesn’t matter what date I insert it always shows up wrong!

    Thank you for all help!

  63. Eole

    It is almost 5 years now that you posted this article and it is still very useful. I just used it and it worked like a charm with Excel 2010. Thanks a lot-

  64. Erik

    That’s great. I wasn’t able to figure out the error bar method in 2010, but was able to get a trendline to run vertical which was the object of the excersize.

  65. shawn

    i find this absurd. i have a graph that is simply graphing percent accomplished per day and i want a vertical line between a particular Wednesday and Viagra Online Without a Prescription Thursday. this method does not allow me to do that.

  66. shawn

    I want to add that the easiest way to put a line on a chart– especially when the X and Y axis are not based on standard dates or what not– is to copy and past the Excel chart in PowerPoint and just draw the line in that. It is the easiest way to do it for Trial-by-Trial ABA charts or various behaviour datatracking charts of that kind.

Leave a Comment

*
Please type the answer to the math equation. Click the pic to hear the numbers.
Click to hear an audio file of the anti-spam equation