0

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.

Example data

Thanks!

1

2 Answers 2

0

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.

3
  • Cool. I tried that but I think something is off as the result looks weird. But your "pivot" keyword helped me experiment with the "Insert > Pivot Table" feature thing... which I can setup to do what I think your formula is trying to do. Feb 25, 2023 at 14:41
  • Adjust the Sheet1!A1:C16 in the formula so that it points to the real location of your data. Mar 11, 2023 at 15:36
  • I didn't mark it as correct or vote because it was only partially correct... Mar 12, 2023 at 16:04
-1

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

2
  • As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    Mar 12, 2023 at 20:06
  • From How to Answer: Brevity is acceptable, but fuller explanations are better. I mantion this because just inserting a Pivot Table is not enough. Please briefly explain how a Pivot Table help you to do achieve your goal. Mar 14, 2023 at 12:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.