How to Find Outliers with Excel

Find outliers in your data in minutes by leveraging built-in functions in Excel.

[@dinadawood]

I had to go through a set of over 300 data points, this helped me so much, I'm so glad I found this video just in time. Thank you!!

[@quinndougan]

Writing an Exam right now using excel and this saved my life!! Thank you!!

[@PayneDeathz]

I regret not opening this video first. Great video!!

[@rynolin342]

This is going to help me so much on my math test. Thank you.

[@isabellaforguson]

This helped me so much I can’t thank you enough!!

[@garinehmelikian8814]

Thank for making this video, you are using my favorite PP template!

[@nickbabulal2526]

Thank you so much this is the best video to explain this you just earned yourself a subscriber

[@absentdata]

Happy that you subscribed😁

[@jorge960621]

Thank you for such a nice and patient explanation!

[@tjdeleon13]

This was awesome. I had a whole page 9 columns wide, saved me so much work. Thank you

[@absentdata]

Glad it helped!

[@mimiallen7873]

Sir also thank you so much for this video, gosh this made it so much simpler to understand how to calculate!!!

[@absentdata]

You are most welcome

[@madoomadoo6382]

I really love your explanations.Thanks a bunch!

[@linspositivevibesonly1046]

This video helped so much❤

[@absentdata]

That's so great

[@zzz11221]

BTW dont forget to sort the data first this will be crucial at bigger datasets as it will be problematic otherwise 2

[@zg5672]

Thanks dude u saved a depressed student about to end it all

[@absentdata]

I am glad this brightened up your day!!!

[@zg5672]

@@absentdata no. it brightened up my life.

[@tentacleprincess5772]

Keep going dude! Stay strong!

[@zg5672]

@@tentacleprincess5772 i am strong now because of absent data

[@tomasvesely6447]

Thank you for the video. Just a quick question why are we multiplying by 1.5? Thanks!

[@datascienceds7965]

Outliers live outside the inner quartile range. By statistical definition, they live 1.5 times below or above your inner quartile range.( copied from the link he ut in the descriotin box)

[@mariaalejandraramirezmarti7724]

You are the best! really really helpful!

[@absentdata]

Happy to help!

[@kadichidu4171]

Please make one complete tutorial video on Excel from basic to advanced

[@biankaolivier8207]

This helped me alot, thanks!

[@tammykonarski6528]

Thank you, thank you, thank you!!

[@igi-chan]

Thank you so much! this is so well made! it explains so well! you're the best!!! :D

[@Tfol077]

This was super helpful brother thank you so much

[@absentdata]

Glad to hear it

[@mslx9496]

If I have no negative values, I asume that I have to use the absolute value for the lower bound, right?

[@nigelhutchinson5332]

Thanks for this great explanation! Curious what the 1.5 factor is as it relates to the upper and lower bounds? Is this some type of outlier exclusion factor? Or just a suitable one based on the data set?

[@absentdata]

1.5 is a constant that is used in this statistical formula. Think of it like PI

[@damionc]

@@absentdata I found that I had to change that 1.5 to 1 for the dataset I was working with for it to pick up other values I considered outliers as well. Will do some more research on that factor and what it is exactly but I had to change it to get a better result.

[@catherineshepperdley7951]

Thank you - This was really helpful!

[@ynn9173]

When can outliers be positive and when can they be negative for the data set?

[@absentdata]

It depends on the data set. For example even if you had a outlier boundaries that's negative, it doesn't always apply. For examples a child birth dataset might not use any negative outliers then you would just use 0 as your range.

[@pipertaylor3641]

this is a lifesaver

[@MikeSocayre]

It is a very helpful video, but what is the name of this method?

[@absentdata]

Iqr method

[@sirtopinhat9217]

I like this video, very helpful thankyou

[@tvoyodelo2858]

Extremely helpful!

[@Tubeytime]

No fluff, thanks

[@Hertesler]

Thanks for this great video!

[@leonamoliveira7034]

Thank you for your help

[@kelsichesnut]

Super helpful, thank you!

[@albert276]

how do we know we want 1.5?

[@jedn1660]

Very helpful, thank you

[@kasy26x0x]

Thanks for the video!

[@loverose4732]

Thank you so much, sir.

[@VyTran-nu3on]

thank you!

[@williamtully6161]

Fantastic! Awesome Thank you!!

[@lBubbleable]

I found this very helpful, thank you! I'm trying to compare different business with their respective values to find the most "different" one. Is this method ok to use in that case?

[@absentdata]

In a sense yes, you may be doing a univariate analysis of the three businesses. For example you may be looking at Revenue across all the three businesses in comparing their distributions. You can compare the shape of the distribution whether it's a normal distribution or a very skewed distribution. Skewed distributions are affected by outliers.

[@lBubbleable]

@@absentdata Thank you so much!!

[@gabrielvaz7014]

amazing. thank you!

[@eddyrealon1105]

What is the basis for the 1.5 multiplied to the IQR? What is the rationale for that? Thank you!

[@absentdata]

This is part of the accepted original formula. Essentially quartiles can be scaled to standard deviations. The math equates 1.5×IQR to under 3 standard deviations from the mean. So the 1.5 was designed in the formula to capture the majority of outliers assuming the data is normally distributed.

[@ragexbabayega4058]

What if the lower quartile is negative in value, then our IQR turns in a really large value of Q3+Q1, will this affect the values of outliers?

[@absentdata]

No it should be fine.. However it depends on the data. Should you have negatives or not might be a better question

[@adamatraore1883]

Hello, I tried to use the function "OR", I couldn't get the right answer. Instead I used the Function "AND". Thanks for the Video

[@absentdata]

Thanks I'm glad you solved the issue in the end

[@kameshkumar1239]

Thank you so much

[@payammadani3781]

liked and subscribed. a ton thanks

[@ask-wj2pf]

Thankyou

[@absentdata]

You’re welcome 😊

[@deepankumarr4256]

How this works on positive only data. I have list of numbers coming daily say. I want to know if the trends are inline. What is the best way to use that. If there is any dip or spike in the trend, how to t find out.

[@absentdata]

Works on negative outliers also

[@aqsanaveed7976]

can we delete the outliers and then proceed by leaving the spaces blank or do we have to fill them?

[@absentdata]

You could do that by using an if function

[@Metia_9898]

THANK YOU BRUHHHHH

[@mahmoudfaouzichaoubi5855]

شكراً، Tank you

[@mimiallen7873]

Can you tell me in general what the reason is why a cell won't calculate?

[@absentdata]

Its hard to say without having the actual file. There are a host of reasons.. data is not a number incorrect formula

[@mimiallen7873]

@@absentdata Ok thank you for that!

[@hienle-kc6qw]

Can i just plug in whatever the Q1 and Q3 I want? Thank you!

[@absentdata]

Welll not really because that invalidates the need for calculating the quartiles. However, I think the approach you are trying to take is more maual where you are setting the boundaries for the outliers yourself.

[@hienle-kc6qw]

@@absentdata yep, I work with big listings at my work and we look for values that out of range based on unique base values. Thank you!

[@dileep3809]

Can U please prepare a proper statistics series.i went through many videos but I like the way you teach

[@absentdata]

I am glad you enjoy the way I teach. Yes, I want to do a purely statistic playlist. Hopefully there is enough interest :)

[@akifrahman]

I tried to do this method but I would always get negative values for the lower bound so I can't tell if there's an outlier or not

[@absentdata]

Probably indicates that your data is very normally distributed with no outliers

[@akifrahman]

@@absentdata would you mind if I showed you my data and say if you think I have correctly identified the outliers?

[@akifrahman]

@@absentdata also my data are decimals so like 0.0347 etc. Would that affect the method at all?

[@simonbreecher8542]

why do we multiply by 1.5?

[@absentdata]

This is standard multiplier for outlier detection. However you can increase it

[@JassiLive2810]

BRO THERE IS SOMETHING WRONG IN THIS DATA....BECAUSE WHENEVER I JUST APPLIED A DIFFERENT FORMULA TO FIND FIRST AND 3RD QUARTILE I JUST GET DIFFERENT VALUES WHICH IS NOT POSSIBLE

[@Lovelyeye2020]

I have the same problem

[@manas806]

how to count the number of true and false after this for the outliers

[@evachristine2138]

I have over 20,000 columns of data, how do i do this without doing each one seperately?

[@absentdata]

Well a column should be seen as a unique dimension in your data. so if you have 20,000 columns. By Excel only has 16,384. I would question the structure of your data. I would see big you can melt the columns down to rows

[@iroha283]

What does it mean when u say click F4?

[@iroha283]

Is it on the keyboard? What if I’m using excel on an iPad

[@absentdata]

⌘⌥R or Command T in Mac I believe it will help you lock the position of the cells. BTW I've never had a Mac so I am hoping Google is correct :)

[@StellaNimas]

Hii! can i use this for a likert scale data?

[@adewaletijani7023]

Is 1.5 a constant ?

[@absentdata]

Yes, its a best practice to use 1.5. However, you can alter it but then release that you will lose data. It conforms to a standard normal distribution which means that it's a reliable way of saying that most of your data falls under this about 97.5%

[@karanpatil8218]

WHY HAVE YOU USE 1.5* IN UPPER BOUND

[@absentdata]

1.5 is a constant value that I used in the equation

[@vivekkoli4479]

Why 1.5?

[@ellereal8216]

How to apply this to 3 data points only? Can you help?

[@absentdata]

Do you just 3 rows of data? That would be the best use of this.

[@ellereal8216]

@@absentdata No. I mean just three data. I conducted an analysis with 3 trials so I generated 3 data points. And I need to know whether I can average all those three and report it. Edited** What I meant with "analysis" is "experiment"

[@absentdata]

@@ellereal8216 Yes that could work for your specific application.

[@ellereal8216]

@@absentdata Is that the best option for my case to use to identify the outlier? Can you advise me some other stat tools I can easily apply on a calculator.

[@absentdata]

@@ellereal8216 Unfortunately I can't really prescribe what to do cause I would need to know your data and context. However I think you can just explain your logic when presenting your findings. You can also can also use percentiles.

[@ghaidferas6146]

why the 1.5?

[@absentdata]

Its a statistical formula. Its similar to using a constant like 3.14 for PI

[@TheGollo10]

What is the name of the test?

[@mickeylandcaster3]

I am not sure the test has a name. It's more of a technique to classify the borders of your data.

[@prasannakumar-qv8nf]

why do you multiply 1.5 to upper bound

[@fuad471]

it is just a statistical rule

[@ryujuano8414]

F4 (absolute value) won't work in windows :(

[@naijastylist5545]

Ryujinne Juano press( fn ) (f4) it would work

[@marissakachadoorian4772]

I have a mac and f4 also does not work

[@bernardkibathi9347]

in Mac is 'Command + t' or '⌘+t'.

[@wilsonbrown3521]

How to calculate outliers by Kermit the Frog

[@erniestremathic2290]

Hello Sir. If the Result Show True. Meaning That we have to delete the data (true only) or the significance true data? (Im Using Panel Data). For eg: 2013 TRUE apple.co 2014 False apple.co 2015 TRUE apple.co 2016 False apple.co 2017 False apple.co SHOULD I dELETE ALL.. or just the true ?

[@berto_osorio_fl]

thank you!

[@Window20230]

Very helpful video, a quick question: Can I use this method to emit outliers in a degradation trendline? or this method is for data that fit normal distribution or some other distribution only?

[@absentdata]

Yes, absolutely. However then you are going to be dealing with standard deviation for normal distribution. This would be a different approach

[@Window20230]

@@absentdata Thanks！