Outliers are values in any dataset that are significantly different from the rest of the data. These extreme values arise due to errors, anomalies, or natural variations in the data. Identifying outliers is crucial because they can skew statistical analysis (e.g. mean, median, and standard deviation), distort machine learning models, and lead to incorrect conclusions. 

Outliers can be identified by using different manual methods like IQR, standard deviation, z score, Grub’s test, box plots, and scatter plots. In this blog, we’ll discuss how to find the outliers of any data in Excel by using the common manual methods like IQR & Z-score. Let’s first understand the outlier and then move to the finding. 

What are Outliers?

Outliers are data points that differ significantly from other observations. They can occur due to errors in data entry, measurement variations, or natural fluctuations in the data. These values have a great impact on your data analysis. Outlier identification makes your data analysis accurate and leads to more informed decision-making. Let’s see how we can find outliers using different methods in Excel. 

Methods to Find Outliers in Excel

Excel has many underused formulas and functions that help to improve data analysis. With this, you can detect outliers mathematically. The most common manual methods like the Interquartile Range (IQR) and Z-score method we performed in Excel.

1. Using Interquartile Range Method (IQR)

IQR is one of the easiest methods to detect outliers from any data. It can be helpful in a situation where you have few values on the end of data but have no idea which one counts as an outlier or not. It focuses on the spread of the middle 50% of your data to make extreme values less sensitive.  

Randomly, we can find it, if any data point value is below the lower or above the upper bound then it is an outlier. But for the correct value, we can use Excel and find the outliers of data by applying the below steps: 

• Open Excel and load your dataset.
• Sort and organize your data by selecting your dataset and using the “Sort feature” under the “Data tab”.
• Check for missing values using the “Find & Select” option under the “Home tab” and clicking 'Go To Special' > 'Blanks.'

Our Dataset

Here’s how to use the IQR method in Excel:

Steps to Use IQR in Excel

Firstly, you need to find the Q₁ and Q₃. The formula for this in Excel is: 

1. Put the “QUARTILE.INC (range, 1)” formula to find Q1 (25th percentile). In our example, the 1 is the quart place because we wish to find the 1^st Quartile for the lower boundary.  After entering the function, hit the keyboard Enter button to calculate Q₁.

2. The 25^th percentile is 2. Now go to calculate the 75^th percentile, mean Q₃. Fill this QUARTILE.INC (range, 3) in the D column to find Q₃ (75th percentile) for the upper boundary. 

The Q₃ (75^th percentile) is 4 which can be used to calculate the lower boundary in the next steps. 

3. Compute IQR by putting the formula =Q3 – Q1 in the column E. In Excel, you’ll put the name of the respected column that has values of Q3 and Q1 like this D₂ – C₂.

4. In the above steps, we get the Q_1, Q_3, and IQR which are essential to calculate the lower and upper boundary to identify Outliers. To get these, put the following formulas in the Excel formula bar and hit the enter button. 

• Lower Bound: = Q₁ – 1.5 * IQR 
• In Excel for our case= C₂ – (1.5*E₂)
• Upper Bound: =Q₃ + 1.5 * IQR
• In Excel for our example = D₂ + (1.5*E₂)

5. At last, we’ll detect the outliers with the help of low and high values. If the dataset is higher than the high value and lower than the low value then it is an outlier. You can also use the function “=IF(OR(A2<$G$2, A2>$F$2), "Outlier", "Normal")”

• The IF function checks if the value in column A is less than the lower fence or greater than the upper fence.
• If true, it marks the value as “Outlier”; otherwise, it marks it as “Normal”.

Since the outlier is 89.  

2. Find Outlier by Z-Score

Z-scores are a statistical measure that tells you how far a data point is from the mean in terms of standard deviations. This method is mostly useful for datasets that follow a normal distribution. Here’s how to calculate Z-scores in Excel:

Note: We’ll use the same data that used for the last method

1. Calculate the mean and standard deviation of the provided dataset in Excel. Enter your data in column A and then apply these formulas: 

Mean (Average): = AVERAGE (A2:A8), Standard Deviation (SD): = STDEV.S(A2:A8)

2. Calculate the Z score for each value by putting the “=(A2 – $C$2) / $D$2”. To get the z value of each. 

3. Once you have done so, then find the outlier by applying the Excel function “=IF(ABS(E2) > 2, "Outlier", "Normal")”. This will automatically detect the outliers in your data. 

There is an “89” outlier. 

Pro Tip: In this method, you can set the threshold of acceptable Z score (e.g., -2 to 2) for the identification of outliers. If any points are outside of this threshold, they are counted as outliers. But for quick prediction, you can try the outlier calculator which finds the accurate outlier value instantly.

Wrap Up!

Outliers are extreme values in datasets that can skew analysis and lead to incorrect conclusions. Excel gives methods to detect them easily apart from manual calculation. The IQR method identifies outliers by focusing on the middle 50% of data while the Z-score measures how far a data point is from the mean in standard deviations. 

Both methods are easy to implement in Excel and identify the outliers. With this, you ensure accurate data analysis and get reliable results.