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

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

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

- 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**

- How to Remove Numbers from a Cell in Excel
- How to Remove Panes in Excel
- How to Remove 0 from Excel
- How to Remove Drop Down Arrow in Excel
- How to Remove HTML Tags from Text in Excel

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