Open In App

How to Find Outliers in Excel?

Improve
Improve
Like Article
Like
Save
Share
Report

Outliers as the name suggest are something that doesn’t fall in the required/given range. Outliers in statistics need to be removed because they affect the decision that is to be made after performing the required calculations. Outliers generally make the decision skewed i.e they move the decision in a positive or negative direction. Sometimes it is easy to find an outlier by looking at the data but it is difficult to find an outlier when the data is large. We’ll see this with the help of an example, given a dataset and you need to perform the average of the dataset 1, 89, 57, 100, 150, 139, 49, 87, 200, 250. So, the average of the given data set is 112.2. But, it is clearly visible that 1, 200, and 250 are ranges that are too small or too large to be a part of the dataset. These ranges are known as outliers in data. After removing the outliers, the average becomes 95.85. It is evidently seen from the above example that an outlier will make decisions based.

Finding Outliers using Sorting in Excel

This is one of the easiest ways to find outliers in MS excel when your data is not huge because by having a look at the data you’ll get to know about the values that are far away from the originally recorded values.

Unsorted-Data

 

From the above image, we can clearly tell that the data is not sorted and hence it would take some time for us to identify outliers.

Sorted-data

 

While looking at Img. 2, we can clearly say that the numbers 1, 200, and 250 are outliers. 

Finding Outliers using LARGE/SMALL Excel Function

Another way to find outlier is by using built-in MS Excel functions known as LARGE and SMALL. The LARGE function will return the largest value from the array of data and the SMALL function will return the smallest value. Here, we will be using a LARGE and SMALL function which is an in-built function in Microsoft excel. Consider the example used above:

Large-and-small-example

 

LARGE Function Syntax:

LARGE($B$1:$B$12, 1)

Here, we are passing an array and a number. The array has the dataset for which we have to find the outlier and the number, 1, represents the first largest number from the array. If we use 2, it will return the second largest value from the array. Now when we use this function in the above example, we will get the following output:

Large-function-output

 

SMALL Function Syntax:

SMALL($B$1:$B$12, 1)

The syntax and pass-on value are the same. Now when we use this function in the above example, we will get the following output:

Small-function-output

 

Note: If there are multiple outliers in the data then you have to use the function again and again.

Finding Outliers using Inter Quartile Range(IQR)

The data presented in the above example has a small sample size but when it comes to a real-life situation, the data can be huge, and that’s where the original problem arrives. As per IQR, An outlier is any point of data that lies over 1.5 times IQRs below the first quartile (Q1) and 1.5 times IQR above the third quartile (Q3)in a data set.

Formula is

High = Q3 + 1.5 * IQR

Low = Q1 – 1.5 * IQR

Finding Outliers using the following steps:

Step 1: Open the worksheet where the data to find outlier is stored. 

Step 2: Add the function QUARTILE(array, quart), where an array is the data set for which the quartile is being calculated and a quart is the quartile number. In our case, the quart is 1 because we wish to calculate the 1st quartile to calculate the lowest outlier.

Quartile-1

Quart Number Quartile Returns
0 Minimum Value
1 First quartile(25th percentile)
2 Median Value(50th percentile)
3 Third Quartile(75th percentile)
4 Maximum Value

Step 3: Similar to step 2 add the quartile formula under Q3 and write 3 as quart number because we wish to calculate the 3rd quartile i.e 75th percentile to calculate the highest quartile value.

Step 4: Inter Quartile Range or IQR is Q3-Q1, put the formula to get the IQR value.

IQR

Step 5: To find the High value, the formula is Q3+(1.5*IQR). Similarly, for Low value, the formula is Q1-(1.5*IQR)

High-and-Low-quartile

Step 6: To find whether the number in the data set is an outlier or not, we need to check whether the data entry is higher than the High value or lower than the Low value. To perform this we will use the OR function. The formula will be OR(B3>$G$3, B3<$H$3). Put the formula in the required cell and drag down the cell adjacent to the last data set, if the value returns TRUE, then the data is an outlier otherwise not.

Checking-whether-the-number-in-the-data-set-is-an-outlier-or-not

Since you’ve checked for the outlier data. Now you can remove the outliers and use the rest data for calculations and get unbiased results.


Last Updated : 28 Mar, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads