How to Spot Anomalies and Outliers in Business Data

An anomaly is a value that does not fit the usual pattern: a day with triple the normal refunds, an invoice ten times the typical size, a branch whose sales suddenly drop to zero. You can spot most of them with four simple methods: the interquartile range (IQR), z-scores, percentage change against a baseline, and business rules. Then comes the important part: deciding whether each one is an error, a fraud risk or a genuine event.

Why anomalies matter

• Errors such as a price captured as R45,000 instead of R450 distort totals and averages.
• Operational problems such as a till that stopped syncing show up as sudden zeros.
• Risk such as unusual discounts, refunds or after-hours transactions may need a closer look.
• Opportunities such as a product suddenly selling well deserve attention too.

Method 1: The IQR rule (robust and simple)

The interquartile range is the spread of the middle 50% of your values. Because it ignores the extremes, one huge value does not distort it. Steps:

1. Find Q1 (25th percentile) and Q3 (75th percentile). In Excel: =QUARTILE.INC(range,1) and =QUARTILE.INC(range,3).
2. IQR = Q3 − Q1.
3. Lower fence = Q1 − 1.5 × IQR. Upper fence = Q3 + 1.5 × IQR.
4. Values outside the fences are potential outliers.

Example: order values have Q1 = R800 and Q3 = R2,400. IQR = R1,600. Upper fence = R2,400 + R2,400 = R4,800. An order of R38,000 is well outside and worth checking. Using 3 × IQR instead of 1.5 flags only the extreme cases, which is handy when you have many rows.

Method 2: Z-scores (how many standard deviations away)

A z-score is (value − mean) ÷ standard deviation. A z-score above 3 or below −3 is unusual for roughly bell-shaped data. In Excel: =STANDARDIZE(value, AVERAGE(range), STDEV.S(range)).

Z-scores work better for things that are naturally stable, such as daily call volumes, daily transaction counts, or delivery times. If you are unsure why mean and median behave so differently, read descriptive statistics for managers.

Method 3: Change against a baseline (for time series)

For daily or weekly figures, compare each period with what you would expect: the same weekday's average over the last four to eight weeks. Mondays are compared with Mondays, not with Saturdays.

In this illustrative example, Wednesday's refunds spiked and Thursday shows none at all. The spike may be a product recall; the zero is probably a system that stopped recording. Both matter. Set a threshold that fits your volatility, such as ±50% for noisy data or ±20% for stable data, and only alert above a minimum rand value to avoid noise on tiny numbers.

Method 4: Business rules

Some anomalies are defined by policy rather than statistics. Rules are simple and easy to explain:

• Discount above 30% on any invoice.
• Refund issued more than 60 days after the sale.
• Negative stock on hand.
• Transactions outside trading hours.
• The same invoice number appearing twice.
• Price below cost.

What to do when you find one

1. Check at source. Look up the original invoice, order or log entry.
2. Classify it: capture error, system problem, genuine but unusual, or needs investigation.
3. Fix or exclude errors and document what you changed.
4. Keep genuine outliers, but consider reporting the median alongside the mean so one big deal does not distort the picture.
5. Fix the cause if the same kind of error keeps appearing, for example with validation on the capture form.

Choosing the right method

You rarely need anything more advanced to catch the problems that matter in a small or mid-sized business. Machine-learning anomaly detection has its place at large volumes, but simple, explainable methods are easier to trust and to act on.

Automating anomaly checks

Running these checks by hand once a quarter is better than never, but anomalies are most useful when caught quickly. Options range from conditional formatting in a shared spreadsheet, to SQL queries on a schedule, to a reporting tool that runs checks every time data refreshes. Summarix, for example, can refresh connected data and send a scheduled report by email or to Slack or Teams, with outliers and data-quality issues called out. See features.

Whatever you use, start with a few high-value checks (refunds, discounts, daily sales by branch), tune thresholds so alerts are rare enough to be read, and pair anomaly detection with the fixes in our data cleaning checklist. For broader patterns rather than single points, see how to find trends in sales data.