How to Remove Outliers in Excel Scatter Plot (3 Easy Methods)

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.

How to Remove Outliers in Excel Scatter Plot: 3 Ways

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

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.

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.

Related Articles

<< Go Back to Outliers in Excel | Excel for StatisticsÂ |Â Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF