Finding Outliers

In this article we will learn how to find outliers in Excel.

What are Outliers?

Outliers are the values in a data which are outside the scope of the general data values, means which are very much higher than very much lower than the general data values.

Why do we need to remove the Outliers?

Outliers spoil our data and representation, when we create a graph of complete data if some values are extremely high and extremely low it other values / range of the graph does not look good.

Let us create simple data. In this example we can clearly see that some values are really outside e.g 200.

Let discuss the procedure to remove outliers. Lets start by finding the first and 3rd quartile:

1st quartile: =QUARTILE(A2:A14,1)

quartile minimum value

3rd quartile: =QUARTILE(A2:A14,3)

3rd quartil

Lets calculate interquartile range(IQR): Q3-Q1

interquartile range

Lets us calculate the lower bound and Upper bound values:

Lower bound = lower of Q1 or Q3 - 1.5 * IQR

Lower bound

Upper bound = max of Q1 or Q3 + 1.5 * IQR

Upper  bound

Let us find the outlier by using the U bound and L bound: =OR(A2<$F$2,A2>$G$2)