DEV Community

Waqas R
Waqas R

Posted on

Holt-Winters Forecasting in Excel: Trend + Seasonality, Explained

#excel #forecasting #datascience #tutorial

If you forecast anything with both a trend and a repeating seasonal pattern - monthly sales, web traffic, energy use - a plain moving average won't cut it. Holt-Winters (triple exponential smoothing) is the classic method that handles both, and you can run it in Excel with no add-ins.

The three pieces

Holt-Winters tracks three things and updates each as new data arrives:

  1. Level - where the series is right now.
  2. Trend - how fast it's climbing or falling.
  3. Seasonality - the repeating pattern within a cycle (e.g. 12 months).

Each gets its own smoothing weight (alpha, beta, gamma) between 0 and 1. A higher weight reacts faster to recent data; a lower one is smoother and more stable.

The update equations (additive) 

Level:    l_t = alpha*(y_t - s_{t-m}) + (1-alpha)*(l_{t-1} + b_{t-1})
Trend:    b_t = beta*(l_t - l_{t-1}) + (1-beta)*b_{t-1}
Season:   s_t = gamma*(y_t - l_t) + (1-gamma)*s_{t-m}
Forecast: y_hat = l_t + h*b_t + s_{t-m+h}
Enter fullscreen mode Exit fullscreen mode

where m is the season length (12 for monthly data with a yearly cycle).

Doing it in Excel

1. The one-function way. Excel 2016+ has FORECAST.ETS, which is essentially auto-tuned Holt-Winters:

=FORECAST.ETS(target_date, values, timeline, seasonality)
Enter fullscreen mode Exit fullscreen mode

Set seasonality to 12 for monthly data, and pair it with FORECAST.ETS.CONFINT for a confidence band.

2. The manual way. Build the level/trend/season columns straight from the equations so you can audit every step - the only way to really answer "why does it predict that?". I wrote up the full manual build with initialisation and a worked example here: Holt-Winters in Excel.

Pitfalls worth knowing

  • Too few cycles. You need at least two full seasonal cycles (24 months for monthly data) before the seasonal component is trustworthy.
  • Additive vs multiplicative. If seasonal swings grow as the series grows, use the multiplicative form.
  • Over-reacting. Large weights chase noise; auto-tuning by minimising one-step error usually beats eyeballing them.

A quick sanity-check

If you just want a fast trend forecast from a column of numbers without building the whole sheet, I made a free browser tool that auto-tunes the weights and charts the result: free forecast calculator. No signup, runs locally in your browser.

Forecasting won't make the future certain - but Holt-Winters gives you a defensible, transparent baseline, which is usually what the conversation actually needs.

Top comments (0)