How to Create Excel Scatter Plot with Multiple Series from One Table

chartsmicrosoft excel

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.

Best Answer

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

Related Question