The ability to effectively modify and enhance charts in Excel is a valuable skill. This article demonstrates how to add vertical lines to your Excel charts—something that can improve both the clarity and the visual appeal of your data representation. The chart types covered here are bar, line, and scatter plots.

We will explore different scenarios where a vertical line can be useful, such as adding an average line, a target line, a reference point, or a baseline. While Excel doesn’t provide a straightforward option for drawing vertical lines, this can actually be seen as a strength—Excel charts are highly customizable, and vertical lines can be added manually with a few extra steps.

In the first example, we will add a vertical line to a bar chart in order to compare the Nordic countries with the EEA (European Economic Area).

Image 1: A horizontal bar chart to which a vertical line will be added.

1. Add the coordinates for the line in the worksheet as shown in the image below. The value for x is the actual value you want to compare against, while y takes the values 0 and 1.

Image 2: shows the dataset. Note the x- and y-coordinates for the comparison line EES.

2. Right-click in the chart and select Select Data.

3. In the dialog box that opens, click Add under Legend Entries.

4. For the Series name, choose the cell containing the name of the series or type in a custom name. For the Series values, select the two x-values. Click OK twice to close the dialog box.

5. Two new bars are now added to the chart. Right-click one of the bars and choose Change Series Chart Type.

6. In the window that opens, select the Combo option on the left. For the new series, choose Scatter with Smooth Lines and Markers.

Image 3: shows the Change Chart Type dialog box.

7. Repeat step 2. This time, click Edit instead of Add. Now select both the x- and y-values for the series. A vertical line will now be added to the chart.

8. Double-click on the secondary axis on the far right of the chart. In the formatting menu, go to Axis Options and change the maximum value to 1.0.

Image 4: shows the formatting menu. In the box for Maximum, change the value to 1.0.

9. At each end of the line, there are two markers. With the formatting menu open, click one of the markers to remove them. Do this by selecting Fill & Line > Marker > Marker Options > None.

10. Done! As a final step, select the secondary axis and press Delete on your keyboard.

Now, only simple formatting and an explanation remain. Personally, I like to use the chart title for this whenever possible. Another option is to add a text box close to the line. If you prefer this option, you can find text boxes under Insert > Text > Text Box. The final result can be seen below.

Image 5: the final result.

Note that in this case, it would have worked just as well to add EES as a bar directly in the chart. For the sake of the example, however, we’ll disregard that option.

Another alternative to adding a text box is, of course, to add a data label with the series name and, in this case, the x-value. To do this, click the green plus sign at the top right corner of the chart (Chart Elements). Click the arrow next to Data Labels > More Options. From here, select Series Name and X Value. Then remove one of the redundant data labels and move the box to a more strategic position. The result may look like the one below.

Image 5b: the final result. Here, a data label is used instead.

Below you can see all the steps in a short clip from Learnesy’s LinkedIn.

To follow along with the example and explanations below, the Excel file is attached here.

In this second example, two vertical lines will be added to a line chart. In this fictional case, the two curves in the line chart represent the distributions of estimated job satisfaction in a workplace for the years 2017 and 2023. The dataset contains a total of 200 observations—100 observations per year. By adding two vertical lines, we can show the respective means along the x-axis.

If we study the dataset in the figure below, the raw data is shown on the left (columns B:C). To the right, we see a frequency table. This table shows the distribution itself and is used to plot the line chart. For example, six individuals rated their job satisfaction as 1 in 2017, while no one rated their job satisfaction that low in 2023. Perhaps this fictional workplace has managed to deal with its dissatisfied employees?

Image 6: Shows the raw data and frequency table.

To create the frequency table, the COUNTIF function was used. The generic formulas in cells F4 and G4 are:

=COUNTIF($C$4:$C$103, E4) // year 2017
=COUNTIF($C$104:$C$203, E4) // year 2023

1. Select the frequency table to insert a line chart.

2. Just like in the previous example, coordinates are needed for the vertical lines. To calculate the mean, the AVERAGE function was used:

=AVERAGE(C4:C103)
=AVERAGE(C104:C203)

These become the x-coordinates for the vertical lines. The y-coordinates have been chosen somewhat arbitrarily here. Of course, there are more sophisticated and precise ways to determine these, but they will not be covered in this article.

Image 8: Shows the coordinates for the vertical lines.

3. The same principles as in the previous example are applied here; follow steps 2–10. Note that a secondary axis will not be needed. This results in the following chart:

Image 9: The previous steps result in the following chart. Note that the y-coordinates need slight adjustment.

4. The next step is to slightly adjust the y-coordinates to better align with the curves.

5. Note that the x-axis has shifted slightly. The values appear between the tick marks rather than directly under them. This can be fixed by double-clicking the x-axis. In the formatting menu, go to Axis Options > Axis Position and select On tick marks.

Image 10: From Format Axis, select On tick marks.

6. Done! The only remaining step is to add textboxes explaining the two lines. The final result is shown below.

Image 11: The final result.

The completed chart clearly shows how the distribution has shifted from 2017 compared to 2023. Note that a horizontal line can be added in a similar way.

How to add a vertical line in a scatter plot in Excel

In this example, a dataset with 62 observations is used. In this scatter plot, two lines will be added: one horizontal and one vertical.

Image 12: Shows the dataset and the necessary coordinates for this example.

The range 0≤y≤140 was chosen as the minimum and maximum coordinates for the scatter plot. Similarly, 10≤x≤35 was chosen as the minimum and maximum for x. The values in G4 and H4, and in G8 and H8, were calculated as follows:

=(10+35)/2 // x
=(40+140)/2 // y

1. Select the raw dataset and insert a scatter plot.

2. Similar to the previous example, add the coordinates for the horizontal and vertical lines to the chart. Do this by right-clicking the chart and selecting Select Data > Add. Add both series. This gives the following chart.

Image 13: Shows a scatter plot with the additional points that will form the lines in the chart.

3. Double-click one of the extra points – preferably the bottom one – then go to Chart Elements > Error Bars > More Options.

4. In the Error Bar Options, select Minus and No Cap. Then choose Percentage and set it to 100%.

5. Remove the unnecessary horizontal lines by clicking on one of them and pressing Delete. See the image below for reference.

Image 14: Remove the unnecessary horizontal lines surrounding the two points.

6. Now select one of the vertical points and choose Error Bars from Chart Elements. This should immediately add a horizontal line. If not, repeat step 5. Make sure to select only one of the horizontal error bars and set it to 100%.

7. Similarly, remove the extra vertical bars (as in step 5) surrounding the points. This can be tricky, so select one and choose No Cap under End Style.

8. Next, format the x- and y-axes to adjust the minimum and maximum values. Double-click the x-axis, go to Axis Options, and set the correct bounds: minimum 10 and maximum 35.

9. Repeat step 8 for the y-axis.

10. Remove the data markers for both lines.

11. As a final step, remove the gridlines by selecting one and pressing Delete.

12. Done! The only remaining task is formatting. The finished result can be seen below.

Image 13: Shows the finished result.

Personally, I like to format the points to be slightly transparent if many points overlap. This dataset is also used to illustrate the CORREL function.

Summary

This article provides a comprehensive guide on how to add vertical lines to bar, line, and scatter charts in Excel, enhancing both clarity and aesthetic value in data visualization. While Excel does not offer a direct, simple way to insert vertical lines, its flexibility allows this to be achieved.

Bar Charts: By adding coordinates in the worksheet and using techniques like Select Data andChange Series Chart Type, a vertical line can be created. This involves adjusting axis values, removing breakpoints, and secondary axes to achieve the desired result. This method can also include adding text annotations or data labels for additional clarity.

Line Charts: In this example, data representing workplace happiness across two different years was used. By using frequency data and mean calculations, coordinates for the lines are created. Steps include adjusting y-coordinates and axis positions, and adding explanatory text.

Scatter Charts: In this example, a vertical and horizontal line was added to create quadrants. This is done by selecting the data, adding coordinates for the lines, and using error bars to create the lines. The process involves adjusting axis limits, removing extra lines and gridlines, and finally formatting the chart for the final result.

In conclusion, although there is no direct and simple method to add vertical lines in Excel, it can be achieved through creative use of the program’s features.