How to automatically plot multiple series from values in a column

Question

In Google Sheets, I know I can plot multiple series into a chart by manually adding a series and selecting/specifying the source data for each series. But is it possible to use a third column to identify each series automatically?

For example, I have columns for X and Y values, and I want to use a third 'Series ID' column to identify which series each point belong to. I have lots of series to plot so setting the sources manually will take too long.

Thanks!

Answer 1

Try an unpivot-repivot transformation. Assuming the data is in Sheet1!A1:C16, choose Insert > Sheet and put this formula in cell A1 of the new sheet:

=query( 
  let( 
    table, Sheet1!A1:C16, 
    numColsToRepeat, 1, 
    numColsToSqueeze, columns(table) - numColsToRepeat, 
    headers, { offset(table, 0, 0, 1, numColsToRepeat), "Category", "Data" }, 
    categories, offset(table, 0, 0, 1), 
    colsToRepeat, offset(table, 1, 0, rows(table) - 1, numColsToRepeat), 
    colsToSqueeze, offset(table, 1, numColsToRepeat, rows(table) - 1, numColsToSqueeze), 
    reduce( 
      { headers, "Row #" }, colsToSqueeze, 
      lambda( 
        result, cell, 
        if( 
          cell = "", 
          result, 
          { 
            result; 
            { 
              index(colsToRepeat, row(cell) - row(colsToSqueeze) + 1), 
              index(categories, 1, column(cell) - column(table) + 1), 
              cell, 
              row(cell) 
            } 
          } 
        ) 
      ) 
    ) 
  ), 
  "select Col2, max(Col3), Col4 
   group by Col2, Col4 
   pivot Col1 order by Col4", 1 
)

Then select the first three columns and choose Insert > Chart. Use the Switch rows/columns option if necessary.

Note that both X and Y will be plotted against the same vertical axis. If your data consists of numbers like 100 and percentages like 25%, the percentages will be close to invisible. You can address that by recasting the percentages as percentage points by multiplying them by 100 or before transforming the data.

Answer 2

I did it semi-automatically by inserting my own pivot table: Insert > Pivot Table