# 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!

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. Feb 25 at 14:41
• Adjust the `Sheet1!A1:C16` in the formula so that it points to the real location of your data. Mar 11 at 15:36
• I didn't mark it as correct or vote because it was only partially correct... Mar 12 at 16:04

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