3.5 Identifying extreme values

An outlier is an extreme value, which is abnormally different from the distribution of a variable. In other words, the value of this observation greatly differs from the other values of the same variable. Just like the search for duplicates, the detection of these extreme values (or outliers) is an essential step in data cleaning, since extreme values can influence upward, or downward certain statistics produced - particularly those relating to averages - and thus lead to erroneous analyses.

Extreme values can be identified via the following steps: to begin with, determine the 1st quartile Q1=QUARTILE(cell range;1) and the 3rd quartile Q3=QUARTILE(cell range;3) of the variable of interest and then evaluate the interquartile range (IQR), i.e., the middle (50%) spread of your data. The latter is calculated as the difference between the value of the 1st and 3rd quartiles, Q3-Q1. The third step is to calculate the lower and upper limits, which are the smallest and largest values in the data range you are using. For the lower limit, the IQR value is then multiplied by 1.5 and subtracted from the Q1 value. For the upper limit, the IQR is multiplied by 1.5, and added to the Q3 value.

To identify outliers, the =OR() function can be used to locate data below the lower limit value or above the upper limit value.

Depending on the context, a TRUE value may then indicate an outlier (above, line 8).

Conditional formatting can also be used to highlight extreme values, by specifying under which conditions a value is considered as such.

Once you have identified the extreme values, you can either:

• keep them as is,
• replace them with a normal or random expected value,
• remove them from the analysis.

The following exercise is only available on the French page at this stage- translation coming soon!