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.
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
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).