At some point someone looks at your trend chart and asks the inevitable question: “so what will it be next quarter?” Excel will answer instantly — it will extend any line into the future with machine confidence and zero shame. The skill isn’t getting a forecast; it’s getting one you can defend, with its uncertainty attached, and knowing the cases where the honest answer is “this data can’t say.”

What a forecast actually claims

Every statistical forecast makes the same quiet claim: the future will keep behaving like the past. That’s it — no tool in Excel knows about your new competitor, the price rise, or the pandemic. So the first honesty check is human: has something changed that the history can’t know? If yes, the model’s job is context, not prophecy. With that said, plenty of series really do keep behaving — utilisation, steady sales, web traffic — and for those, two tools cover working life.

Tool one: TREND, for straight-ish lines

For a series that grows or shrinks steadily, the workhorse is:

=TREND(Sales, Months, NextMonths)

— fit the best straight line through history, read it forward (spilling one value per future month). Chart people know the same thing as a trendline (right-click the series → Add Trendline), which comes with the honesty gauge attached: R² (tick “Display R-squared”). Near 1, the line explains the data; near 0.5, you’re drawing confident lines through weather. Show the R² or don’t show the trendline.

Tool two: FORECAST.ETS, for seasonal shapes

Most business series aren’t lines — they breathe: December peaks, August slumps, weekly rhythm. Fitting a line through seasonality gives an answer that’s wrong twice a year in opposite directions. Modern Excel’s answer:

=FORECAST.ETS(next_date, Sales, Dates)

— exponential smoothing that detects and replays the seasonal pattern. Its companion is the one that separates honest forecasts from decoration:

=FORECAST.ETS.CONFINT(next_date, Sales, Dates)

— the ± band around the point estimate. Or skip the formulas: Data → Forecast Sheet builds all of it — forecast, upper, lower, chart — in one dialog. It’s the most underused honest button in Excel.

today the fan is the honesty the rhythm repeats… …probably, within THIS range
A defensible forecast has three parts: the pattern, the projection, and the widening band that admits how far confidence reaches.

The honesty rules

Four disciplines make forecasting respectable:

  1. Always show the band. A point forecast (“December: 1,240”) invites false precision; a band (“1,050–1,430, 95%”) shows the claim’s real size. If the band is embarrassingly wide, that is the finding.
  2. Feed it enough history. Seasonal detection needs at least two full cycles — ideally three. Twelve months of data cannot establish a yearly rhythm; it can only assume one.
  3. Short reach only. The fan widens for a reason — forecasting three months from three years of data is respectable; forecasting three years is astrology with confidence intervals.
  4. Backtest before you trust. Hide the last six months, run the forecast, compare with what actually happened. Five minutes, and now you know how this method performs on this series — the difference between “Excel said” and “the model, which was within 6% over the backtest, says”.

And a data note from the cleaning stage: FORECAST.ETS needs real dates on a regular rhythm — text dates or gappy months break it, and the fix is the usual one, upstream.

What forecasting is for

Not prophecy — planning ranges. Stock to hold, staff to rota, the budget’s next-quarter expectation, the sanity line on a target (“the trend says 1,100; the target says 1,500; the gap is the conversation”). Wired into the dashboard with its band drawn honestly, a forecast turns arguments about optimism into arguments about assumptions — which are the only arguments a spreadsheet can actually settle.

The line extends either way. The craft is the fan around it.