How to Find Outliers with Standard Deviation in Excel (with Quick Steps)

Outlier is the exceptionally high or low value with respect to the other data of our dataset. We can easily find the outliers of a dataset using Microsoft Excel. In this article, we will show you step by step procedure to find outliers with standard deviation in Excel. If you are curious about the process, download our practice workbook and follow us.

What Is Outlier?

Outlier is the exceptionally high or low value with respect to the other data of that dataset. When any value of that dataset lies far from the neighboring points of that dataset, then that value is considered an outlier. We can find the outliers of a dataset or a data graph in several ways whose are:

Using Mean and Standard Deviation
Through Inter-Quartile Range
By Regression Analysis
Using Z-Score

What Is Standard Deviation?

The Standard Deviation of a dataset represents the dispersion of that dataset with respect to its mean value and calculates as the square root of the variance. The mathematical expression of standard deviation is:

Here:

σ represents the standard deviation
N is the size of the dataset
X stands for each value from the dataset
µ represents the mean value

How to Find Outliers with Standard Deviation in Excel: Step-by-Step Procedure

To demonstrate the procedure, we consider a dataset of 10 students and their examination marks. The name of the students are in column B, and their examination marks are in column C.

Step 1: Estimate Mean of Dataset

In this step, we are going to evaluate the mean or average of our dataset. To complete the calculation, we will the AVERAGE function.

At first, select cell E5 and entitle the cell as Mean.

Now, write down the following formula into cell F5.

=AVERAGE(C5:C14)

Press Enter.

You will get the mean value of our dataset.

Thus, we can say that we have completed the first step to find outliers with standard deviation in Excel.

Step 2: Determine Standard Deviation

Now, we will estimate the value of the standard deviation of our dataset. For that, the STDEV.S function will help us.

First, select cell E6.
After that, entitle the cell as Standard Deviation.

Now, write down the following formula into cell F6.

=STDEV.S(C5:C14)

Similarly, press Enter.

You will get the value of the standard deviation of our dataset.

So, we can say that we have finished the second step to find outliers with standard deviation in Excel.

Read More: How to Calculate Outliers in Excel

Step 3: Evaluate Upper and Lower Boundary

In the following step, we have to estimate upper and lower boundary levels for our dataset. We will get these values from the values of mean and standard deviation.

At the beginning of this step, entitle the cells E7 and E8 as Upper Boundary and Lower Boundary respectively.

Then, select cell F7 and write down the following formula into cell F7.

=F5+(2*F6)

Press the Enter.

After that, in cell F8, write down the following formula.

=F5-(2*F6)

Again, press Enter to get the value.

At last, we can say that we have accomplished the third step to find outliers with standard deviation in Excel.

Step 4: Find Outliers from Dataset

This is the final step of our procedure. The OR function will help us to complete the task. After completing this step, we will find the outliers of our dataset.

First of all, insert a column between columns C and D and entitle the column as Outlier.

Now, in cell D5, write down the following formula in the cell. Make sure you insert the Absolute Cell Reference sign for the cells G7 and G8.

=OR((C5>$G$7),C5<$G$8)

Press Enter.

Then, double-click on the Fill Handle icon to copy the formula up to cell D14.
You will get the decision from Excel, and the result shows us whether the value is an outlier or not.

Finally, we can say that we have finished the final step to find outliers with standard deviation in Excel.

🔍 Interpretation of the Result

From the result of column D, we get the decision whether the value is an outlier or not. If you notice carefully, all the entities of that column are FALSE, except cell D9. The decision for cell D9 is TRUE. Now, if you look at column C, the value of corresponding cell C9 is 98 which is higher than the rest of the data. Our other values are in the range of 75-89. So, we can acknowledge that the value of cell C9 is an outlier with respect to the other data.

Step 5: Insert Chart to Visualize Output

In this step, we are going to insert a chart. The chart insertion is not mandatory, but this chart will help us to acknowledge the outlier.

At first, select the range of cells C5:C14.
Now, in the Insert tab, click on the drop-down arrow of the Insert Scatter (X, Y) or Bubble Chart from the Charts group.
Then, choose the Scatter chart from the Scatter section.

The chart will appear in front of you.
You can modify the chart according to your desire from the Chart Design and Format tab. We kept the default style for our chart.

After that, click on the Chart Elements icon and check only Primary Vertical from the Axes option.

Besides it, we also check the Data Labels on the Left and the Trendline option.

If you look at our dataset carefully, you may notice that all of our data is above 74. So, we don’t need the scaling of 0-74 in the vertical axis.
To eliminate it, double-click on the scale of the vertical axis.
As a result, a side window called Format Axis will appear.
Next, in the Axis Options tab, reset the Minimum value from 0 to 70 in the Bound section. You will see the scale below 70 is disappeared.

Now, if you look at the chart, you will notice that all the values of our dataset are close to our trendline, except 98. This value is much away from the trendline.

So, you can accept that this value is the outlier of our dataset, and our previous formula result also mentioned it.

In the end, we can say that our chart and formula worked successfully, and we are able to find outliers with standard deviation in Excel.

Download Practice Workbook

Download this practice workbook for practice while you are reading this article.

Outliers with Standard Deviation.xlsx

Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to find outliers in Excel with standard deviation in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.

<< Go Back to Outliers in Excel | Excel for Statistics | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!