Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
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 May 6 at 14:11
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 May 6 at 14:20
    
I've also added the plotted chart to show what I want to achieve, but automatically. –  dtech May 6 at 14:24
    
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 May 6 at 15:58
    
Are there meant to be more points for A in the graph? Eg, (4,3)? –  Brian Funt May 6 at 18:55

2 Answers 2

up vote 4 down vote accepted

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 B2:B8, hold Ctrl while selecting D1:F8 so both areas are selected, and insert a scatter plot.

enter image description here

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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