How to Add Vertical Line in Excel Graph (6 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes for data visualization, you may need to make a graph in Excel. In that case, you may need to add a vertical line in the Excel graph. In this article, I will explain how to add a vertical line in an Excel graph.


How to Add Vertical Line in Excel Graph: 6 Examples

Here, I will describe 6 methods to add a vertical line in an Excel graph. In addition, for your better understanding, I’m going to use a sample dataset. Which contains 3 columns. They are Month, Cost, and Sales. The dataset is given below.

How to Add Vertical Line in Excel Graph


1. Adding Vertical Line in Scatter Graph

You can add a vertical line in the Scatter graph. Suppose, you have the following dataset and you want to add a vertical line with the data Average Cost and Average Sales which are given below the dataset.

Furthermore, let’s assume that the graph should be Cost vs Sales. This means the Cost value will be in the X direction and the Sales value will be in the Y direction.

The steps are given below.

Steps:

  • Here, the X and Y values for the new point of the adding line will be respectively Average Cost value and Average Sales value. Hence, you need to write these as the following format.

How to Add Vertical Line in Excel Graph

  • Now, you have to select the data. Here, I have selected the range C5:E13.
  • Then, you have to go to the Insert tab.

  • Now, from the Charts group section you have to choose Scatter >> then choose Scatter with Straight Lines and Markers.

How to Add Vertical Line in Excel Graph

At this time, you will see the following graph with an added vertical line.

Also, I have changed the Chart Title to “Adding Vertical Line”.

How to Add Vertical Line in Excel Graph

Read More: How to Add a Vertical Dotted Line in Excel Graph


2. Use of Bar Chart Feature to Add Vertical Line in Excel Graph

You can use the Bar Chart feature to add a vertical line in Excel Graph. Suppose, you have the following dataset. Which has 3 columns. Those are Month, Cost, and Average Cost. In addition, you want to add a vertical line of Average Cost.

The steps are given below.

Steps:

  • Firstly, you have to select the data. Here, I have selected the range B5:D11.
  • Secondly, you have to go to the Insert tab.

How to Add Vertical Line in Excel Graph

  • Now, from the Charts group section you have to choose 2-D Line >> then choose Line with Markers.

Furthermore, there are 6 features under the 2-D Line. Along with that, you can choose as your requirements. Here, I have used Line with Markers.

Now, you will see the result.

How to Add Vertical Line in Excel Graph

  • At this time, you need to select the Chart.
  • Then, from the Chart Design feature >> you have to go to Change Chart Type under Type Command.

Here, without selecting the Chart, the Chart Design feature will not be in your ribbon. Furthermore, you can do it by Right-Clicking on the Chart and using the Context Menu Bar.

Subsequently, a dialog box named Change Chart Type will appear.

  • Firstly, go to All Charts from that dialog box.
  • Secondly, from the Combo option >> select Custom Combination.
  • Thirdly, choose Line with Markers for Series 1 and Clustered Column for Series 2. Also, you need to check the Secondary Axis for Series 2.
  • Finally, press OK to get the changes.

Basically, you have to choose the Clustered Column for the data with which you want to make a vertical line.

How to Add Vertical Line in Excel Graph

At this time, you will see the following line graph with a vertical line. This vertical line is located in April because you kept the value towards April. So, wherever you keep the data, the vertical line will be plotted there.

Here, I changed the Chart Title to “Adding Vertical Line”.

How to Add Vertical Line in Excel Graph


3. Applying XY Scatter Plot

You can apply the XY Scatter plot to add a vertical line in Excel graph. Suppose, you want to add a vertical line in an Excel graph whose X value and Y value are respectively 4 and $1729.

The steps for adding a vertical line are given below.

Steps:

  • Now, you have to write down the Y value as follows. Here, I have written it in the D5 cell.

How to Add Vertical Line in Excel Graph

Now, you have to make the Bar Chart using data range B5:D11. Here, you can follow method-1 to make the Bar Chart. Or briefly, can follow the steps:

  • Firstly, select the data range.
  • Secondly, from the Insert tab >> select 2-D Column under Charts group section.

Finally, you will see the following result.

  • Now, you have to Right-Click on the Chart.
  • Then, from the Context Menu Bar >> select Change Chart Type.

How to Add Vertical Line in Excel Graph

Subsequently, a dialog box named Change Chart Type will appear.

  • Firstly, go to All Charts from that dialog box.
  • Secondly, from the Combo option >> you need to select Custom Combination.

  • Now, from the Drop-Down Arrow adjacent to the Series 2 >> choose Scatter with Straight Lines and Markers.

How to Add Vertical Line in Excel Graph

  •  Also, you need to uncheck the Secondary Axis for Series 2.
  • Finally, press OK to get the changes.

Now, you will see the following changes. Here, you have changed the Bar into the Point of that vertical line.

How to Add Vertical Line in Excel Graph

  • At this time, you need to select the point.
  • Then, you have to Right-Click on that point.
  • Finally, from the Context Menu Bar >> choose Select Data.

After that, you will see the following dialog box named Select Data Source.

  • Now, you have to choose Series2.
  • Then, you need to select the Edit feature.

How to Add Vertical Line in Excel Graph

After selecting the Edit feature, another dialog box will appear named Edit Series.

  • Now, you can write down or select the Series name in that dialog box. Here, I have written down the Series name as “Add”.
  • Then, you have to include the Series X values. Here, I have used the range B14:B15.
  • Also, you have to include the Series Y values. Here, I have used the range C14:C15.
  • Finally, press OK to get the Vertical line.

At this time, the Select Data Source dialog box will appear again.

  • Now, press OK to get the changes.

How to Add Vertical Line in Excel Graph

Finally, you will see the added vertical line in the Bar graph.

Read More: How to Add Border to a Chart in Excel


4. Employing Shapes Feature to Add Vertical Line in Excel Graph

The easiest way to add a vertical line in any Excel graph is by employing the Shapes feature. The steps are given below.

Steps:

  • Firstly, you have to make the Graph in which you want to add a vertical line. For that, you can follow the steps of method-1.

How to Add Vertical Line in Excel Graph

  • Now, from the Insert tab >> you need to go to Shapes.
  • Then, you have to select the Line which is in the Lines section.

  • Now, you have to drag the Mouse Pointer. Here, you need to drag the Mouse Pointer where you want to keep the vertical line. Furthermore, you can simply change the Shapes location using the Mouse Pointer

How to Add Vertical Line in Excel Graph

Now, you can also format the Line.

  • Firstly, you need to select the Line.
  • Secondly, from the Shape Format >> Click on the Drop-Down Arrow under Shape Styles.
  • Finally, from the Format Shape Menu, you can change the Line format according to your preference. Here, I have changed the Color of the vertical line and made the Width of the shape 2 pt.

Finally, you will see the added vertical line in the Line graph.

How to Add Vertical Line in Excel Graph


5. Use of Error Bars Command

You can use the Error Bars command to add a vertical line in an Excel graph. Now, suppose you want to add a vertical line whose value will be $1500 situated in the D6 cell.

The steps are given below.

Steps:

  • Firstly, you need to make an Excel graph using the whole data range. Here, I have made a Line graph using the data range B5:D11. To make the graph you can follow method-2.

How to Add Vertical Line in Excel Graph

  • Now, remove the other cell values of the Sales column.

  • Now, select the Point.
  • Then, you need to click on the + icon.
  • After that, from the Chart Elements >> check the Error Bars.
  • Finally, from the Selection Arrow of the Error Bars >> choose Percentage.

How to Add Vertical Line in Excel Graph

  • Now, from the Chart Design >> select Add Chart Element.
  • Then, from Error Bars >> you need to select More Error Bars Options to open the window of Format Error Bars.

  • Now, from the Format Error Bars >> you need to select Fill & Line.
  • Then, you have to choose Solid line.
  • After that, you may change the Color of the line and may change the Width. Here, I have changed the Color to Orange and made the Width to 2 pt.

Here, still the vertical line will not be visible.

How to Add Vertical Line in Excel Graph

  • Now, from the Format Error Bars >> you must go to Error Bar Options.
  • Then, from the Direction >> you may select any of them according to your preference. You can see the changes in the Chart instantly. Here, I have selected Minus.
  • After that, from the End Style >> you should select No Cap.
  • Finally, from Error Amount >> you need to choose Percentage and make it 100%.

Lastly, you will see the added vertical line in the Line graph.

How to Add Vertical Line in Excel Graph


6. Adding Dynamic Vertical Line in Excel Graph

Now, the most interesting part, you can add a Dynamic vertical line in an Excel graph. For this, you have to use the IF function and the MATCH function. The steps are given below.

Steps:

  • At first, you have to plot an Excel graph in which you will add the dynamic vertical line. Here, you have to include a blank cell range like D5:D11 where you will keep the data for the vertical line.

  • Now, you need to go to the Developer tab.
  • Then, from the Insert command >> choose Scroll Bar from Form Controls.

How to Add Vertical Line in Excel Graph

  • At this time, drag your mouse pointer to a suitable place where you want to add the Scroll bar.

  • Now, you must Right-Click on the Scroll bar.
  • Then, from the Context Menu Bar >> choose Format Control option.

How to Add Vertical Line in Excel Graph

Subsequently, a dialog box named Format Control will appear.

  • Now, from that dialog box >> you must go to the Control option.
  • Then, select the Maximum value range up to which you need to scroll. Here, I have made this to 7.
  • After that, you have to link the cell. So, choose the Drop-Down Arrow of the Cell link.

  • Now, select the cell where you want to link up the Scroll bar. Here, I have selected cell E5.
  • Then, click on Drop-Down Arrow to go back to the whole Format Control dialog box.

How to Add Vertical Line in Excel Graph

  • Finally, press OK.

At this time, if you change the Scroll bar you will see the result to Scroll Count.

How to Add Vertical Line in Excel Graph

  • After that, select the bank cell which is included in the chart. This means you must select D5.
  • Now, you need to use the corresponding formula in the D5 cell.
=IF(MATCH($B5,$B$5:$B$11,0)=$E$5,600,"")
  • Then, you must press ENTER to make the changes.

Formula Breakdown:

MATCH(lookup_value,lookup_array,[match_type])

  • lookup_value = $B5: This is the look-up value for which the MATCH function will search in the array. Here, the dollar sign $ denotes that the Column range is fixed.
  • lookup_array = $B$5:$B$11: This is the look-up array where the MATCH function will search for the value B5. Here, the dollar sign $ denotes that the array is fixed to B5:B11.
  • [match_type] = 0 : The function will search for the exact match.
  • In the IF function, I used a logical_test where if the values are equal to the Match value then it will return 600 else it will return a blank space.

  • Subsequently, you have to drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D11.

How to Add Vertical Line in Excel Graph

Finally, you will see the result in the data table. According to your scrolling, $600 will be shown in the D Column.

Here, you can see that the graph will be auto-changing. Basically, the vertical line is generated in April because your scroll count was 4. If you change it, then the vertical line will also be changed.

How to Add Vertical Line in Excel Graph

For a better understanding, see the GIF below.

How to Add Vertical Line in Excel Graph

Read More: How to Add Arrow in Excel Graph


Things to Remember

  • In the case of method-4, if you change the position of the graph the shape will not change automatically. Wherever you shift the graph, you have to shift the vertical line manually.
  • Furthermore, for method-1, you must use Scatter from the Chart groups section.

Practice Section

Now, you can practice the explained method by yourself.


Download Practice Workbook

You can download the practice workbook from here:


Conclusion

I hope you found this article helpful. Here, I have explained 6 different ways how to add vertical line in Excel Graph. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


<< Go Back to Formatting Chart in Excel | Excel Charts | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Musiha Mahfuza Mukta
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo