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!

Improve this question
0

2 Answers 2

Reset to default
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.

Improve this answer
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. Commented 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. Commented Mar 11, 2023 at 15:36
  • I didn't mark it as correct or vote because it was only partially correct... Commented Mar 12, 2023 at 16:04
-1

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

Improve this answer
1
  • 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. Commented Mar 12, 2023 at 20:06

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.