Sometimes in our dataset, there may remain some outliers. That means, some values get much deviated from most of the other values. While making scatter plotted charts in Excel, it looks a little bit awkward. But no worries, we can easily remove them in Excel. I’ll show 3 easy methods in this tutorial to remove outliers in Excel scatter plot chart.

**Table of Contents**hide

**Download Practice Workbook**

You can download the free Excel workbook from here and practice on your own.

**3 Ways to Remove Outliers in Excel Scatter Plot**

Before starting, get introduced to our dataset first. It shows the obtained marks of some students out of 100.

**1. Removing Outliers Manually in Excel Scatter Plot**

already you may have noticed that most of the numbers are pretty closer, only two values- 5 and 90 are far different. From the scatter plot chart, it’s easier to understand the difference visually. In our very first method, we’ll learn the manual approach to remove the outliers manually. We’ll just identify the values and will delete them. It’s not feasible for a large dataset but for a small dataset, it’s quite helpful.

**Steps:**

- Press and hold the
**CTRL key**on your keyboard and then click on the values one by one. - Then right-click your mouse and select
**Clear Contents**from the**Context menu**. Or, just press the**DELETE key**.

Now you see, the graph became approximately linear. But as we deleted the values, so it became discontinued.

**Read More: ****How to Show Outliers in Excel Graph (2 Effective Methods)**

**2. Using Excel IF, AND, ABS, and NA Functions to Erase Outliers in Scatter Plot**

For a large dataset, we’ll have to use some functions to delete outliers. We’ll use the **IF**, **AND**, **ABS**, and **NA **functions in this regard. To apply the formula, I have added a new column- **D**. The formula will check the difference between every value and its previous and next value. We’ll specify the difference, so if the difference passes our specified range then the formula will remove that value and will return an **#N/A** error. For my dataset, I specified the difference- **5** in **Cell D14** and inserted a blank row before the first data because to find the difference we’ll need previous data. The blank cell will work as zero here.

**Steps:**

- Insert the following formula in
**Cell D6**–

`=IF(AND(ABS(C6-C5)>$D$14,ABS(C6-C7)>$D$14),NA(),C6)`

- Later, hit the
**Enter**button to get the output.

**Formula Breakdown:**

**ABS(C6-C7)>$D$14**Here, the ABS function will find the absolute difference between**Cell C6**and**C7**. then the output will be checked whether it is greater than the specified difference or not. which will return-**FALSE**

**ABS(C6-C5)>$D$14**It will find the absolute difference between**Cell C6**and**C5**. then again the output will be checked whether it is greater than the specified difference or not. which will return-**TRUE**

**AND(ABS(C6-C5)>$D$14,ABS(C6-C7)>$D$14)**Later, the**AND**function will combine the outputs and will return as-**FALSE**

**IF(AND(ABS(C6-C5)>$D$14,ABS(C6-C7)>$D$14),NA(),C6)**Finally, the**IF**function will do the logical test. If both the previous output returns**TRUE**then it will show #N/A error and if returns**FALSE**then will show the exact value of**Cell C6**. so for**Cell D6**, it will return-**33**

- After that, use the
**Fill Handle**tool to copy the same formula for the other cells.

The most deviated values are now removed successfully.

- Now select the new column and create a
**Scatter Plot chart**

Then you will see that it has removed all the outliers from the **Scatter Plot chart**.

**Read More: ****How to Calculate Outliers in Excel (5 Easy Ways)**

**3. Using MIN Function to Delete Outliers in Scatter Plot**

If you dislike getting a discontinuous chart then you can use **the MIN function**. It will not remove any value, rather will return a specified value if any value passes it. So we’ll get a continuous chart. Here, our specified value is **35**. But this method is not applicable for much deviated lower values. It will only work for upper values. So for this method, I modified the dataset and now it has 2 upper values.

**Steps:**

- In
**Cell D5**, type the following formula-

`=MIN(C5,35)`

- Then press the
**Enter**button to get the result.

- Next, drag down the
**Fill Handle**icon to copy the formula for the rest of the cells.

- Then insert a
**Scatter Plot**chart for the new column and you will get a smooth and continuous chart like the image below.

**Read More: ****How to Find Outliers in Excel (5 Useful Ways)**

**Conclusion**

I hope the procedures described above will be good enough to remove outliers in Excel scatter plot. Feel free to ask any question in the comment section and please give me feedback. Visit **ExcelDemy **to explore more.