Anomaly Detection in Excel — Find Outliers and Revenue Spikes with Rolling Z-Scores

A single revenue spike or unexpected drop can mean a tracking error, a broken integration, or a genuine business event — and the difference matters enormously before a month-end report goes out. This tutorial shows you how to build a rolling z-score anomaly detector in pure Excel formulas. No add-ins, no macros. By the end you’ll have a column that flags every statistically unusual data point and a conditional formatting heatmap that makes outliers impossible to miss.

Dr Waqas Rafique · Founder & CTO · PhD, Statistical Machine Learning
LinkedIn · About

1.Step 1 — Organise the time series

Put dates in column A and your metric values in column B. This works for any numeric time series: daily revenue, weekly order count, hourly sessions, monthly churn rate. Sort the data ascending by date — the rolling window formulas rely on rows being in chronological order.

You need at least 30 data points for a rolling 28-day window to produce meaningful statistics. With fewer than 30 rows, the early rows will have very small windows and the z-scores will be unstable — likely flagging too many or too few anomalies.

Add headers in row 1: Date, Value, RollingMean, RollingStdev, ZScore, Flag. Data starts in row 2.

2.Step 2 — Calculate rolling mean (28-day window)

In column C (RollingMean), enter this formula in C2 and drag down:

This looks complex but does something simple: it takes an average over the last 28 rows, or fewer rows if we are near the top of the data. Breaking it down:

ROW(B2)-ROW($B$2) is the zero-indexed row position (0 for the first data row, 1 for the second, etc.). MIN(..., 27) limits the offset to at most 27 rows back, so we never try to read before the start of the data. MIN(...+1, 28) gives the height of the window — up to 28 rows, but fewer at the start.

For the first row of data (row 2), the window is just 1 row, so the rolling mean equals the value itself. By row 29, the full 28-day window is in use. This is called a causal rolling window — it only uses past data, which is what you want for anomaly detection in a live series.

3.Step 3 — Calculate rolling standard deviation

In column D (RollingStdev), the formula is identical in structure but uses STDEV instead of AVERAGE:

Use STDEV (sample standard deviation, divides by n−1) rather than STDEVP (population, divides by n). Since the rolling window is a sample of the series, the sample version is statistically correct.

Important: for the first row, STDEV of a single value returns an error (you cannot compute standard deviation from one point). Wrap the formula in IFERROR(..., 0) or start flagging anomalies only from row 3 onwards where there are at least 2 points in the window.

=IFERROR(STDEV(OFFSET(B2,-MIN(ROW(B2)-ROW($B$2),27),0,MIN(ROW(B2)-ROW($B$2)+1,28),1)), 0)

4.Step 4 — Calculate z-score

In column E (ZScore), the formula is straightforward:

This tells you how many standard deviations the current value is from the rolling mean. Wrap in IFERROR to handle the early rows where the stdev is zero:

=IFERROR((B2-C2)/D2, 0)

Interpreting the result:

Z > +2 or Z < −2: statistically unusual — only about 5% of points in a normal distribution fall outside this range. Worth a second look.

Z > +3 or Z < −3: strong anomaly — roughly 0.3% of points. These are almost always genuine events or data errors.

Z between −2 and +2: within normal variation for this window. Not an anomaly by this method.

5.Step 5 — Flag anomalies with IF formula

In column F (Flag), add a plain-language flag:

You can refine this to distinguish direction and severity:

=IF(E2>3,"HIGH SPIKE",IF(E2>2,"SPIKE",IF(E2<-3,"DEEP DROP",IF(E2<-2,"DROP",""))))

Adjust the threshold to match your use case. For a daily revenue series where you want to investigate anything unusual: use 2.0. For a weekly series where you only want to escalate genuine crises: use 2.5 or 3.0. The threshold does not change the underlying statistics — only the number of rows that get flagged.

6.Step 6 — Highlight with conditional formatting

Select the range of values in column B (e.g. B2:B366 for a year of daily data). Go to Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.

Enter this formula (adjust to match your actual column references):

Set the fill to red. This uses an expanding window (all data from the start up to the current row) rather than a rolling window — it is less precise than the rolling approach above, but requires only a single conditional formatting rule with no helper columns and renders a visible heatmap instantly.

For a more precise highlight, add a second conditional formatting rule that checks the Flag column: formula =$F2="ANOMALY" applied to column B, fill red. This will highlight exactly the rows your rolling z-score formula flagged.

Anomaly detection methods compared

Z-score is not the only approach. Here is how the common methods compare for business time series data in Excel:

For an IQR fence in Excel: =IF(B2<QUARTILE($B$2:$B$365,1)-1.5*(QUARTILE($B$2:$B$365,3)-QUARTILE($B$2:$B$365,1)),"LOW",IF(B2>QUARTILE($B$2:$B$365,3)+1.5*(QUARTILE($B$2:$B$365,3)-QUARTILE($B$2:$B$365,1)),"HIGH","")). Note this uses the entire series as the reference range — adapt to a rolling window if your series has a strong trend.

Seasonal data — the one gotcha

Rolling z-scores work well on stationary or slowly trending data. They break down on seasonal data. If your revenue spikes every December, a 28-day rolling window will flag December as an anomaly every year — because November and January surround it, pulling the rolling mean down.

Two solutions:

Year-over-year z-score: instead of comparing against the trailing 28 days, compare against the same day or week in the prior year. Calculate (this week’s value) − (same week last year’s value) and compute the z-score of those differences. Seasonal spikes that repeat on schedule will cancel out; genuine deviations from the seasonal pattern will still be flagged.

De-seasonalise first: use Holt-Winters or a seasonal decomposition to extract the residuals (the part of the series not explained by trend or seasonality), then apply z-scores to the residuals. See our Holt-Winters in Excel tutorial for the decomposition step. The residuals are by definition free of seasonal effects, so z-scores on them are far more reliable.

When to move beyond Excel

The Excel rolling z-score approach works well for a single series up to a few thousand rows. The formula refresh time is fast, and the visual output is clear. The limitations appear when you need to:

Monitor many columns at once. If you have 20 metrics (revenue, orders, sessions, refunds, conversion rate, etc.), rebuilding the 4-column setup for each one becomes tedious and error-prone. At that point a live KPI dashboard in Excel — or one of the best dashboard software tools — surfaces every flagged anomaly in one view.

Get real-time alerts. Excel does not send you a notification when a new anomaly appears. You have to open the file to see it.

Auto-tune the window and threshold. The right window size and z-score threshold vary by series. Manually calibrating each one takes significant time.