Excel scatter plot with multiple series from 1 table

Say I have a table as follows:

Label | X | Y | A | 1 | 1 | B | 2 | 2 | B | 3 | 2 | A | 4 | 3 | C | 5 | 4 | A | 4 | 3 | C | 2 | 1 |

How can I make this into an Excel scatter plot with 3 series (A,B,C) without manually selecting the correct rows manually for each series (like this answer). This table would be this chart:

enter image description here

Sorting won't help, as I want to do this relatively dynamically with new data.

dtech

Posted 2014-05-06T13:30:33.547

Reputation: 669

Re: pivot tables, Excel doesn't permit scatter plots using pivot tables as input data, at least in MS Office 2010 – Michael Rusch – 2014-10-24T19:25:39.423

@MichaelRusch You can make a regular (i.e., XY) chart from a pivot table.

– Jon Peltier – 2016-08-11T19:17:54.650

What have you tried? Have you considered using a pivot table to organize your data, then make a chart from there? Regular Charts from Pivot Tables might help you.

– CharlieRB – 2014-05-06T14:11:27.257

1@CharlieRB PivotTable's give aggregates of the data right? I want all the data points to be visible in the chart, so how can PivotTables help me? – dtech – 2014-05-06T14:20:59.433

I've also added the plotted chart to show what I want to achieve, but automatically. – dtech – 2014-05-06T14:24:42.967

No, you'll need to add each series individually. Whether you want to try to automate that with a macro or use the built in tools. – Raystafarian – 2014-05-06T15:58:11.963

1Are there meant to be more points for A in the graph? Eg, (4,3)? – binaryfunt – 2014-05-06T18:55:40.130

@BrianFunt Yes I forgot that point – dtech – 2014-05-06T20:12:29.567

Answers

Easier way, just add column headers A, B, C in D1:F1. In D2 enter this formula: =IF($A2=D$1,$C2,NA()) and fill it down and right as needed.

separated a-b-c data for scatter plot

Select B1:B8, hold Ctrl while selecting D1:F8 so both areas are selected, and insert a scatter plot.

chart with data highlighted beneath it

Jon Peltier

Posted 2014-05-06T13:30:33.547

Reputation: 3 470

1How did you get it to use A, B, C as series names? This did not work for me. – nietras – 2016-08-06T17:01:56.060

1@harryuser Select B1:B8 and ctrl-select D1:D8. You need to include the top row of the data. Then insert the chart. (I originally said to select B2:B8 and D1:D8, sorry for the confusion). – Jon Peltier – 2016-08-10T12:20:40.700

1@JonPeltier It's easier: Just select columns D to F and then control+select the disjoint column B. – Ark-kun – 2017-03-12T09:57:56.460

@Ark-kun Sure. I'm never confident that Excel will accurately parse the data from complete columns, and if you have lots of other data in the worksheet, selecting a whole column might select irrelevant data. Just a stray label somewhere is enough to hose the chart. – Jon Peltier – 2017-03-13T14:08:09.393

I had to hardcode D$1 to "A" since Excel kept trying to increment it to D$2, but ither than that this worked like a charm. – A N – 2017-04-27T14:55:07.743

This solution doesn't seem to be dynamic like OP requested. If we want to show data with Label D, we have to add more columns and add new series to the chart. Not very dynamic. – Nicolas – 2019-09-05T22:02:37.370

1@Nicolas - Okay, convert the data range into a Table (Ctrl+T). Now add a row. The formulas autofill, and the chart automatically plots the new row. Very dynamic ;-) – Jon Peltier – 2019-09-07T18:55:57.657

@Ark-kun. You are correct: Excel does not care about the order you select the columns. It appears to always use the column on the left as the horizontal axis. And selecting the multiple columns before selecting one single floating column is a little easier. – JosiahYoder-deactive except.. – 2020-01-08T16:55:50.577

Excel won't dynamically add new series, so I'm going to assume while the data can change, the names and number of series won't.

What I would recommend is transforming the data in a dynamic way that is easier to place a spot for each series by itself.

In Column D put:

=A2&COUNTIF(A2:A$2)

This will give values such as B3 for the 3rd element of the B series. Now that you have sequential labels for all elements of all series you can do lookups.

In a new sheet put

A1="Number"
A2=1
A3=A2+1

B1="A"
B2=Match(B$1&$A2,Sheet1!$D$1:$D$100,FALSE)

C1="A - X"
C2=IF(ISERROR(B2),"",INDEX(Sheet1!$B$1:$B$100,B2))

D1="A - Y"
D2=IF(ISERROR(B2),"",INDEX(Sheet1!$C$1:$C$100,B2))

And just add 3 columns just like that for each of your series. So it'll find which row the series named "A" has its first entry, the one you labeled A1, and then in column C it'll look up the X value, and in column D it'll look up the Y value. Then create a series A on your graph with X coordinates from column C and Y coordinates from column D, and as your underlining data gets more rows or rows change which series they are in, the graph will automatically update.

Jarvin

Posted 2014-05-06T13:30:33.547

Reputation: 6 712