How to Create Clustered Scatter Plot in Excel (with Easy Steps)

A clustered scatter plot is a type of chart in Excel that displays data points as individual dots on a graph. Unlike a regular scatter plot where all data points are plotted together, a clustered scatter plot groups data points into clusters based on their similarities.

Let’s create one to demonstrate.

Step 1 – Preparing Dataset and Reference Data

To create the clustered scatter plot, we’ll use the following sample dataset containing the data of some employees in an office.

Dataset

We also need reference values to compare with the values in the dataset:

Step 2 – Inserting 2D Column

Select the data range in the reference data.

Insert a 2D Column chart of this data by following the steps in the image below.

The chart below is generated in the worksheet.

Step 3 – Modifying the Column Chart

The chart needs to be modified further to clearly visualize it.

Right-click on any column of the chart and select Format Data Series from the context menu.

A sidebar will appear where we will modify some values.

Set the “Series Overlap” to 0 and “Gap Width” to 75.

Change the color of the columns to improve readability as in the image below.

The column chart now looks like the image below.

Step 4 – Inserting Dataset into Column Chart

We need to insert our dataset into the chart with an accurate position in the chart.

To define the “X-Axis” positions, enter the following formula into cell F5:

=IF(D5="Management",C5+0.25,C5-0.25)

Press Enter to return a value in the cell.
Use the AutoFill tool to fill the values in the remaining cells.

Select the data for the “Management” department on the X-axis as per the image below.

Right click on the chart.
Click on “Select Data”.

A Select Data Source dialog box opens

Enter the name of the series.
Select the range of values associated with it.

The data for the employees of the Management department has been plotted.

To insert the data for the Finance department, repeat the whole process.

Our chart now looks like the image below.

Step 5 – Clustering Data Points into Chart

From the Chart Design tab, click Select Data.
In the Change Chart Type dialog box that opens, choose Combo from the Recommended Charts list.
Select Scatter as the last two options as in the picture below.

Follow the steps in the video below to modify the “Management Salary” as well as the “Finance Salary” ranges, because the X-axis data is missing in both cases.

We have our desired scattered plot.

How to Create a Scatter Plot with Labels in Excel

Having labels of the data points in a scatter plot makes interpretation of the data much easier.

To demonstrate how to add labels in a scatter plot, we’ll use the dataset below.

After creating a scatter chart of the above dataset, it looks like the image below.

Let’s add a data label to the above plot.

Steps:

Click on the chart.
Click the Plus (+) sign.
Tick the Data Labels option.

The output looks like the following image.

How to Create a Scatter Plot with Different Colors

In the following dataset, we have some subjects and the number of students assigned to each.

Our desired scatter plot will show the number of students in different subjects grouped into different colors. Physics, Chemistry and Biology students will be shown in one color; Accounting, Economics and Finance in another color; and the rest in a different color entirely.

Steps: