Seasonal forecasting in Excel: detect & project seasonality

Most real business data has a season — a December spike, a summer dip, an end-of-quarter surge. This guide shows you how to detect that seasonality in Excel, turn it into seasonal indices, deseasonalise the series, forecast the underlying trend, and then re-seasonalise to a final forecast. We’ll use the classic ratio-to-moving-average method by hand, and the one-cell FORECAST.ETS.SEASONALITY shortcut.

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

1.What seasonal forecasting is

A seasonal time series can be thought of as three things layered on top of each other: a slow-moving trend-cycle (where the business is heading), a repeating seasonal pattern of fixed length (the monthly or quarterly fingerprint), and noise (everything left over). Seasonal forecasting means estimating the first two, projecting them forward, and recombining them.

The cleanest manual approach is classical decomposition using the ratio-to-moving-average method. The intuition: a moving average that spans exactly one full season averages the seasonality away, leaving the trend. Dividing each actual value by that trend estimate tells you how much of the value was seasonal — and averaging those ratios period-by-period gives you a stable set of seasonal indices.

This is the same skeleton that the full Holt-Winters method automates with smoothing parameters, and it builds directly on the plain moving average in Excel. If you already understand a moving average, you are 70% of the way here.

2.Step 1 — Detect the seasonality

Before modelling seasonality, prove it exists. Three quick checks:

• Line chart. Plot the series. If the same shape repeats at a fixed interval — a bump every December, a trough every July — you have seasonality.
• Year-over-year overlay. Put each year in its own column (Jan–Dec rows) and chart them on one axis. If the lines roughly track each other, the pattern is stable and worth modelling.
• FORECAST.ETS.SEASONALITY. Let Excel detect it: =FORECAST.ETS.SEASONALITY(values, timeline) returns the cycle length it finds — 12 for monthly seasonality, 4 for quarterly, or 0 if it detects none.

Set your season length m in a cell (say H1 = 12). Everything below references it.

3.Step 2 — Centred moving average (the trend)

For an even season length like 12, a plain 12-month moving average sits between two months, so you centre it with a second 2-period average (the “2×12” centred moving average). The clean Excel trick is a single weighted formula that gives the first and last month half-weight.

With values in B2:B…, put this in C8 (the centre of the first full window) and drag down:

C8:  =(0.5*B2 + SUM(B3:B13) + 0.5*B14)/12

That single line is the centred 12-month moving average — the half-weighted ends make it symmetric around month 8. The result is the trend-cycle: the series with seasonality and most noise averaged out. The first and last six months won’t have a centred average (the window runs off the edge); that’s expected.

4.Step 3 — Build the seasonal indices

Now divide each actual by its centred moving average to get the seasonal-irregular ratio for that month:

Each ratio mixes the true seasonal effect with noise. To isolate the seasonal effect, average the ratios for the same month across all years. If January ratios sit in rows 8, 20, 32, you average those. AVERAGEIF on the month makes this clean: with a month number in column A,

=AVERAGEIF($A$8:$A$43, A8, $D$8:$D$43)   ' mean ratio for this month

Finally normalise so the 12 indices average exactly 1 (they should sum to 12). Divide each raw average by the mean of all 12 raw averages:

These indices are the heart of the model. They say: whatever the trend is in December, the actual tends to land 34% higher.

5.Step 4 — Deseasonalise the series

Divide every actual by its month’s seasonal index to strip the season out:

Pull the matching index with a lookup — if your 12 indices live in $G$2:$G$13 keyed by month number, =B2/INDEX($G$2:$G$13, A2) works. The deseasonalised column is now a smooth-ish series of just trend + noise, with the December spikes and January dips flattened out. Charting it next to the raw series is the single most convincing “aha” in the whole method.

6.Step 5 — Forecast the trend

Because the deseasonalised series has no seasonality left, you can forecast it with a simple trend tool. The easiest is FORECAST.LINEAR, which fits a straight line through the deseasonalised points and extends it:

=FORECAST.LINEAR(future_period, deseasonalised_range, period_range)

If the deseasonalised trend is curved rather than straight, fit it with a regression instead — see regression analysis in Excel for polynomial and multiple-driver options. For a robust drift estimate you could also smooth the deseasonalised series first; our moving average tutorial covers that. Produce a deseasonalised forecast for every future month you need.

7.Step 6 — Re-seasonalise the forecast

The final step puts the season back. Multiply each future deseasonalised trend value by the seasonal index for that month:

So if your model says next December’s deseasonalised trend is 1,000 and the December index is 1.34, the seasonal forecast is 1,340. Do this for all twelve future months and you have a forecast that breathes with the season instead of flattening it. For confidence bands, take the standard deviation of the in-sample residuals (fitted minus actual) and add ±1.96σ — the same residuals you’d feed into anomaly detection in Excel.

The FORECAST.ETS shortcut

If you don’t need to see the decomposition, Excel can do the whole seasonal forecast in one cell. FORECAST.ETS fits a triple-exponential-smoothing model that detects and projects seasonality automatically:

=FORECAST.ETS(future_date, values, timeline)            ' seasonal point forecast
=FORECAST.ETS.SEASONALITY(values, timeline)             ' the detected season length
=FORECAST.ETS.CONFINT(future_date, values, timeline)    ' +/- prediction interval

It’s faster and usually more accurate than hand decomposition, but it hides the seasonal indices — which is exactly why doing it manually once, as above, is worth the effort. For the underlying smoothing maths, see exponential smoothing in Excel.

Common mistakes

1. Only one season of data. With a single cycle you cannot tell a real seasonal pattern from luck. Insist on two cycles minimum; three or more for stable indices.

2. Forgetting to normalise the indices. If your 12 indices don’t average to 1, the deseasonalised series is biased up or down and the whole forecast drifts. Always rescale so they sum to m.

3. Using a non-centred moving average for even seasons. A plain 12-month average is offset by half a month. Centre it (the half-weighted-ends formula) or your indices will be slightly wrong.

4. Additive vs multiplicative mismatch. If the seasonal swing stays constant in absolute size, use additive seasonality (subtract/add instead of divide/multiply). Multiplicative assumes the swing grows with the level.

5. Treating one-off events as seasonal. A promo or a stockout isn’t a season. Clean those out before computing indices, or they pollute the pattern for that month forever.

The 30-second shortcut

If you’d rather not build the decomposition by hand, our free forecasting calculator detects seasonality and projects a seasonal forecast on any pasted column of values, in your browser, no signup. Same method, same maths, no centred-average gymnastics.

Frequently asked questions