Deleting data points within a range from trend line

Occasional Visitor

Hello, I have the attached image (left) of more than 45k data points and I want to delete all the points within a range from the trend line to have it like the one on the right side (only the points between the two red lines, deleting all other points that fall out). I can't think of a solution to this, so please let me know if you know a solution or a way around. Thanks





1 Reply


Instead of plotting y against x and adding a trendline, add the trend data to the table using 

=TREND(y, x)

Plot this against x as a line with no markers.

Filter the y values using a formula of the sort

= IF( ABS(y-t)<a, y, NA() )


= IF( ABS(y-t), y, -100 )

An up to date version of Excel will ignore the '#N/A's but older versions will scatter markers along the x-axis.  If that happens choose a fictional for y value that will be outside the plot region.