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. In the following article, we will see how to create a clustered scatter plot in Excel. Hopefully, you will enjoy the whole article.

How to Create Clustered Scatter Plot in Excel: Easy Steps

In the following section, I will demonstrate the steps to creating clustered scatter plots. If you follow them carefully, you will easily be able to create your desired plot in Microsoft Excel.

Step 1: Preparing Dataset and Reference Data

To create the clustered scatter plot, first, we need to have the relevant dataset in Excel. We have created a sample dataset containing the data of some employees in an office. The dataset looks like the image below.

Besides, we need reference standard values to compare the values of the dataset. The reference values in this case look like the following image.

Step 2: Inserting 2D Column

Firstly, we will select the data range in the reference data. In this case, we will select the 2nd and 3rd columns of “Reference Data” like the image below.

After that, our target is to insert a 2D Column chart in Excel. To do that, follow the steps demonstrated in the image below.

Following the steps above, you will find the chart below in your worksheet.

Step 3: Modifying Column Chart

In the above section, we found a chart that needs to be modified further to clearly visualize it. We will do it in the current section.

First, we change the default “Format Data Series” option of the chart. To do that, we right-click on any column of the chart and select Format Data Series from the context menu.

When you click on the marked option of the above image, a sidebar will appear and we will modify some values there. We will set the “Series Overlap” to 0 and “Gap Width” to 75.

Also, we change the color of the columns so that it becomes easy for the users to interpret the data.

Doing these, the column chart appears like the image below.

Step 4: Inserting Dataset into Column Chart

This is the most complex work of creating the chart in Excel. We have to insert our dataset into the chart with an accurate position in the chart. To define the “X-Axis” positions, we put the following formula into cell F5.

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

If you write the formula into the cell, it looks like the image below.

Now you have to hit Enter on your keyboard to get value in the cell. To get the values in the remaining cells, we will use the AutoFill feature of Excel.

Our next step is to select the data of the “Management” department in the X-axis. The steps will look like the images below.

Clicking on the “Select Data” button, you will get a dialogue box like the image below.

Then you have to enter the name of the series as well as select the range of values associated with it.

We have done this for the employees of the Management department. In order to insert the data of the Finance department, repeat the whole process and you will succeed in inserting the whole dataset into the chart.

Doing all these, you will find the chart like the image below.

Step 5: Clustering Data Points into Chart

In the final stage, we will cluster the data points in the chart. Follow the steps that are shown in the following image.

After that, we will modify the “Management Salary” as well as the “Finance Salary” range. The X-axis data is missing in both cases. The following video will help you do this.

Do the same thing for both departments and finally you will get the desired scattered plot.

How to Create Scatter Plot with Labels in Excel

It is convenient sometimes to have labels of the data points in a scatter plot. Interpretation of data is much easier in that case. In the section below, we will add labels in a scatter plot. The dataset on which we will perform the work looks like the image below.

When you create a scatter chart of the above dataset, it looks like the image below.

Our target is to add a data label in the above plot. Do the things shown in the following image to do that.

After following the steps in the image, you will get the output like the following image.

How to Create Scatter Plot with Different Colors in Excel

There is another way to make the scatter plot more attractive to the users. That is using different colors for different data ranges in the plot. For example, in the following dataset, we have the data of a college which shows different subjects available in that college and the number of students who have chosen it.

Our desired scatter plot will show the number of students in different subjects in different color but in a group. Like physics,chemistry and biology students will be shown in one color. Accounting,Economics and Finance in another color. And the remaining in a different color.

First, we will choose the range and insert “Scatter Plot” in worksheet.

Following the steps shows in the image,you will find a scatter plot like the image below.

Now add a label to the plot which is described in the prior section. Next,we will add new data range to the existing plot.

A Dialogue box will appear like the following image.

When you click “Ok” a new box appears prompting you to select range.

If you do all these perfectly, you will get a chart with different colors for different data like the following image.

Frequently Asked Questions(FAQs)

1. What is a clustered scatter plot in Excel?

A clustered scatter plot in Excel is a type of chart that displays data points as individual dots on a graph. The data points are grouped into clusters based on their similarities.

2. What are the advantages of using a clustered scatter plot in Excel?

A clustered scatter plot in Excel can help you visualize complex data sets and identify patterns or trends within each cluster.

3. Can I customize the appearance of my clustered scatter plot in Excel?

Yes, you can customize the appearance of your clustered scatter plot in Excel by adjusting the chart’s colors, fonts, labels, and other formatting options.

Download Practice Workbook

You may download the following workbook to practice yourself.

Clustered Scatter Plot.xlsx

Conclusion

That is the end of the article regarding clustered scatter plots in Excel. The main idea behind it was to create scatters on top of columns. Keep in mind, that the amount of clusters depends on the dataset and reference data values. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries.

<< Go Back To Scatter Chart in Excel | Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!