Color excel plot by field

Posted on


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

enter image description here

Leave a Reply

Your email address will not be published. Required fields are marked *