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.

**Table of Contents**Expand

## 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.

**Read More: **How to Find Outliers in Regression Analysis in Excel

### 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.

**Read More: **How to Remove Outliers in Excel Scatter Plot

**Download Practice Workbook**

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

## 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.

## Related Articles

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