How to Make a Categorical Scatter Plot in Excel

Get FREE Advanced Excel Exercises with Solutions!

Today we will discuss how to make a categorical scatter plot in Excel. A scatter plot represents different numeric values with a combination of dots. The position of each dot with respect to the vertical and horizontal axis represents different data points. A categorical scatter plot illustrates the distribution of multiple categories in a single plot using dots.

categorical scatter plot excel


How to Make a Categorical Scatter Plot in Excel

In this article, we will demonstrate with examples how we can make a categorical scatter plot for different data points in Excel. We can create a categorical scatter plot using line charts in Excel. We can also use functions such as AVERAGE and STDEV.S to calculate the mean value and standard deviation and subsequently create a categorical scatter plot in Excel.


Method 1: Apply Line Chart to Create a Categorical Scatter Plot in Excel

In this method, we will make a categorical scatter plot from a line chart. This method is simple and easy to understand. Here in the worksheet, we have 3 categories of movies. The categories are Movie_1, Movie_2, and Movie_3. 5 people watched these movies and gave ratings on a scale of 1 to 10. Reviewer No represents the reviewer. Every row of the three categorical fields shows the ratings for the movies by each reviewer. To make a categorical scatter plot from this dataset, follow the steps below.

Steps:

  • To begin, we need to rearrange our dataset. We need to move the entries of the second categorical field in such a way that the first entry of the second categorical field remains below and next to the last entry of the first categorical field. So, select cells D5:D9.

Selecting Cells to Apply Line Chart to Make a Categorical Scatter Plot in Excel

  • Secondly, move the entries of cells D5:D9 to D10:D14.

Moving Cells to a Different Position

  • Similarly, with the third categorical field, we will move the entries in such a way that the first entry of the third categorical field remains below and next to the last entry of the second categorical field. We are moving the entries of cells E5:E9 to E15:E19.

Moving Cells to a Different Position

  • Now, select the cells C4:E19 and go to the Insert tab.

Selecting the Insert Tab

  • After that, click on the Line Chart from the Charts group.

Selecting Line Chart Option from the Charts Group

  • Then, select an appropriate line chart. We are selecting Line with Markers from 2-D Line charts.

Selecting an Appropriate Line Chart to Make a Categorical Scatter Plot in Excel

  • Subsequently, you will see a 2-D line chart of the selected data points.

Making a Categorical Scatter Plot in Excel

  • Next, click on the chart and go to the Chart Design tab.

Selecting the Chart Design Option

  • Then, select the Switch Row/Column option.

Switching the Rows with Columns in Categorical Scatter Plot in Excel

  • After that, you will see a categorical scatter plot of your data points.

Making a Categorical Scatter Plot in Excel

  • Then, you may click on the Chart Title and give your chart a suitable name.

Giving a Chart Title to the Categorical Scatter Plot

  • Finally, you will have a categorical scatter plot of your data points showing the ratings for every movie category individually.

Apply Line Chart to Make a Categorical Scatter Plot in Excel


Method 2: Use Mean and Standard Deviation to Create a Categorical Scatter Plot in Excel

In this method, we will create a categorical scatter plot by showing the mean and standard deviation for each category. This method includes very simple calculation steps. The dataset is the same as Method 1. Simply follow the steps below.

Steps:

  • First, we need to calculate the arithmetic mean of the entries of each category. Select a cell where you want to put the value of the mean for the first category. We are selecting cell C10.

Selecting a Cell to Make a Categorical Scatter Plot in Excel

  • Secondly, enter the formula into the cell:
=AVERAGE(C5:C9)
This formula will calculate the arithmetic mean for the numeric entries of the cells C5:C9

Entering A Formula into the Cell

  • Thirdly, press Enter and you will see the value of the arithmetic mean for the first category.

Finding out the Mean for the First Category

  • Then, use the Fill Handle to Autofill data from C10:E10.

Using Fill Handle to Autofill the Data

  • Next, you will see the means for all categories in cells C10:E10.

Calculating Means of the Categories to Make a Categorical Scatter Plot in Excel

  • Now, just like Method 1, we need to move the entries of the second categorical field in such a way that the first entry of the second categorical field remains below and next to the last entry of the first categorical field. Here, we are moving the entries of cells D5:D10 to D11:D16.

Moving Value to Different Cells

  • Similarly, with the third categorical field, we will move the entries in such a way that the first entry of the third categorical field remains below and next to the last entry of the second categorical field. We are moving the entries of cells E5:E10 to E17:E22.

Moving Values to Different Cells

  • Now, select the cells C4:E22 and follow Method 1 to create a categorical scatter plot.

Apply Line Chart to Make a Categorical Scatter Plot in Excel

  • Next, we need to calculate the standard deviation for the data points. Select cell C23 to put the value of the standard deviation for the first category.

Selecting a Particular Cell

  • Then, enter the formula into the cell:
=STDEV.S(C5:C10)
This formula will calculate the standard deviation for the numeric entries of the cells C5:C10

Entering a Formula into the Cell to Calculate Standard Deviation for the First Category

  • After that, to calculate the standard deviation for the second category select cell D23 and enter the formula below:
=STDEV.S(D11:D16)

Entering a Formula into the Cell to Calculate Standard Deviation for the Second Category

  • Next, to calculate the standard deviation for the third category select cell E23 and enter the formula below:
=STDEV.S(E17:E22)

Entering a Formula into the Cell to Calculate Standard Deviation for the Third Category

  • Now, in the chart, find the data point that represents the mean of the first category.

Selecting the Mean Value to Make a Scatter Plot in Excel

  • Then, click on the mean data for the first category and select the plus (+) icon and go to different Chart Elements option, select Error Bars > More Options.

Selecting More Options from Error Bars

  • Subsequently, from Format Error Bars, select Custom > Specify Value.

Selecting Specify Value Option from Custom

  • Then, you will see Custom Error Bars. Put the value of cell E23 as the value for both Positive Error Value and Negative Error Value.

Putting the Value of the Standard Deviation

  • Then, press OK.

Pressing OK to Apply Formula to Make a Scatter Plot in Excel

  • After that, you will see the mean associated with the standard deviation for the first category.

Using Mean and Standard Deviation to Make a Scatter Plot for the First Category in Excel

  • Now, do the same steps with the means of the rest of the categories.
  • Finally, you will have a categorical scatter plot showing the mean and standard deviation for each category.

Using Mean and Standard Deviation to Make a Scatter Plot in Excel


Download Practice Book

You can download this practice book while going through this article.


Conclusion

In this article, we have talked in detail about how to make a categorical scatter plot in Excel. This article will allow users to use Excel more efficiently and effectively. If you have any questions regarding this essay, feel free to let us know in the comments. Also, if you want to see more Excel content like this, please visit our website, Exceldemy.com and unlock a great resource for Excel-related content.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abu Sina Ibne Albaruni
Md. Abu Sina Ibne Albaruni

Md. Abu Sina Ibne Albaruni holds a BSc in Mechanical Engineering from Bangladesh University of Engineering and Technology. He is a dedicated employee of the ExcelDemy project. He has written quite a large number of articles for ExcelDemy. Besides, he has expertise in VBA. He efficiently automates Excel issues using VBA macros and actively engages in the ExcelDemy forum, offering valuable solutions for user interface challenges. His areas of interest in work and study span MATLAB, Machine Learning,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo