Finding outliers and removing them is a very common task to analyze data in statistics, data mining, machine learning etc. There are so many ways that you can find outliers in your dataset but the process to remove them is not so easy to find. In this article, we will show you how to **remove outliers in Excel**.

**Download Workbook**

You can download the free practice Excel workbook from here.

**What are Outliers in Excel?**

An **outlier** is a value that falls way off from the mean or median value of a dataset. In other words, outliers are values that are significantly higher or lower than the rest of the values in a dataset.

Think of a cricket match where all the batters scored around 50 but only one batsman scored a century (100) and another batsman got out at 0. That 100 and 0 scores are the outliers in this match.

Outliers are problematic as they can skew the result of the data analysis and occur misleading results. So it is better to find outliers and remove them to have a smooth dataset.

**3 Ways to Remove Outliers in Excel**

In this section, you will learn how to **delete outliers** from your dataset in 3 different ways. Removing outliers is a tricky process, so read the whole article carefully.

**1. Utilizing Excel Function to Calculate Average without Outliers**

Excel **TRIMMEAN function** can be used to calculate the average of a given dataset while excluding outliers. The data points to exclude is supplied as a percentage. The percentage value can be input as a decimal format or percent format.

Syntax to calculate mean without outliers is,

**=TRIMMEAN(array, percent)**

Here,

**array**= Range of data to trim and calculate the average result**percent**= The number of data points to exclude

Letâ€™s see how to implement this function to calculate results with removed outliers.

Consider the above picture. We have data from **Cell B5 to B14**. Here most of the numbers are between 20 to 27, but two values â€“** 0 and 100** â€“ are a far way off those values. So these are the **outliers** in our dataset.

For our dataset, the formula to calculate mean (average) without the outlier values with 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% way off the rest of the dataset, then that number will be called outliers.

If you write the formula according to your dataset and press** Enter**, you will get the **calculated mean without outliers **for your dataset. In our case, **Cell E6** holds the calculated mean which is **23.50**.

To check whether the answer is correct or not, we run the **AVERAGE function** in **Cell E5** which returns the average (**28.80**) of all the values in range **B5:B14**. And in **Cell E7**, we run another **AVERAGE** function by manually inputting all the cells except those that hold outliers values, and got a return of **23.50** as result.

So we can say that the **TRIMMEAN** function can successfully remove outliers from a given dataset while calculating the average in Excel.

**Read More:** **How to Clear Formula in Excel (7+ Methods)**

**2. Remove Outliers from Dataset and Line Chart in Excel**

Notice the following picture. We have some data based on which we created a line graph. From the line graph, we can easily see that in **data points 4 and 8 we have our outliers**.

Now we will see how we can smooth out the line chart by removing the outlying values from the dataset.

In another cell (in our case it is **Cell H6**), write the following formula,

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

Here,

**C6**= Cell that holds the first data in the dataset**C5**= Cell above the first data cell**C7**= Cell below the first data cell**$E$6**= Difference between data points.

Looking at our data, it seems that when a data point is **differ 10 units** from others, is designated as an **outlier**. So we put **10** in **Cell E6 **and make it an **Absolute Cell Reference**Â so that this cell will be fixed while scanning and calculating through the whole dataset.

After pressing** Enter**, you can see the first data is still appearing in the result cell **H6**. All you had to do here is, just to construct the formula so that we can drag the **Fill Handle** to apply the formula to the rest of the cells.

Now drag the row down with **Fill Handle** and you will see the cells that were holding the outliers are now filled with **#N/A**.

And look at the line chart now, this is now a flattened graph without any outlying values in it.

**Formula Breakdown**

**=IF(**

As we are going to compare values based on which we will extract the result, we initiate the formula with the **IF** condition.

**=IF(AND(**

We are going to compare the current data point and the cell above and the cell below. And if both comparisons are true, only then we will show the data point as a result. So, since we need to deal with â€śBothâ€ť comparisons so we should utilize the **AND function**.

**=IF(AND(ABS(**

When we subtract data points, some data may throw negative values. And 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â€ť. So to make sure 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 with the value above and see whether the result is greater than the difference level stored in **Cell E6** and made the cell an **Absolute Reference** to keep the cell value locked.

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

Next, the second part of the comparison which 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()**

Now we put a comma and determine to put **NA function** in there if both the **AND** functions are true.

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

And 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:** **How to Remove Data Validation in Excel (5 Ways)**

**Similar Readings**

**How to Remove a Header in Excel (4 Methods)****Remove Dotted Lines in Excel (5 Quick Ways)****How to Remove Grid from Excel (6 Easy Methods)****Remove Borders in Excel (4 Quick Ways)****How to Remove Compatibility Mode in Excel (2 Easy Ways)**

**3. Remove Outliers Manually in Excel**

Another easy way to eliminate outliers in Excel is, just sort the values of your dataset and manually delete the top and bottom values from it.

To sort the data,

**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**. It sorted the numbers in our dataset in ascending order, placing the smallest ones at the top and the largest ones at the bottom.

Now just manually delete those data to make the dataset outlying values free.

We donâ€™t recommend following this process while working with a large dataset. When your dataset is small and easier to view, only then you can apply this procedure, otherwise donâ€™t implement this.

**Read More:** **How to Remove Sort in Excel (3 Easy Methods)**

**Conclusion**

This article showed you how to **remove outliers** in Excel. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.