Table of Contents
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: