How to Create a Mean Standard Deviation Plot in Excel

When analyzing data, it's often useful to visualize the spread or dispersion of data points. In Excel, you can create a mean standard deviation plot to display the average score and the range of scores for each team or group. This article will walk you through step-by-step instructions on how to create such a plot.

Step 1: Enter the Data

First, let's enter the following data that shows the points scored by various basketball players on three different teams:

Step 2: Calculate the Mean and Standard Deviation for Each Group

To calculate the mean and standard deviation for each team, you can use the AVERAGE function in Excel to calculate the mean, and the STDEV.P function to calculate the standard deviation. In this example, we will calculate the mean score of the class is 65 and the standard deviation is 10.

Here are the formulas that we used to calculate the mean and standard deviation in each row:

Mean: =AVERAGE(H2:H4)

Standard Deviation: =STDEV.P(I2:I4)

We then copy and pasted this formula down to each cell in column H and column I to calculate the mean and standard deviation for each team.

Step 3: Plot the Mean and Standard Deviation

To plot the mean and standard deviation, follow these steps:

1. Click anywhere on the chart, then click the green plus (+) sign in the top right corner.

2. Click Error Bars, then click More Options.

3. In the new window that appears, choose the cell range I2:I4 for both the Positive Error Value and Negative Error Value.

The following bar chart will appear that shows the mean number of points scored by each team:

Adding Standard Deviation Lines to Each Bar

Once you click OK, the standard deviation lines will automatically be added to each individual bar. To customize these lines, follow these steps:

1. Click anywhere on the chart, then click the green plus (+) sign in the top right corner.

2. Click Error Bars, then click More Options.

3. In the new window that appears, choose the cell range I2:I4 for both the Positive Error Value and Negative Error Value.

The standard deviation lines will be added to each bar, providing a visual representation of the spread or dispersion of data points.

In this article, we have shown you how to create a mean standard deviation plot in Excel. By following these step-by-step instructions, you can easily visualize the average score and the range of scores for each team or group, making it easier to analyze and understand your data.

Mean standard deviation plot in excel