How to do Holt-Winters forecasting in Excel — step-by-step

A practical walk-through of triple-exponential-smoothing in pure Excel formulas. No Solver required, no Python add-in, no library imports. By the end you’ll have a 12-month forecast with seasonality and an honest understanding of the smoothing parameters.

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

1.What Holt-Winters actually is

Holt-Winters — sometimes called triple exponential smoothing — is a method published by Charles Holt and Peter Winters in the early 1960s. It extends simple exponential smoothing with two extra ideas: a trend component (so the forecast can drift up or down) and a seasonality component (so a December spike still happens in next December’s forecast).

The mental model: imagine three running averages. One tracks the current level (where the line is right now), one tracks the trend (how fast the line is moving), and one tracks the seasonal pattern (the recurring monthly/quarterly fingerprint). Each one has its own smoothing parameter that decides how much new data should overwrite the old running estimate.

It works extremely well on data that has both trend and seasonality — e-commerce GMV, SaaS MRR with end-of-quarter spikes, agency monthly billings, energy demand. It does not work well on data with structural breaks (e.g. a relaunch that changed the business shape) or pure white noise.

2.Step 1 — Lay out the data

Open Excel. Put dates in column A and the observed value in column B. The data should be at least two full seasonal cycles — for monthly data with seasonality length m=12, that’s 24 months. More is better; 36-48 months is ideal.

Reserve cells for parameters: put α in E1, β in E2, γ in E3, and seasonality length m in E4 (12 for monthly).

3.Step 2 — Initialise the seasonal indices

For multiplicative Holt-Winters (the default for retail/SaaS data), the initial seasonal index for month j is the average of values for that month in the first season, divided by the overall average of the first season.

For monthly data with one full year of history to initialise from, this is just =B2/AVERAGE($B$2:$B$13) for January, =B3/AVERAGE($B$2:$B$13) for February, and so on. Stick the 12 indices in column D rows 2–13.

4.Step 3 — Initialise level and trend

Two simple values to start the recursion:

For T, in cell F13: =(AVERAGE(B14:B25)-AVERAGE(B2:B13))/$E$4.

5.Step 4 — The three update equations

This is the core of the method. For each new period t after initialisation:

In Excel, with values starting in row 2 and initialisation in row 13, the row 14 formulas look like:

C14:  =$E$1*(B14/D2) + (1-$E$1)*(C13+F13)        ' level
F14:  =$E$2*(C14-C13) + (1-$E$2)*F13              ' trend
D14:  =$E$3*(B14/C14) + (1-$E$3)*D2               ' seasonality (offset by m=12)

Drag those three formulas down to the end of your historical data. You now have a fitted model.

6.Step 5 — Pick alpha, beta, gamma

This is the part most tutorials hand-wave. Three options, in order of effort:

Option 1 — Solver. Set up an objective cell that computes mean absolute percentage error (MAPE) of the in-sample fit. Use Solver (Data → Solver in Excel) to minimise it by changing E1:E3, with constraints 0 < α, β, γ < 1. Two clicks once you’ve set it up.

Option 2 — manual grid search. Try the 125 combinations of {0.1, 0.3, 0.5, 0.7, 0.9} for each of α, β, γ. Pick the combination that minimises MAPE on the last 6 months of your data (held out from fitting). This is what auto-tuned packages mostly do under the hood.

Option 3 — default. α = 0.3, β = 0.1, γ = 0.3 is a serviceable starting point for monthly data with mild seasonality. Use it if you’re in a hurry; it won’t be the best fit but it almost never embarrasses you.

One thing nobody warns you about: do not optimise on the entire series. Always hold out the last 10-20% as a test window. Models that fit the past perfectly almost always over-fit and forecast poorly.

7.Step 6 — Produce the forecast and bands

For periods beyond the data, the forecast for horizon h ahead of the last observation is:

In other words: take the latest level, project it forward by h trend-units, then multiply by the seasonal index for the matching month.

For confidence bands, compute the standard deviation of the in-sample residuals (forecast minus actual on every fitted period) and add ±1.96σ to the point forecast for a 95% band. Those same residuals are what you feed into anomaly detection in Excel once seasonality has been stripped out. This is approximate — it assumes residuals are normal, which they often aren’t — but it’s the band most BI tools render and it’s defensible for monthly data.

Additive vs multiplicative seasonality

The equations above are for multiplicative Holt-Winters — seasonality scales with the level. Use this when the seasonal swing gets bigger as the trend grows (typical for revenue, sales). If you only need a quick trend line rather than full seasonality, a moving average in Excel is the lighter-weight alternative.

If your seasonal swing stays constant in absolute terms (typical for things like temperature, headcount), use the additive form instead: replace the divisions and multiplications by S with subtractions and additions. The Wikipedia article on exponential smoothing has the full additive equations.

Common mistakes

1. Not enough data. Two seasons is a hard minimum; one season fits the seasonal indices perfectly but tells you nothing about whether they generalise. If you have 18 months, switch to a non-seasonal Holt model (drop the S equation) instead.

2. Mixing additive and multiplicative. The two forms are not interchangeable. If your data has zero or negative values, multiplicative will divide by zero and produce nonsense.

3. Re-fitting α/β/γ every period. The smoothing parameters should be fixed for a given series. Re-tuning them every month gives you the illusion of a perfect fit and forecasts that wobble.

4. Forecasting through a structural break. If the business changed shape (relaunch, acquisition, pricing change), Holt-Winters will keep projecting the old shape forwards. Forecast the post-break period only, or model the break explicitly.

The 30-second shortcut

If you’d rather not build the spreadsheet by hand, our free forecasting calculator runs auto-tuned Holt-Winters on any pasted column of values, in your browser, no signup. Same method, same maths, no Solver gymnastics.

Frequently asked questions