Detect Outliers in Excel Data with pandas

A column of invoice amounts that normally sit around 400 contains one for 48,000. A quantity column has a -40. A delivery log says one parcel took 90 days. Some of these are typos — an extra zero, a sign keyed wrongly, a date in the wrong year — and some are real events that someone should know about. Either way they distort averages and charts, and they are easy to miss in a sheet of thousands of rows. This page, part of Cleaning Excel Data With Pandas, compares three statistical ways of finding outliers on the same data, adds per-group detection and business rules, and flags the results in the workbook rather than deleting anything.

Prerequisites

pip install pandas openpyxl

invoices.xlsx has sixteen invoices in two regions. North's are around 400 with one of 48,000; South's are around 125 with one of 690 and one of -40.

Step 1 — Look before you test

A statistical rule is only useful once you know what normal looks like. describe() per group gives the shape of the data in one line each:

import pandas as pd

inv = pd.read_excel("invoices.xlsx")
print(inv.groupby("region")["amount"].describe().round(1))

North's mean is over 6,000 while its median is about 400 — a gap that large always means extreme values. South's minimum is negative, which for invoices may be a credit note or a mistake. Neither fact needs a formula to see, but both need rules to catch automatically on next month's data.

Step 2 — Flag values outside the IQR fences

The interquartile range method uses the 25th and 75th percentiles, which ignore extreme values, and flags anything more than 1.5 IQRs beyond them:

def iqr_flags(s, k=1.5):
    q1, q3 = s.quantile([0.25, 0.75])
    iqr = q3 - q1
    low, high = q1 - k * iqr, q3 + k * iqr
    return (s < low) | (s > high), (low, high)

flags, fences = iqr_flags(inv["amount"])
print("fences:", tuple(round(f, 1) for f in fences))
print(inv.loc[flags, ["invoice", "region", "amount"]].to_string(index=False))

Across the whole company, only the 48,000 invoice falls outside the fences — the fences are wide because the two regions have different typical amounts. The 690 and the -40 are inside, which is the first sign that the test should run within each region (Step 5). k=1.5 is the conventional multiplier; k=3 flags only extreme values.

Step 3 — See why z-scores miss what you are looking for

The z-score measures distance from the mean in standard deviations, and a common rule flags anything beyond 3. On this data it is a lesson in why that rule is unreliable:

north = inv[inv["region"] == "North"]["amount"]
z = (north - north.mean()) / north.std()
print(z.round(2).tolist())

The 48,000 invoice has a z-score of only about 2.5. The outlier inflates both the mean and the standard deviation it is measured against, pulling its own score down; with eight values, no single value can reach a z-score of 3 at all. Z-scores work for large samples from roughly bell-shaped data with modest outliers. For finding the extreme values themselves, use a method whose yardstick the outliers cannot move.

Step 4 — Use a robust score based on the median

A robust z-score replaces the mean with the median and the standard deviation with the median absolute deviation (MAD), neither of which an outlier can drag far. The 0.6745 constant scales MAD to match the standard deviation for normal data, and a threshold of 3.5 is the usual choice:

def robust_scores(s):
    median = s.median()
    mad = (s - median).abs().median()
    return 0.6745 * (s - median) / mad

print(robust_scores(north).round(1).tolist())

The 48,000 invoice now scores in the thousands, and the ordinary invoices stay close to zero. One caution: if more than half the values are identical, MAD is zero and the score divides by zero. Guard against it by falling back to the IQR rule for such columns.

Step 5 — Detect outliers within each group

A 690 invoice is unremarkable company-wide and very unusual for South, where invoices sit around 125. Compare each row with its own group by computing the statistics with groupby().transform, which returns values aligned to the original rows:

g = inv.groupby("region")["amount"]
q1 = g.transform(lambda s: s.quantile(0.25))
q3 = g.transform(lambda s: s.quantile(0.75))
iqr = q3 - q1
inv["outlier"] = (inv["amount"] < q1 - 1.5 * iqr) | (inv["amount"] > q3 + 1.5 * iqr)
print(inv.loc[inv["outlier"], ["invoice", "region", "amount"]].to_string(index=False))

Per region, the test flags North's 48,000, South's 690 and South's -40 — the three values a person looking at each region would question. Grouped detection is almost always the right default for business data, where "normal" depends on the product, region, customer or month.

Step 6 — Add the rules statistics cannot know

Some values are wrong whatever the distribution says. A negative invoice is either a credit note, which belongs in a different document type, or an error; a quantity of zero on an order line is suspicious; a delivery date before the order date is impossible. Encode those as explicit rules and report them alongside the statistical flags, each with a reason:

reasons = pd.Series("", index=inv.index)
reasons[inv["outlier"]] += "unusual for region; "
reasons[inv["amount"] <= 0] += "not a positive amount; "
inv["reason"] = reasons.str.rstrip("; ")
review = inv[inv["reason"] != ""]
print(review[["invoice", "region", "amount", "reason"]].to_string(index=False))

The -40 invoice now carries both reasons, which tells the reviewer why it was flagged twice. Rules are the part of outlier detection that improves most over time: each confirmed error suggests a rule that catches the next one without statistics. Validate Excel Data with pandera Schemas shows how to keep such rules in a declarative schema.

Step 7 — Flag them in the workbook

Write the data with a flag column and colour the flagged rows, so the people who own the data can review them where they already work. Keep every row; nothing is deleted:

from openpyxl import load_workbook
from openpyxl.styles import PatternFill

inv.drop(columns="outlier").to_excel("invoices_flagged.xlsx", index=False)
wb = load_workbook("invoices_flagged.xlsx")
ws = wb.active
amber = PatternFill("solid", fgColor="FFEB9C")
for row in ws.iter_rows(min_row=2):
    if row[3].value:                         # the reason column
        for cell in row:
            cell.fill = amber
wb.save("invoices_flagged.xlsx")
print(sum(1 for r in ws.iter_rows(min_row=2) if r[3].value), "rows flagged")

Once reviewed, corrections go back to the source system; a note in the reason column, such as "confirmed: bulk order", lets the next run skip a value that is unusual but real. Highlight Invalid Cells in Excel with Python covers highlighting individual cells rather than rows.

Common pitfalls

• Z-scores on small samples. Outliers inflate the yardstick; use IQR or robust scores.
• Company-wide thresholds on grouped data. Compare each row with its own group.
• Deleting flagged rows. Totals become wrong; flag and review instead.
• MAD of zero. Columns with many identical values need a fallback rule.
• Re-flagging confirmed values every run. Keep a list of reviewed invoice numbers and exclude them, or reviewers stop reading the list.

Performance notes

quantile and median are vectorised and fast on millions of rows. groupby().transform with a lambda calls Python once per group, which is fine for hundreds of groups; for tens of thousands of groups, compute the quartiles once with g.quantile([0.25, 0.75]).unstack() and merge them back on the group key.

Conclusion

Look at the data per group first, then flag outliers with IQR fences or median-based robust scores — not plain z-scores, which the outliers themselves distort. Run the tests within each group with groupby().transform, add business rules that no statistic can infer, record a reason for every flag, and mark the rows in the workbook for review instead of deleting them.

Frequently asked questions

What is the IQR method? It flags values below Q1 - 1.5 x IQR or above Q3 + 1.5 x IQR, where Q1 and Q3 are the 25th and 75th percentiles and IQR is the gap between them. Because quartiles ignore extreme values, one huge outlier does not stretch the fences.

Why did the z-score miss an obvious outlier? A large outlier inflates the standard deviation it is measured against, so its own z-score shrinks. In small samples a single extreme value cannot reach a z-score of 3 at all. Use IQR or a median-based score when outliers are what you are looking for.

Should I delete outliers? Not automatically. An outlier may be a typo, a unit error or a genuine large order. Flag it, show it to someone who knows the data, and decide per case. Deleting real data to make charts look tidy produces wrong totals.

How do I find outliers within each category? Compute the statistics per group with groupby().transform so every row is compared with its own group. A 690 invoice is unusual in a region where invoices are around 125, even if it is ordinary for the company overall.

Related

• Up one level: Cleaning Excel Data With Pandas — the full cleaning workflow.
• Validating Excel Data with Python — rule-based checks before import.
• RANK and PERCENTILE Formulas in pandas — quantiles in more depth.
• Highlight Top and Bottom Values in Excel with openpyxl — letting the workbook mark extremes itself.