An outlier is a data point beyond the other data points in the data set. When you have outlier data, it can skew your data, leading to incorrect inference.
There are several ways to find out an outlier in excel.
By sorting the data
The quickest way to identify outlier us with the small databases is by sorting the data and manually going through some of the values at the top of the sorted data.
Steps
1. Choose the "column header" of the column you want to sort.
2. Tap on the "Home tab."
3. In the editing group, click on the sort and filterer icon.
4. Click on Custom Sort.
5. In the Sort dialog box, select Duration (seconds) in the sort by dropdown and largest to smallest in the order dropdown.
6. Click OK.
The above steps would sort the call duration column with the highest values at the top, Scan.
By Using Quartile Function
Steps
Calculation of the Quartiles.
It has some formulas to calculate to come up with an interquartile range.
Calculation of Upper and Lower Boundaries.
Evaluation of Result.
It is now the final task to be carried out to confirm the outlier presence and absentia.
The Inter-quartile range ( IQR) Measures where the starting and end of the bulk of your excel data lies. Therefore, any value away from this data cluster is probably considered an outlier.
1. Calculate the Q1 and Q2 using the Quartile function for your data.
2. Calculate the IQR by subtraction of Q1 from Q3. =Q3-Q1
3. Calculate the lower bound by multiplying IQR By 1.5 and subtracting it from Q3.
4. Calculate the upper bound by multiplying IQR By 1.5 and adding it to the Q3.
5. Find the point that is smaller than that lower bound or larger than the upper bound. These points are outliers.
Create space
1. Create a small table next to your data.
2. In cell C2, type the formula t to calculate the Q1 value =Qurtile.inc(A2,:A14,1)
3. In cell D2, type the formula to calculate the Q3 value =Quartile. Inc(A2:A14,3)
4. In cell E2, type the formula to calculate the IQR value =D2-C2
5. In cell E3, type the formula to calculate the Lower bound value. =C2-(1.5*E2)
6. In cell E4, type the formula to calculate the Upper bound value. =D3+(1.5*E2)
7. Now, you can determine if it is an outlier for each data value. Type the formula in cell B2=OR((A2<$E$3), A2>$E$4)). It will return a TRUE value if the data value is an outlier and FALSE otherwise.
8. Copy this formula to the rest of the cells in column B by double-clicking on the cell's fill handle.
Apart from these two methods, some other methods can be applied to find an outlier—for instance, using the mean calculation.