Sorry I have been swamped on a new project in Australia and Singapore so my posts have been less frequent than I wish. We had a successful launch in Melbourne and Perth. I am off to Darwin and Singapore next so hopefully I can get more posts completed on the flights.

Anyway, on to more Excel Dashboarding

So I have a good friend that was here at the start of my Excel Dashboard Template blog and his name is Don. Don is a retiree living in the Mid-west of the USA and he likes to track things using Excel and then he likes to see his data visually. Don also likes to use Excel to track everything about the Doctor Who series. But that is for another post.

Recently, Don sent me an email and asked if I could help him add a vertical line to a chart in Excel. The Excel chart was a line chart. So he sent me a sample chart (see the Excel Line chart pictured below) and he wanted to add 2 vertical lines. One above Tuesday and one above Thursday. Below are step by steps for each of the 3 methods and video is linked at the end of the post.

Here is what the final chart should look like:

So in this post I will show you 3 ways that you can Add a Vertical Line to an Excel Line Chart.

So we will start with this data:

**1) Add a Vertical Line using Excel Shapes**

The 1st and typically easiest way to Add a Vertical Line to an Excel Line Chart is to just draw an Excel Shape of a Line on top of the Excel Line Chart.

A) Create your Excel Line Chart

B) Select Insert Ribbon

C) Select Shapes in the Illustrations Group

D) Select the Line without arrows in the line section

E) Draw your Line on top of the Excel Line Chart you have already created.

F) With the Line selected, Press CTRL+1 to format the Excel Shape

G) Change the Width to 3pt

H) Change the Line Color to Red

This is really the easiest way to add a line, HOWEVER, the line is not very dynamic and may get “OUT OF LINE” if you will when you add new data points to your line chart. So you will have to constantly adjust the line as the chart adjusts.

**2) Add an Additional Column Series**

This is my **recommend**ed way to quickly and easily add as many vertical lines as you want to your Excel Line Chart. It is dynamic and can be controlled with formulas and it will move and adjust as you add data points.

A) Add an additional column of data that will create an additional line series in your Excel Chart. You will see that I have added a data point for each day at a value of 12. This is 20% above the real data because I want the vertical lines to go above the data and reach the maximum vertical position of the vertical axis. (Note, you will set this vertical axis maximum as detailed below.)

B) Highlight the New Data Range and Create your Line chart. You will have 2 horizontal lines and your graph will look like the picture below. Note, you can see the red horizontal line (titled Vertical Lines) because we have put a value for every day. If we had only put a 12 on Tuesday and Thursday, we would not have seen the line because they would have been two data points that were not adjacent, thus it would not depict a line for you to easily see and select.

C) Right Click on the Vertical Line Series. In this case, the Red Data Series. Then from the Pop up menu, choose “Change Series Chart Type…””

D) Change the Excel Chart Type to a 2-D Clustered Column Chart.

It will then look like this:

E) We are getting very close. Lets change a few things. First, we wanted to only have vertical lines at Tuesday and Thursday. So lets delete the Vertical Lines data values from all days except Tuesday and Wednesday. Your chart will now look like this:

F) Now lets make the columns look more like lines by changing some of their formatting. First, right click on either column and then choose the “Format Data Series…” from the pop up menu.

and then move the Gap Width slider to the right toward the Large Gap. The farther right you move it, the smaller the column will appear. It can get very small and will almost appear as a line.

On a line chart with very few horizontal categories, the column will not get much thinner than this:

But when you more than about 20 categories, the column will get very very thin and will appear as a line. Compare these vertical columns/lines with the ones in the picture above. You will see that they are thinner than the ones above, but the ones above are very acceptable to me.

G) Okay here is the final step for the easy dynamic vertical lines. Right click on the Primary Vertical Axis and then choose “Format Axis…” from the Excel pop up menu.

Then from the Format Axis Dialog Box, you should change the Axis Options Maximum to 12 (equal to the amount you have in your vertical lines column.

This will make the lines appear that they go from the bottom of the vertical axis to the very top. Here is your final chart.

Looks good to me. What do you think? Let me know in the comments.

Alright, you may be saying that I have tried this, but I noticed that your vertical lines using this technique all have the same formatting like the fill color. And you are correct. Here is the way to add one line for Tuesday and one line for Wednesday.

a) Add another column of data for each line. In the picture below, I made the 2nd vertical line data set = 10 for each value so that you can discern each line individually. If I set them both to 12, they would overlap and you would not be able to tell them apart as easily. You can all ready see that the 2 lines have different colors. Your data set and new line chart will look like this:

Then you will have to change each series chart type to Clustered Column Chart and follow each step from C thru F. Also, you will want to change the values for the Vertical Line 2 data series back to 12 so that it will reach to the top of the chart. When doing so, your chart will now look like this:

**3) Add a Vertical Line using an XY Scatter Lines**

Okay, here is the 3rd way to add vertical lines to your Excel Line Chart. There is a 4th way that I discovered, but it is a real pain in the you know what. If you want to know more about this 4th way, you can see this on this post:

The 3rd way is a little trickier to complete, but I am sure that if you follow my steps that you too will be able to do it. This is a great technique to master because it will allow you to add any line to most 2D chart types.

A) Create your Excel Line Chart with the original set of data that looked like this:

B) Now create 2 sets of data anywhere in your Excel Spreadsheet that looks like this:

C) Now I recommend that you highlight and COPY the Tuesday data from the upper right hand corner down one cell to the 12. Your range would look like this:

then Select your Chart and select “Paste Special”. You will then see this Dialog Box:

Then click on OK and your chart will now look like this:

D) Now you may say that this is NOT a vertical line. And you are correct . However, here is how we start the transformation to an XY Scatter Chart. First, Right Click on the Vertical Line Series. In this case, the Red Data Series. Then from the Pop up menu, choose “Change Series Chart Type…””

and then change the chart type to an XY (Scatter) – Scatter with Straight Lines:

Your resulting chart will now look like this. Doesn’t look any better, but we are now very very close

E) Now select your chart and then from the Design Ribbon, choose the “Select Data” button from the Data Group.

and you will then see this dialog box. You will then want to select “Series2” from the Legend Entries (Series) area. then click on “Edit”

Now when you click edit, you will see the Edit Series Dialog Box. Give your series a title, so that you can easily recognize it in the future. Then change the Series X Values to the X’s for Tuesday and change the Series Y Values to the Y’s for Tuesday.

And then press OK but don’t close down the “Select Data Source” dialog box as you need it in the next step. Now looking at the chart, you will see a Vertical Red Line in your Excel Line Chart.

F) Now we have only created one line, so lets create the 2nd line. To do this, you need to ADD an new series in the Select Data Source dialog box.

From the Edit Series dialog box, add the Thursday vertical line data by adding the X’s and the Y’s like this:

then go ahead now and click on the OK from the “Select Data Source” dialog box. IMPORTANT: if you click “Cancel” at this step, you will have to start over editing BOTH XY data series as Excel will think you want to start over with the edit of all series for the chart since you stated to edit them.

Your chart will now look like this:

This is when you say Steve, why is the data not lined up with Tuesday and Thursday?

Well that is because we need to do one final step! That is to simply delete the Secondary Horizontal Axis and Secondary Vertical Axis.

G) Click on the Horizontal Secondary Horizontal Axis and press the delete key on your keyboard and then select the Secondary Vertical Axis and press your delete key. And boom goes the dynamite and you get this chart:

H) Okay here is the final step for the actual vertical lines. Right click on the Primary Vertical Axis and then choose “Format Axis…” from the Excel pop up menu.

Then from the Format Axis Dialog Box, you should change the Axis Options Maximum to 12 (equal to the amount you have in your vertical lines data.

This will make the lines appear that they go from the bottom of the vertical axis to the very top. Here is your final chart:

These are actually lines, not columns, but I think either can be fine. Looks great to me. What do you think? Let me know in the comments.

**VIDEO Demonstration**

Additional Tutorial mentioned in the video:

**how-to-select-data-series-in-an-excel-chart-when-they-are-un-selectable**

I want to thank each and every one of you again for subscribing to my blog and making it so successful. THANK YOU!

Steve=True

Steve – Thank you very much for your help.

The video helped me a lot, I will save this so I will be able to

do it agin in the future.

Thank you again for your knowledge and help.

I enjoy your website very much.

Don Heeter

I have a bar chart showing ranges of minutes that a task takes (e.g. >0-5, >5-10, etc.) along the x-axis and frequency counts for the y-axis. I can calculate various statistical measures such as the minimum, maximum, and median number of minutes a task takes. I cannot figure how to put in a vertical line to show the median. Certainly, your first technique works, but I would like it to be responsive to the data. I’ve tried your other two methods and have failed. I note that when I go to Select Data in a chart and Add a Series, the dialogue box I get only shows boxes for Series Name and Series Values, not the three your examples show (Series Name, Series X Values, and Series Y Values). Nor, if I copy the two cells in your third method and go into a chart and right click, do I get the option of a Paste Special.

Hi there.

It has been awhile since I posted this tutorial, so bear with me if I misspeak to the steps.

For your last note, you will not see the Paste Special in the right click on the chart. First select the chart and then go to your home ribbon and from the paste button you will see paste special.

Secondly, if you are not seeing Series Name, Series X values, Series y values, you need to change the chart type for that series to an XY scatter chart first. It is probably still set as a bar chart type.

Let me know if you are still having problems.

Steve=True

You were right. The problem was that the second data set did need to be changed, more specifically to an “XY Scatter With Straight Lines” chart. Thank you.

P. Kimmel,

Wonderful. I glad I could help you troubleshoot your problem. Thanks for visiting my blog and the comments!

Steve=True

This was very helpful, thank you! I managed to add a row to my raw data that puts the Maximum Axis value under the column for the current day (using a “=TODAY()” formula and a couple “=IF” and “=AND” formulas). Now I have a vertical line in all my line graphs marking the transition between historical data and my future projections!

Cheers!

Katie, I am so glad it helped. Sounds like you have created an awesome solution in your chart. Hope your executives appreciate your hard work. Steve=True

Thank You !

This was very helpful… especially the third solution !

You are welcome Francesco. So glad it helped! Steve=True

Excellent – well explained – thank you

Thanks Echo for the nice comment. Glad it helped. Steve=True

Thanks a ton; this was most useful! Your screen shots made the whole process a breeze. I used #3.

Hi Nandita, I am so glad to help. Thanks for letting me know which one you liked best. Steve=True

I used #3. It worked out well. But I had a problem when I was trying to copy the sheet which had this graph to another one and referenced some other data other than the one used in this graph. The line graph didn’t work at all. Is there a way to fix it ?

Hi Raghav,

I am not sure why it wouldn’t work there. Perhaps you need to change the reference cells by editing each series?

Check out this post and it may help on why this is happening when you copy the chart and not the entire sheet.

https://www.exceldashboardtemplates.com/how-to-copy-charts-and-change-references-to-new-worksheet/

Hope this helps!

Steve=True

Thank you! I am working on my undergraduate dissertation and could not get a grip on how to get excel to do what I needed. This post was extremely helpful!

Gina, I am so glad to help! Can I get a footnote 🙂 lol. Good luck with your paper! Thanks again for the great comment. Steve=True

Option 2 worked great. I discovered you can achieve a thinner line if you add a thick white border

Jeff. Glad you liked one of the options. Great idea on the thinner line! Thanks for the comment. Steve=True

Excellent article – I tried a couple of the options and the third option using XY scatter lines worked perfectly for what I needed. Definitely bookmarking for future use!

Jen, thanks for the great comment. Glad you will be back! Also glad that your XY scatter line worked for you.

Hi Steve, This was really useful. Option 2 worked a treat but the columns were a bit thicker than I wanted so I’m trying option 3 but when I delete the secondary horizontal axis the whole stacked line chart disappears. I found I can leave the axis in place and just format the axis text as “No fill” to hide it.

The other problem I had was lining up my “lines” (which were year dividers) with the correct months on a production report running from Oct-14 to Dec-16. I got round this by having my axiz bounds at 1 – 25 and then January 15 value was 4.5 and January 16 value was 16.5. Seemed odd but worked.

Thank you very much for providing this solution.

You are welcome. Thanks for the great comment and the workaround.

This tutorial is very helpful..

Thanks Mike

Thanks Steve! I used method 3 which I found to be very helpful!! A lot quicker than I thought too! Appreciate your time!

Thanks Tara, so glad to help!. I appreciate your comment as well.

Excellent! How do I make the vertical line got into the negative part of my graph? I have percentages that range from 30% to -50% and at the moment the line only goes to 0.00%.

Hi Gerri, you can make the XY coordinates negative, so that should work.