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

**x̅**= 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**.

**Read More: **How to Find Outliers with Standard Deviation in Excel

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

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

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

**Download the Practice Workbook**

## Related Articles

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