Often you may want to color the points in a scatterplot in Excel based on a value or category, similar to the plot below:

Fortunately this is easy to do in Excel.

The following step-by-step example shows exactly how to do so.

Step 1: Enter the Data

First, let’s enter the following (X, Y) values for three different categories:

Step 2: Format the Data

Before we can create a scatterplot to visualize the (X, Y) coordinates for each group, we must first format the data in a specific manner. 

First, we’ll enter the unique values for each category along the top row and then type the following formula into cell D2:

=IF($C2=D$1, $B2, NA())

We can then drag this formula to the right to cell F2.

We can then drag it down until we hit cell F9:

Step 3: Insert the Scatterplot

Next, highlight the range A2:A9. Then, hold Ctrl and highlight every cell in the range D2:F9.

The following scatterplot will appear:

Each of the (X, Y) coordinates from the dataset are shown on the scatterplot and each of the points are colored based on the category they belong to.

Step 4: Modify Appearance of Scatterplot (Optional)

Lastly, feel free to modify the colors, point sizes, and labels to make the plot more aesthetically pleasing:

The following tutorials explain how to perform other common functions with scatterplots in Excel: