# How to automatically plot multiple series from values in a column

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!

## 2 Answers

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.

• 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

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

• 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
Commented 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. Commented Mar 14, 2023 at 12:53