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.
Using the Chart Wizard, create an XY (Scatter) chart with time on the x-axis and 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.
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.
A “Paste Special” dialog box will appear, be sure that the options match those shown in the picture below:
Step 3: “Hacking” the error bars to create a vertical line…
After 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 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.
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:
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
19 Comments
Ed Kohler
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.
Jun 8th, 2007
TOMAS
Hi Ed, I’m glad you enjoyed the tip. BTW, your site looks great!
Jun 8th, 2007
Mik
Do you know how to do that with the chart type “line graph”?
Jun 25th, 2007
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 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
)
Jun 28th, 2007
Sean
Thanks! This really helped me at work!
Sean
Dec 19th, 2007
TOMAS
@Sean - Glad to know that you were able to make use of the tutorial. Nice blog by the way!
Dec 20th, 2007
spongeblog
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.
Dec 27th, 2007
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…
Jan 2nd, 2008
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.
Jan 3rd, 2008
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.
Jan 13th, 2008
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 i can now impress my boss on Monday
Thank you
Feb 9th, 2008
Adrian
Great tip, I have bookmarked this, thank you.
Feb 13th, 2008
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!
Feb 17th, 2008
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!
Mar 22nd, 2008
TOMAS
@Cristiano: Hey, looks like it would definitely work - thanks for the tip!
Mar 23rd, 2008
Adriaan
Check out the article on this site:
http://peltiertech.com/Excel/Charts/BarLineCombo.html
Apr 18th, 2008
TOMAS
@Adriaan - Thanks for the link!
Apr 18th, 2008
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
May 14th, 2008
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.
May 28th, 2008
Leave a Comment