Find Outliers in Excel

Outliers are data points that significantly differ from other observations in a dataset. In statistics, they can distort analytical results by skewing averages, misleading trends, or affecting overall interpretations. Detecting and handling outliers is crucial to ensure accuracy in data-driven decision-making.

Example:

Consider the dataset: 1, 89, 57, 100, 150, 139, 49, 87, 200, 250.
The average of this dataset is 112.2, but values like 1, 200, and 250 stand out as being unusually low or high. When these outliers are removed, the new average becomes 95.85, giving a more realistic representation of the data. This demonstrates how outliers can heavily influence outcomes and why identifying them is essential.

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

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.

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

2. 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 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:

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:

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

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

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.

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)

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.

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.