When analyzing data, we usually assume that the data values fall somewhere near the mean or median, or at least closeby.

However, there might be some values that are way off from the mean/ median.

These values, also known as outliers, can skew your analysis and end up giving misleading results.

In this tutorial we will see three ways in which you can **find outliers in your Excel data**:

Table of Contents

**Finding Outliers in Excel using the Inter-Quartile Range**

The *Inter-Quartile Range (IQR) *is a measure of where the beginning and end of the bulk of your data lie.

So any value that is away from this cluster of data is most probably an outlier.

Let us first understand how the *Inter-Quartile Range *is calculated.

In order to calculate the IQR, we need to know the first and third quartiles of the data, because the formula for calculating IQR is:

IQR = Q3 - Q1

Where,

*Q1*is the first quartile of the data*Q3*is the third quartile of the data

A *quartile *consists of a quarter of the values in the data, when the data is sorted from the smallest to largest values.

The *first quartile (Q1)* consists of the lowest 25% of the data.

Similarly, the third quartile (Q3) consists of the values that are just above the median (values that are between 50% to 75% of the data).

These quartile values can easily be calculated using the QUARTILE.INC function in Excel. The syntax for the function is as follows:

QUARTILE.INC(array,quart)

Here,

*array*is the range of cells containing your data*quart*is a number that indicates which quartile you want to calculate.

If you want to calculate the first quartile, you need to specify the *quart *parameter as 1. Similarly, if you want to calculate the third quartile, you need to specify the *quart *parameter as 3.

Once you have the *Q1*, *Q3* and *IQR* values, you can use these three to calculate the smallest and largest values of the acceptable data range (also known as the *Lower bound *and *Upper bound *respectively).

So any value that is smaller than the *lower bound* or larger than the *upper bound* are the outliers. Here’s how you can calculate the lower and upper bound values:

We can calculate the *lower bound limit* by multiplying the *IQR* value by 1.5 and then subtracting it from the *Q1* value:

Lower Bound = Q1-(1.5 * IQR)

Similarly, we can calculate the* upper bound limit* by multiplying the *IQR* value by 1.5 and then adding it to the Q3 value:

Upper Bound = Q3+(1.5 * IQR)

In a nutshell, the sequence of calculations to find outliers in your data are:

Select your data

- Calculate
*Q1*and*Q3*using the QUARTILE function for your data. - Calculate
*IQR*by subtracting Q1 from Q3. - Calculate
*Lower bound*by multiplying*IQR*by 1.5 and subtracting it from*Q1.* - Calculate
*Upper bound*by multiplying*IQR*by 1.5 and adding it to the*Q3.* - Find the points that are smaller than the
*lower bound*or larger than the*upper bound*. These points are the*outliers*.

Let us take an example to see how to apply the above method in Excel. Consider the following list of data values:

To calculate and find outliers in this list, follow the steps below:

- Create a small table next to the data list as shown below:

- In cell E2, type the formula to calculate the
*Q1*value: =QUARTILE.INC(A2:A14,1).

- In cell E3, type the formula to calculate the
*Q3*value: =QUARTILE.INC(A2:A14,3).

- In cell E4, type the formula to calculate the
*IQR*value: =E3-E2.

- In cell E5, type the formula to calculate the
*lower bound*value: =E2-(1.5*E4).

- In cell E6, type the formula to calculate the upper bound value: =E3+(1.5*E4).

- Now for each data value, you can find out if it is an outlier. Type the following formula in cell B2: =OR((A2<$E$5),(A2>$E$6)). This will return a TRUE value if the data value is an outlier and FALSE otherwise.

- Copy this formula to the rest of the cells in column B by double clicking on the fill handle of the cell.

You should now see a TRUE value next to all the outliers in your data.

**Note**: Alternatively you can also use *conditional formatting* to highlight the outliers in your data. You can use the formula of Step 7 as the condition for formatting the cells.

**Finding Outliers in Excel using the Mean and Standard Deviation**

Another simple way of detecting outliers is by using the *Standard deviation* of the data distribution.

The standard deviation is a quantity that expresses how much the points in a distribution differ from the mean value for the distribution.

Usually we assume a value to be an outlier if it is more than 2 or 3 times the standard deviation of the distribution.

To calculate the *Standard deviation* of data in Excel, we can use the STDEV.S function.

This function works on a sample of data (not population) and returns the standard deviation of the data’s distribution.

The syntax for the function is:

=STDEV.S(number1,[number2],…)

Here, *number1*, *number2*, etc. are references to individual cells in a range. You can also just specify a reference to the *range *of cells containing your data instead.

Besides the standard deviation you will also need to know the *mean *of the distribution. To calculate the *mean*, you can use the AVERAGE function. The syntax for the function is:

=AVERAGE(number1,[number2],…)

Again, *number1*, *number2*, etc. are references to the individual cells in a range. You can also just specify a reference to the *range *of cells containing your data instead.

The values that are 2 standard deviations away from the mean are the outliers.

This means any value that is less than *Mean-*(2**Standard Deviation*) or more than *Mean*+(2**Standard Deviation*) are outliers.

Consider the same dataset again:

Let us use the *Mean *and *Standard Deviation *of the data to find outliers:

- Create a small table next to the data list as shown below:

- In cell E2, type the formula to calculate the
*Mean*: =AVERAGE(A2:A14).

- In cell E3, type the formula to calculate the
*Standard Deviation*: =STDEV.S(A2:A14).

- In cell E4, type the formula to calculate the
*lower bound*: =E2-(2*E3).

- In cell E5, type the formula to calculate the
*upper bound*: =E2+(2*E3).

- Now for each data value, you can find out if it is an outlier. Type the following formula in cell B2: =OR((A2<$E$4),A2>$E$5)). This will return a TRUE value if the data value is an outlier and FALSE otherwise.

- Copy this formula to the rest of the cells in column B by double clicking on the fill handle of the cell.

You should now see a TRUE value next to all the outliers in your data.

**Finding Outliers in Excel using the Z-Score**

Another way of finding outliers is by using the *Z-score* value. The* Z-score* value gives an idea of how far a data point is from the Mean. It is also known as the *Standard Score*.

To calculate the *Z-score*, we need to know the *Mean *and *Standard deviation* of the data distribution. The formula for the Z-score is:

Z= (X-mean) /Standard Deviation

Here, *X* is an individual data value in the distribution.

The further away a data value’s *Z-scor*e is from *zero*, the more unusual it is.

A standard cut-off value for finding outliers are *Z-scores* of +/-3 or further from zero. So any value with a *Z-score* of less than -3 and more than +3 can be considered an outlier.

Again let’s consider the same dataset:

Here’s how you can use the *Z-score* method to find outliers in the data:

- Create a small table next to this list as shown below:

- In cell F2, type the formula to calculate the
*Mean*: =AVERAGE(A2:A14).

- In cell F3, type the formula to calculate the
*Standard Deviation*: =STDEV.S(A2:A14).

- Now for each data value, calculate the
*Z-score*. Type the following formula in cell B2 and copy it to the rest of the cells in column B: =(A2-$F$2)/$F$3.

- You will notice none of the values in our dataset have crossed the -3 or +3 mark. This is because these points are probably not that far from the mean. However, even a boundary z-score of +2 and -2 can be considered as quite away from the mean. So, let us consider values with a Z-score less than -2 or more than +2 as outliers.
- Type the following formula in cell C2: =OR((B2<-2),(B2>2)). This will return a TRUE value if the data value is an outlier and FALSE otherwise.

- Copy this formula to the rest of the cells in column C by double clicking on the fill handle of the cell.

You should now see a TRUE value next to all the outliers in your data.

There are many other ways to **find outliers in your data in Excel. **In this tutorial, we took a look at three such ways.

You can select the method that works best with your data. We hope this was helpful for you.

**Other articles you may also like:**