How to Remove Outliers in Excel (3 Ways)

Method 1 – Utilizing Trimmean to Calculate Average without Outliers

Below, we have data from Cells B5 to B14. Most numbers are between 20 and 27, but two values—0 and 100—are far off those. These are the outliers in our dataset.

For our dataset, the formula to calculate the mean (average) without the outlier values with the Excel TRIMMEAN function is,

=TRIMMEAN(B5:B14,0.2)

Here,

B5:B14 = Range of data to trim and calculate the average result
0.2 (or 20%) = The number of data points to exclude

If any number in the dataset falls 20% off the rest of the dataset, then that number will be called an outlier.

Steps:

Enter the above formula according to your dataset and press Enter.
You will get the calculated mean without outliers for your dataset. (i.e., Cell E6 holds the calculated mean, which is 23.50.)
Run the AVERAGE function in Cell E5, which returns the average (28.80) of all the values in range B5:B14.
In Cell E7, run another AVERAGE function by manually inputting all the cells except those with outlier values.

Method 2 – Removing Outliers from the Dataset and Line Chart

Below, we have some data based on a line graph. From the line graph, we can see data points 4 and 8 have outliers.

Steps:

In a cell (i.e., Cell H6), Enter the following formula:

=IF(AND(ABS(C6-C5)>$E$6,ABS(C6-C7)>$E$6),NA(),C6)

A data point that differs 10 units from others is designated as an outlier.

Enter 10 in Cell E6 and make it an Absolute Cell Reference.
Press Enter. You will see that the first data still appears in cell H6.
Drag the Fill Handle to apply the formula to the rest of the cells.

Drag the row down with the Fill Handle, and the cells holding the outliers will be filled with #N/A.

There is now a flattened graph without any outlying values.

Formula Breakdown

=IF(

We initiate the formula with the IF condition because we are going to compare values based on what we will extract from the result.

=IF(AND(

We are going to compare the current data point with the cell above and the cell below. If both comparisons are true, we will show the data point as a result. Since we need to deal with “both” comparisons, we should utilize the AND function.

=IF(AND(ABS(

When we subtract data points, some data may throw negative values. We don’t want to deal with negative values as negative values will always contradict the condition of “removing data when the difference is 10”. To ensure that we always have positive results, we need to wrap the comparisons in an Absolute value function.

=IF(AND(ABS(C6-C5)>$E$6

Here we started our first comparison. We simply subtract the current value from the value above and see whether the result is greater than the difference level stored in Cell E6 and make the cell an Absolute Reference to keep the cell value locked.

=IF(AND(ABS(C6-C5)>$E$6,ABS(C6-C7)>$E$6)

The second part of the comparison is separated by a comma. It will compare the current data point with the next data point.

=IF(AND(ABS(C6-C5)>$E$6,ABS(C6-C7)>$E$6),NA()

We put a comma and determine t the NA function if both the AND functions are true.

=IF(AND(ABS(C6-C5)>$E$6,ABS(C6-C7)>$E$6),NA(),C6)

If the AND function is not true then we want to return the actual data point as the IF False value. So we type in a comma and pass the cell reference number C6 in there.

Read More: Remove Dotted Lines in Excel

Method 3 – Removing Outliers Manually

Steps:

Select the dataset.
Go to Sort & Filter in the Editing group and pick either Sort Smallest to Largest or Sort Largest to Smallest.

In our case, we selected Sort Smallest to Largest. This sorted the numbers in our dataset in ascending order, with the smallest at the top and the largest at the bottom.

How to Remove Outliers in Excel (3 Ways)

Method 1 – Utilizing Trimmean to Calculate Average without Outliers

Method 2 – Removing Outliers from the Dataset and Line Chart

Method 3 – Removing Outliers Manually

Related Articles

Leave a reply Cancel reply