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–

• Later, hit the Enter button to get the output.

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

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

Download Practice Workbook

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

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

• How to Find Outliers Using Z Score in Excel
• How to Find Outliers in Regression Analysis in Excel
• How to Find Outliers with Standard Deviation in Excel

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