How To Add Straight Line On Excel Graph

Sorry I accept 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 tin can go more than posts completed on the flights.

Anyway, on to more Excel Dashboarding

So I have a practiced 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 meet his data visually.  Don also likes to employ Excel to track everything well-nigh the Medico Who series.  But that is for another mail.

Recently, Don sent me an e-mail and asked if I could assist him add a vertical line to a chart in Excel.  The Excel chart was a line nautical chart.  So he sent me a sample chart (see the Excel Line chart pictured below) and he wanted to add two vertical lines.  Ane in a higher place Tuesday and one higher up Thursday.  Below are step by steps for each of the 3 methods and video is linked at the terminate of the post.

Here is what the concluding nautical chart should look like:

And then in this post I will prove you 3 means that yous can Add a Vertical Line to an Excel Line Chart.

And so we will start with this data:

i) 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 tiptop of the Excel Line Nautical chart.

A) Create your Excel Line Chart

B) Select Insert Ribbon

C) Select Shapes in the Illustrations Grouping

D) Select the Line without arrows in the line department

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

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

Grand) Alter the Width to 3pt

H) Modify the Line Color to Red

This is really the easiest way to add together a line, HOWEVER, the line is not very dynamic and may get "OUT OF LINE" if y'all volition when you add new data points to your line chart.  So yous will accept to constantly adapt the line every bit the chart adjusts.

2) Add an Boosted Column Serial

This is my recommended style to apace and easily add equally many vertical lines equally you want to your Excel Line Chart.  It is dynamic and tin can exist controlled with formulas and information technology will move and adapt as you add together data points.

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

B) Highlight the New Data Range and Create your Line nautical chart.  Yous will accept 2 horizontal lines and your graph volition look like the film below.  Note, y'all can run into the red horizontal line (titled Vertical Lines) considering we take put a value for every day.  If we had only put a 12 on Tuesday and Thursday, we would non have seen the line considering they would accept been two information points that were not adjacent, thus it would not draw a line for you to easily run across and select.

C) Right Click on the Vertical Line Series.  In this instance, the Red Data Serial.  So from the Popular upward menu, cull "Modify Serial Chart Blazon…""

D) Change the Excel Chart Type to a two-D Clustered Cavalcade Chart.

Information technology will then look like this:

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

F) Now lets brand the columns look more like lines by changing some of their formatting.  First, right click on either column and so cull the "Format Data Series…" from the pop up menu.

and then motion the Gap Width slider to the right toward the Large Gap.  The farther right yous move it, the smaller the column volition appear.  It can become very pocket-sized and will most appear as a line.

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

But when you more than almost twenty categories, the column will go very very sparse and will appear as a line.  Compare these vertical columns/lines with the ones in the picture above.  You will encounter that they are thinner than the ones above, only the ones above are very adequate to me.

G) Okay here is the final step for the easy dynamic vertical lines.  Right click on the Master Vertical Axis and so choose "Format Axis…" from the Excel pop upward menu.

So from the Format Axis Dialog Box, you should alter the Axis Options Maximum to 12 (equal to the amount you take in your vertical lines cavalcade.

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

Looks expert to me.  What do yous think?  Let me know in the comments.

Alright, you may exist saying that I have tried this, merely I noticed that your vertical lines using this technique all have the same formatting like the fill up color.  And y'all are right.  Here is the way to add one line for Tuesday and ane line for Wednesday.

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

And so you lot will take to modify each serial chart type to Clustered Cavalcade Chart and follow each stride from C thru F.  Too, y'all volition want to change the values for the Vertical Line 2 information series back to 12 and so that information technology will accomplish to the top of the chart.  When doing so, your nautical chart will now look like this:

3) Add a Vertical Line using an XY Besprinkle Lines

Okay, hither is the third manner to add vertical lines to your Excel Line Chart.  At that place is a 4th fashion that I discovered, simply information technology is a real pain in the y'all know what.  If yous desire to know more about this quaternary manner, you can encounter this on this mail service:

The 3rd way is a little trickier to complete, simply I am sure that if you follow my steps that you also will be able to do it.  This is a great technique to master considering information technology will allow y'all to add any line to near 2D chart types.

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

B) At present create ii sets of data anywhere in your Excel Spreadsheet that looks like this:

C) At present I recommend that yous highlight and COPY the Tuesday information from the upper right paw corner down one cell to the 12.  Your range would look like this:

so Select your Chart and select "Paste Special".  You will so run into this Dialog Box:

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

D) Now y'all may say that this is NOT a vertical line.  And you are correct .  However, here is how we start the transformation to an XY Besprinkle Chart.  Start, Right Click on the Vertical Line Series. In this instance, the Cerise Data Serial. And so from the Pop up carte du jour, choose "Change Series Chart Type…""

so change the chart type to an XY (Scatter) – Scatter with Direct Lines:

Your resulting chart will at present look similar this.  Doesn't look whatever better, but we are now very very close

E) Now select your nautical chart and so from the Design Ribbon, choose the "Select Data" button from the Data Grouping.

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

Now when you click edit, y'all will see the Edit Serial Dialog Box.  Give your series a title, so that you tin can easily recognize information technology in the future.  Then change the Series Ten Values to the 10's for Tuesday and change the Series Y Values to the Y's for Tuesday.

And so press OK merely don't close down the "Select Data Source" dialog box every bit you lot need it in the next step.  Now looking at the chart, you volition run across a Vertical Cherry-red Line in your Excel Line Chart.

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

From the Edit Serial dialog box, add together the Th vertical line data by adding the 10'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 "Abolish" at this footstep, you volition have to start over editing BOTH XY data series as Excel volition think you want to showtime over with the edit of all series for the chart since yous stated to edit them.

Your nautical chart will now await like this:

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

Well that is because we demand to do 1 last step!  That is to but delete the Secondary Horizontal Axis and Secondary Vertical Centrality.

K) Click on the Horizontal Secondary Horizontal Centrality and press the delete fundamental on your keyboard and then select the Secondary Vertical Centrality and press your delete key.  And boom goes the dynamite and you become this chart:

H) Okay here is the final pace for the actual vertical lines. Right click on the Primary Vertical Axis and then cull "Format Centrality…" from the Excel pop upward menu.

Then from the Format Centrality Dialog Box, you should modify 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 get from the bottom of the vertical axis to the very top. Here is your final chart:

These are actually lines, not columns, simply I think either tin exist fine.  Looks swell to me. What do you think? Let me know in the comments.

VIDEO Demonstration

Boosted Tutorial mentioned in the video:

how-to-select-data-series-in-an-excel-nautical chart-when-they-are-united nations-selectable

I want to thank each and every one of y'all once again for subscribing to my blog and making it and so successful. Cheers!

Steve=True

How To Add Straight Line On Excel Graph,

Source: https://www.exceldashboardtemplates.com/3-ways-to-create-vertical-lines-in-an-excel-line-chart/

Posted by: johnsfiromind.blogspot.com

Share this post