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:

Reference Data

Step 2 – Inserting 2D Column

Select the data range in the reference data.

Data Range to Insert into Column

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

Selecting Exact Chart Type

The chart below is generated in the worksheet.

Initial Look of Column Chart

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.

Format Data Series Option of Column Chart

A sidebar will appear where we will modify some values.

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

Changing the Values in Format Data Series

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

Modify Color of Columns

The column chart now looks like the image below.

Column Chart After Modification

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)

Defining X Axis position of Data

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

Autofill Feature to Fill the Cells

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

Selecting X Axis Data

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

A Select Data Source dialog box opens

Dialogue Box to Add Data

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

Selection of Range and Naming 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.

Output of Inserting Data into Chart

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.

Changing Chart Options

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.

Final Output

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.

Dataset of Scatter Plot

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

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

Steps:

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

Select Option to Add Label

The output looks like the following image.

Scatter Plot with Label

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.

Dataset of Colored Scatter Plot

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: