Musiha Mahfuza Mukta
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.
Download Practice Workbook
You can download the practice workbook from here:
6 Methods to Add Vertical Line in Excel Graph
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.
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.
- Now, you have to select the data. Here, I have selected the range C5:E13.
- Then, you have to go the Insert tab.
- Now, from the Charts group section you have to choose Scatter >> then choose Scatter with Straight Lines and Markers.
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”.
Read More: How to Add a Vertical Dotted Line in Excel Graph (3 Easy Methods)
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.
- 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 requirement. Here, I have used Line with Markers.
Now, you will see the result.
- 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.
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”.
Read More: How to Combine Bar and Line Graph in Excel (2 Suitable Ways)
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.
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.
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.
- 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.
- 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.
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.
Finally, you will see the added vertical line in the Bar graph.
Read More: How to Draw a Horizontal Line in Excel Graph (2 Easy Ways)
Similar Readings
- How to Overlay Line Graphs in Excel (3 Suitable Examples)
- Make Line Graph with 3 Variables in Excel (with Detailed Steps)
- How to Make a Line Graph in Excel with Two Sets of Data
- Line Graph in Excel Not Working (3 Examples with Solutions)
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.
- 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.
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.
Read More: How to Make a Line Graph in Excel with Multiple Lines (4 Easy Ways)
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.
- 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.
- 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.
- 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.
Read More: How to Add a Marker Line in Excel Graph (3 Suitable Examples)
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.
- 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.
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.
- Finally, press OK.
At this time, if you change the Scroll bar you will see the result to Scroll Count.
- 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.
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.
Furthermore, for your better understanding, I will add a GIF.
Read More: How to Make a Double Line Graph in Excel (3 Easy Ways)
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.
Conclusion
I hope you found this article helpful. Here, I have explained 6 different ways about How to Add Vertical Line in Excel Graph. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.
