Create a Google Sheets chart with multiple data ranges with separate key columns

Question

I have this data:

 X1      Y1      X2     Y2
  1      20       1     25
  2      20       3     42
  3      20       6     50
  5      25       8     50
 10      30      10     50

I want to create a graph that overlays two lines, one with X1 as X axis, Y1 as Y axis, and one with X2 and Y2.

Values with the same X are meant to line up, so at 3 on the X axis, there's supposed to be 20 for Y1 and 42 for Y2. Just adding two data ranges to the graph won't do that.

My current plan is to create two intermediate data ranges that are basically exploded, so it interpolates the data in each range so there's a value for every single X (see my question here). But that approach doesn't sound very smart, there must be a better way.

Answer 1

Please try with layout as shown. Select A1:C11 (or to suit), insert Line chart with Use row 1 as headers and Use column A as labels:

Answer 2

You can bring you data in A:C to the format

X       
1   20  Y1
2   20  Y1
3   20  Y1
5   25  Y1
10  30  Y1
1   25  Y2
3   42  Y2
6   50  Y2
8   50  Y2
10  50  Y2

and then use

=QUERY(A:C,"select A, avg(B) where A is not null group by A pivot C")

to generate

X   Y1  Y2
1   20  25
2   20  
3   20  42
5   25  
6       50
8       50
10  30  50

ready to plot.

Answer 3

There is no need to duplicate the X values.

• Just put the Y2 values in a different column from Y1.
• Create a chart from A1:C5.
• Edit the chart and select line chart if necessary.