Using an additional series that contains two data points, we can add a vertical line to a line chart in Google Sheets.

But it depends upon the data formatting of the category/horizontal axis (x-axis).

It will work as expected if your category axis contains numeric/date/time values.

What about text values on the x-axis?

The x-axis in a line plot may usually contain dates/months/years as it visualizes changes in values over a specified time interval.

If it contains text labels, we may require to follow a workaround solution.

This post will help you add a vertical line to a line chart in both the scenarios in Google Sheets.

Adding a Vertical Line to a Line Chart in Google Sheets – Dates on X-axis

In the following example, the category axis contains dates.

The source data is in cell range B1:C (not in B1:D), where B2, B3, B4,… contains 1/1/22, 1/2/22, 1/3/22,…, respectively.

That means the x-axis contains dates.

There is only one series (y-axis) which is the range C1:C. You will get to know the purpose of the range D1:D later on.

Here are the required steps, which you may follow to add a vertical line to a line chart in Google Sheets.

Steps – Additional Series and Formulas

As I have already mentioned at the very beginning, we require an additional series. It is D1:D here.

Insert the field label “Focus Line” in cell D1 and keep the rest of the cells down blank.

We will use cell E1 to control the position of the vertical line.

For that, in cell E1, I’ve used the below data validation rule.

To get this window, go to Data > Data validation.

In cell F1, insert the following FILTER and MAX combination formula.

={filter(B1:D,B1:B<>"");{E1,"",0};{E1,"",max(C2:C)}}

It will generate the required range in F1:H to plot a line chart.

Select F1:H and go to Insert > Chart, and follow the settings as per the image below.

Follow the above steps to add a vertical line to a line chart in Google Sheets.

Formula Logic and Explanation

Logic:

For the time being, let’s forget about the formula in F1 and talk about our original data in B1:D.

Please scroll up and see the first image.

To draw a vertical line at a particular point on the x-axis, enter that point label (for example, May-22, i.e., 1/5/22) in the cells B14 and B15.

Then leave the corresponding series column (C14 and C15) blank.

Finally, enter 0 in D14 and max of the first series column, i.e., =max(C2:C), in D15.

In this way, we have added two new rows at the bottom of the source data.

Select B1:D15, plot the chart, and voila!

We use a formula to automate the same, i.e., adding two additional rows at the bottom, but in a new range.

Explanation:

There are three parts to the above combination formula as ={part_1;part_2;part_3}.

part_1 – filter(B1:D,B1:B<>"")

part_2 – {E1,"",0}

part_3 – {E1,"",max(C2:C)}

The part_1 returns the B1:D range after filtering out blanks based on column B.

part_2 returns the drop-down value (a particular point on the x-axis), followed by a blank cell and 0 (zero).

part_3 also returns the drop-down value and a blank cell.

But, here, instead of 0 in the last cell, the formula finds and returns the max value (column B).

Additional Tips – More than One Series

We have only one series, i.e., “Amount,” in our first example above.

What if we have two series, for example, “Amount 1” and “Amount 2”?

The changes are minimal. You require to tune the formula a little bit.

1. Add one more blank cell in part_1 and part_2.
2. Get the max of both the series.

={filter(B1:E,B1:B<>"");{F1,"","",0};{F1,"","",max(C2:D)}}

Adding a Vertical Line to a Line Chart in Google Sheets – Texts on X-axis

It’s a fact that we require numeric/date/time values on the X-axis to draw a vertical line as above.

We have text values now!

What we will do here is, use the original text values on the x-axis as its LABELS and add a sequence number column to use as the x-axis.

We have only one series in the following example, i.e., “Amount.”

As per the above example, column B contains the category axis (x-axis) texts.

We will use column B as labels and column A (helper), which contains sequence numbers as the category axis.

The drop-down in cell E1 is based on column B.

But we require to use a VLOOKUP in E2 to get the corresponding sequence number.

=vlookup(E1,{B2:B,A2:A},2,0)

Here is the formula in F1.

={filter(A1:D,B1:B<>"");{E2,"","",0};{E2,"","",max(C2:C)}}

The formula is similar to the one in our earlier examples. So I am not going into the detail.

I have used the range F1:I to plot the line chart.

Following this, we can add a vertical line to a line chart in which the source data contain texts on the x-axis.

That’s all. Thanks for the stay. Enjoy!

Sample Sheet 28522

Resources

1. How to Move the Vertical Axis to Right Side in Google Sheets Chart.
2. Enabling the Horizontal Axis (Vertical) Gridlines in Charts in Google Sheets.
3. How to Make a Vertical Line Graph in Google Sheets (Workaround).
4. Scatter Chart in Google Sheets and Its Difference with Line Chart.
5. Sparkline Line Chart Formula Options in Google Sheets.
6. How to Plot a Line Chart Using Lap Times in Milliseconds in Google Sheets.
7. Create a Shaded Target Range in a Line Chart in Google Sheets.