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

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset

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

Reference Data


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.

Data Range to Insert into Column

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

Selecting Exact Chart Type

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

Initial Look of Column Chart


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.

Format Data Series Option of Column Chart

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.

Changing the Values in Format Data Series

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

Modify Color of Columns

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

Column Chart After Modification


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.

Defining X Axis position of Data

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.

Autofill Feature to Fill the Cells

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

Selecting X Axis Data

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

Dialogue Box to Add Data

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

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

Output of Inserting Data into Chart


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.

Changing Chart Options

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.

Final Output


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.

Dataset of Scatter Plot

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

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

Select Option to Add Label

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

Scatter Plot with Label


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.

Dataset of Colored Scatter Plot

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.

Steps to Insert Scatter Plot

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

Initial Look of Scatter Plot

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.

Adding Range

A Dialogue box will appear like the following image.

Dialogue Box to Choose Range

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

Give Series Name and Select Range

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

Final Output with Different Colors for Different Range


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.


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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Junaed-Ar-Rahman
Junaed-Ar-Rahman

Md Junaed-Ar-Rahman, BSc, Biomedical Engineering, Bnagladesh University of Engineering and Technology, Bangladesh, has been working with the ExcelDemy project for 11 months. He has written over 15+ articles for ExcelDemy. Currently, he is working as Technical Content Developer in ExcelDemy project; writes unique articles as well as solves user problems. He participated in 2 specialized training programmes on VBA and Chart and Dashboard designing in Excel. He loves to solve any problem in unique way and explore new functions and fomulas of Excel. In future, he wants to explore more applications like Excel and gaining proficiency in those applications.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo