Add a vertical line to Google Sheet Line Chart

Question

I have a sheet with a line chart, now I'm trying to do something maybe very simple:
I would like to add to this chart a vertical line using a value in a cell.

So I have this line chart
And a cell with the date 2016/01/01, I would like to have a vertical line through all the chart on the cell date

I can't figure out how to do it...

This is a copy of that sheet: https://docs.google.com/spreadsheets/d/1oeiwmeDT8pUVqBQvoE_cqk7mZxxvD5moZr41Vp4IN2I/edit?usp=sharing

I would like to show a vertical line using the "Purchase date"

Answer 1

A hacky solution that worked for me: add a line out of view, with enormous error bars.

For example:

When I show a wider interval on the vertical axis, you can see how I did it:

In this case, my real values are all well between 75 and 90. So I added another column with value 0 for rows without a line, and 70 for rows that needed a line.

Use "edit chart", then adapt the "data range" and click "Add series" to add this new column. Then in "Customize", you'll want to edit "series" to pick the different colors and such, and to add error bars (in this case of size 100 percent). Under "vertical axis", edit "min" and "max" to make sure the values of the new column stay well out of view, and the error bars are visible.

Answer 2

One way is to add a label to your x-axis.

For example, this is a chart that plots weight against date, with a label "Cheat Day" on 2021-07-21

For the data:

Date	Label	Weight (kg)	Weight Goal (kg)
2021-07-19		83.85	75
2021-07-20		84.55	75
2021-07-21	Cheat Day	83.8	75
2021-07-22		84.95	75
2021-07-23		83.75	75

Go to Edit the chart > Setup > Under X-axis > Click on ••• next to your "Date" column > Add labels > Select the column "Label" as your label.

Your Chart Editor > Setup should look like this:

Answer 3

I had the same problem and created a solution to overcome limitations of Google Sheets charts.

The main idea is to create an additional line in the chart, with only two points, both with the desired date. The value of the first point is 0 and the last has the maximum value of the Y axis. This way, the line always covers the entire height of the chart.

Screenshot of the Chart

Note that it is necessary to add two new values in the X axis (highlighted in blue on the sheet). Don't worry with the fact they are repeated. Google Sheets handles it correctly.

These values can be placed at the beginning of the lists. This way, it is possible to add new values at the end of them.

This solution can be viewed in: "[GoogleSheets] Dinamic Vertical Line in a Chart"

To change position of red line, just select a different value in "Purchase date" (yellow cell).

---

I made a merge of my first solution with the one suggested by dimo414 and created a new solution with two variations.

In the previous version of the spreadsheet, there were only two points to draw the vertical line.

In the new version, a third point were inserted to show intersection between the line and the real curve. A new column was also created, containing only a label for the new point.

The result is:

Theses changes can be seen in green background in sheets 'Dashboard_v2' and 'Dashboard_v3' of the SpreadSheet.

To determine coordinates of the new point, two approaches were used:

1. Search Purchase Date directly in the dataset (see sheet 'Dashboard_v2')

If the goal is to highlight only points of intersection that belong to the original dataset, it is just necessary to VLOOKUP() the date in the dataset.

2. Interpolate the two points immediately smaller and larger than the purchase date (see sheet 'Dashboard_v3')

Given the points [x1,y1], [x2,y2] and a value of x (where x1 <= x <= x2), its possible to find an interpolation point [x,y] with the following formula:

y=(y2-y1)*(x-x1)/(x2-x1)+y1

Although this formula is easy to implement, find the correct points to interpolate is more challenging and requires a bit of creativity.

At first, I thought of using a JS script to make things easier, but decided to use only builtin functions.

By the way, different approaches to find [x1,y1] and [x2,y2] are welcome.

To make things easier to understand, each point coordinate is determined in a different cell (see L2:M5) and the point of intersection is in L6:M7.

Of course, its possible to join all of them in just one cell, but I thought it would be harder to understand.

To close, one more detail: According to above definition, interpolation formula is valid only if (x1 <= x <= x2). Thus, both cells C2 and M6 have protections to limit the value of 'x'.

Answer 4

As best I can tell there isn't a way to add a vertical marker line to a chart in Google Sheets. One option that may be "good enough" in many cases is to "Add notes to a data point" and then use "Format data point" to make the point more visible. Here's an example, from your spreadsheet:

Unfortunately one limitation with this approach is you can only label a data point in the data set the chart is displaying. In your case the date you wanted to mark with a line isn't in the data set, so this won't work directly. You might be able to introduce a separate data series consisting of just that date and then add a note to that data point, but I haven't fiddled with it enough to make it work.

Answer 5

you can have it like this, unfortunately not programmatically. the only way is to insert a line via Drawing and position it manually where needed.

spreadsheet demo