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

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'.