QUESTION :
I have data which looks like this:
Type, X, Y
Foo, 1.2, 3.4
Foo, 3.2, 8.5
Bar, 2.3, 8.4
Baz, 3.4, 5.3
I would like to make a scatter plot of the x and y columns, and color the dots by the type column. There are over 2000 rows and 20-30 types, so creating series manually will be difficult.
It looks like Plotting data in excel grouped by type almost does what I want, except I cannot see how to get a scatter plot instead of a bar chart.
I can easily make a scatter plot of the data, but everything then shows up as one color.
Bonus points if the solution allows me to easily filter which data is shown.
I am running Excel for Mac 16.33.
ANSWER :
You can use simple formulas to split the column of Y values into three columns for Foo, Bar, and Baz. In the data range below, the formula in D2, and filled into the whole range D2:F5, is
=IF($A2=D$1,$C2,NA())
Then select the highlighted range and make a scatter chart by columns (I got something strange at first and had to click Switch Rows and Columns).
