# How to Find Outliers Using Z Score in Excel (with Quick Steps)

## What Is Z Score?

In statistics, the z score represents the standard deviation from the population for a particular data point. This measure tells us how far a specific value is from the mean of a set in relation to its standard deviation (standard deviation is the RMS value of all the different points and mean). The mathematical formula for the z score is

Z= z score

X=observed value

= mean value

σ = standard deviation

## What Is an Outlier?

Outliers are values that are significantly different from other values in a dataset. An outlier is a value that is much higher or lower than the mean and median of the dataset. In an Excel graph, you can consider the data points which remain far away from the other data points as outliers. For instance, consider a scenario where you have total points scored by 5 different players in the NBA season. The scored points are 1600, 1400, 300, and 1500. If you plot a graph in Excel, you will see that point 300 lies far away from the others. So, it is the outlier.

An outlier is considered to have a z score value outside the range of 3 to -3.

## How to Find Outliers Using Z Score in Excel: Step-by-Step Procedure

### Step 1 – Calculate the Mean

• Our dataset contains values in the range C5:C20 that we’ll analyze.
• Insert the following formula in a cell. We chose H4.

`=AVERAGE(C5:C20)`

• Press Enter.

### Step 2 – Estimate Standard Deviation

• Insert the following formula in cell H5.

`=STDEV.P(C5:C20)`

• Press Enter.

Read More: How to Calculate Outliers in Excel

### Step 3 – Evaluate Z Scores

• Use the following formula in cell D5.

`=(C5-\$H\$4)/\$H\$5`

• Press Enter.

• Drag the Fill Handle icon down to fill out the rest of the cells in the column with the formula.

• The mean value is 113.875 and the standard deviation is 100.21. If we consider the specific value of \$79, the z score for this value is -0.347 which means \$79 is 0.347 standard deviations below the mean or average value.
• When the value is \$500, we get the z score is 3.85. That means \$500 is 3.85 standard deviations above the mean value.

### Step 4 – Find the Outliers

• Use the following formula in cell E5.

`=OR((D5<-3),(D5>3))`

• Press Enter.

• Drag the Fill Handle icon to fill out the rest of the cells in the column with the formula.

From the above dataset, we can see only one id’s z score is above the value of 3. That’s why we only get one outlier.

We are going to show outliers using a Scatter chart:

• Select the range of the cells C5:D20.
• Go to the Insert tab.
• Select Insert Scatter (X, Y) or Bubble Chart and select Scatter.

• You will get the following chart.

• Select Chart Design and then select your desired option from the Chart Styles group.

• We get the following Scatter chart.
• Notice that the value with ID 16 is away from the others. This is the outlier.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!

Saquib Ahmad Bhuiyan, holding a BSc degree in Naval Architecture & Marine Engineering from Bangladesh University of Engineering and Technology, skillfully integrates engineering expertise with a passion for Excel. He provides solutions to complex issues as an Excel & VBA Content Developer for ExcelDemy Forum. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing his commitment to excellence. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, Rhinoceros,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF