Lesson 5 - Outliers: What to Clean and What to Keep?

Identifying outliers is a key component of EDA. An outlier is a data point that deviates significantly from the rest of the values — but the real question is not “how different is it?” It is, “why is it different?” And that distinction matters because how you handle outliers can directly influence business recommendations.


Remember that not all extreme values are mistakes. In fact, in business data, they rarely are.


In this lesson, you’ll learn how to identify, investigate, and treat outliers in a systematic, context-aware way. We’ll work directly with the rossmann-sales.xlsx file, using the Sales and Customers columns.

1. What Causes an Outlier?

Before opening Excel, let’s understand the possibilities. In retail data like Rossmann’s, high or low values might stem from:

• Genuine business events such as grand openings, clearance sales, or national holidays that cause spikes in sales.
• Data entry errors, for example, an extra zero added accidentally (€50,000 instead of €5,000).
• System glitches during data logging or integration.
• Store closures, weather events, or regional disruptions, which might cause zero sales on what should have been open days.

2. Identifying Potential Outliers in Excel

Let’s begin with the Sales column in rossmann-sales.xlsx. The analyst’s question would be:


“Are there values in the Sales column that are unusually high or low compared to typical daily sales?”


Before deciding whether a value is problematic, we must first define what qualifies as an outlier — and this begins with a foundation in descriptive statistics.

3. Understanding the Statistical Definition of an Outlier

An outlier is commonly defined as a data point that lies far outside the range of expected values, given the distribution of the dataset. To quantify this, we rely on two fundamental statistical measures:


1. The Mean (Average)

The mean represents the central tendency of a dataset. It is calculated by summing all values and dividing by the number of observations.


$$
\bar{x} = \frac{1}{n} \sum_{i=1}^{n} x_i
$$


Where:

• \(x_i\) = each individual value in the dataset
• \(n\) = total number of observations

In Excel, to calculate the mean of the Sales column (where the data starts in cell D2), you would use:

=AVERAGE(D2:D91257)

or simply:

=AVERAGE(D:D) 

2. The Standard Deviation (σ)

The standard deviation measures how spread out the data is around the mean. A small standard deviation means most values are close to the mean; a large standard deviation indicates high variability.


$$
\sigma = \sqrt{ \frac{1}{n} \sum_{i=1}^{n} (x_i  –  \mu)^2 }
$$


Where:

• μ is the mean

• σ is the population standard deviation 
  

To calculate the standard deviation in Excel, use:

=STDEV.P(range)

3.1. Defining Outliers Using the 3σ Rule

A commonly used rule in analytics is the Three Sigma Rule, also known as the 68–95–99.7 Rule. This principle comes from the properties of a normal distribution:

• ~68% of values lie within 1 standard deviation of the mean
• ~95% within 2 standard deviations
• ~99.7% within 3 standard deviations

By this rule, any value that lies beyond three standard deviations from the mean is statistically rare and may be considered an outlier.


$$
\text{Upper Outlier Threshold} = \mu + 3\sigma
$$


$$
\text{Lower Outlier Threshold} = \mu  –  3\sigma
$$


These thresholds help identify data points that fall outside the typical range — either suspiciously high or unusually low.


Here is a short (external) video on Using the mean and standard deviation to identify outliers. Note that the video uses 2σ for calculating outliers – this is also common, based on distribution of data.


Applying the 3σ Rule in Excel

Now that you understand the statistical definition of an outlier using the Three Sigma Rule, let’s apply it to real data from the Rossmann sales file.


Suppose you’ve opened rossmann-sales.xlsx, and your Sales values are in column D, beginning from cell D2.


Step 1: Compute the Mean

The mean (average) sales value gives you a sense of the central value around which other data points fluctuate. It forms the baseline for comparison.


In an empty cell — say, L1 — enter:

=AVERAGE(D2:D91257)

Label this cell as “Mean” in M1 for clarity.


Step 2: Compute the Standard Deviation

Standard deviation measures how spread out the sales values are around the mean. A high standard deviation suggests high variability — possibly due to store size, location, seasonality, or inconsistent data quality.


In L2, enter:

=STDEV.P(D2:D91257)

Label this cell as “Standard Deviation” in M2.


Note: Use STDEV.P when analyzing the entire population (e.g., all sales data in your file). Use STDEV.S only when working with a sample. We will cover samples later in the module.


Step 3: Set the Outlier Thresholds

You’ll now calculate the upper and lower boundaries beyond which a sales value may be considered a statistical outlier.


In L3 (Upper Threshold):

=L1 + 3*L2

Label this as “Upper Outlier Threshold”.


In L4 (Lower Threshold):

=L1 – 3*L2

Label this as “Lower Outlier Threshold”.


These values represent the outer bounds of what would be expected in a normally distributed dataset. Any value above or below these is flagged for review.


Step 4: Flag Potential Outliers

Create a helper column in your data table — for example, in column J, insert a heading called “Outlier?” in J1.


In J2, enter the following formula:

=OR(D2 > $L$3, D2 < $L$4)

which means if the cell value of D2 is more than Upper Threshold OR less than Lower Threshold, then mark that entry as True. Else, mark it as False.


Copy this formula down for the entire column (e.g., through J91257). This will return:

• TRUE for rows where sales exceed the threshold,
• FALSE otherwise.

This step gives you a structured, documented way to isolate unusual values for further inspection.


Tip: Instead of deleting these rows immediately, consider filtering or highlighting them for manual review first.


Step 5: Highlight Outliers Visually (Optional)

To support visual review:

1. Select your Sales column (D2:D91257).
2. Go to Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.
3. Enter the formula:

=OR(D2 > $L$3, D2 < $L$4)

Choose a fill color (e.g., light red) and click OK.

This highlights all outlier values directly in the dataset — a useful visual aid when reviewing data in team discussions or stakeholder sessions.

3.2. Alternative Method: Using Interquartile Range (IQR)

The Three Sigma Rule is appropriate when your data is approximately normally distributed — that is, it follows a bell-shaped curve. However, business data — especially retail sales — is often skewed. Some stores may have vastly different sales patterns due to location, size, or operational differences. In such cases, the Interquartile Range (IQR) method offers a more robust and distribution-independent approach to detecting outliers.

What Is the IQR?

The Interquartile Range (IQR) measures the spread of the middle 50% of the data — the range between the first and third quartiles.


Definitions:

• Q1 (First Quartile): The 25th percentile — 25% of the data falls below this value.
• Q3 (Third Quartile): The 75th percentile — 75% of the data falls below this value.
• IQR = Q3 – Q1

This method ignores the extreme lower and upper 25% of values and focuses on the “core” of your data — making it highly resistant to skewed distributions or existing outliers.

Why Use IQR for Outlier Detection?

Unlike standard deviation-based methods, the IQR method does not assume any specific distribution. It is particularly effective when:

• The dataset is not symmetrical, or clearly skewed.
• There are known segments with inherently different scales (e.g., high-volume vs. low-volume stores).
• You wish to limit the influence of extreme values when determining thresholds.

Detecting Outliers with IQR in Excel

Let’s apply the IQR method to the Sales column in rossmann-sales.xlsx. Your sales data is in D2:D91257.

Step 1: Compute Q1 and Q3

In an empty section of your sheet (e.g., column N), use the following formulas:

Note: Use QUARTILE.EXC to exclude the minimum and maximum values, which improves robustness for outlier detection. If your Excel version lacks QUARTILE.EXC, use PERCENTILE.EXC(D2:D91257, 0.25) and 0.75 respectively.


Step 2: Calculate the IQR

In N4 (labelled “IQR”):

=N3 – N2

This gives the spread of the middle 50% of your data.


Step 3: Determine Outlier Thresholds

Now calculate the upper and lower bounds beyond which a value is considered an outlier:

These thresholds are based on the Tukey Rule for outlier detection, which considers values more than 1.5 × IQR outside Q1 and Q3 as outliers.


In more conservative applications, you may use 3 × IQR to flag only extreme outliers. However, 1.5× is widely accepted in exploratory data analysis.


Step 4: Flag Outliers with a Helper Column

Now create a new column in your dataset (e.g., column O) called “IQR Outlier?”. In cell O2, write:

=OR(D2 < $H$5, D2 > $H$6)

Copy this formula down for all rows in your dataset. This formula will return:

• TRUE if the sales value is an outlier under the IQR method
• FALSE otherwise

You may optionally combine this with the earlier 3σ method to compare the overlap and robustness of both approaches.

Visual Comparison with a Box Plot

A box plot (also called a box-and-whisker plot) visually depicts the IQR and its associated outliers. Here’s how to insert one:

1. Select your Sales

2. Go to Insert → Charts → Box and Whisker.