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.

TRIMMEAN function to remove outliers in excel

 

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.

Read More: How to Remove Compatibility Mode in Excel


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.

Formula to Remove Outliers from Dataset and Line Chart in Excel

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

Applying formula to Remove Outliers from Dataset in Excel

There is now a flattened graph without any outlying values.

Line chart with removed outliers in Excel

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.


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.

  • Manually delete that data to make the dataset’s outlying values free.

Read More: How to Remove Value in Excel


Download the Workbook

You can download the free practice Excel workbook from here.


Related Articles


<< Go Back To Data Cleaning in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Sanjida Ahmed
Sanjida Ahmed

Sanjida Ahmed, who graduated from Daffodil International University with a degree in Software Engineering, has worked with SOFTEKO since 2021. She has written over 100 articles on Excel & VBA and, since 2022, has worked as the Project Manager of the Excel Extension Development Project in the Software Development Department. Since starting this software development, she has established an outstanding workflow encompassing a full SDLC. She always tries to create a bridge between her skills and interests in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo