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