You are staring at two years of monthly revenue that quietly climbs, dips every summer, and spikes in December. Someone wants a number for next March. Averaging the trailing three months misses the seasonal pattern, and a straight linear trend flattens the December spike into a shrug. This is exactly the shape FORECAST.ETS was built to handle — a single formula that reads the level, trend, and seasonality out of your history and returns a future value.
This walkthrough covers the arguments in the order they actually matter, gives you a recipe for the seasonality parameter, adds confidence intervals so the forecast is more than a lonely point, and shows when a simpler tool is the right call instead.
What FORECAST.ETS is actually doing
FORECAST.ETS runs an AAA exponential triple smoothing model over your history. In plain terms it fits three moving pieces and projects them forward: the current level (the running average), the trend (how fast that level is drifting), and a seasonal component (a repeating cycle Excel measures against the level). The math has been in production use since the 1960s; it landed in Excel with the 2016 release and shipped in every desktop build since, including Microsoft 365.
There is one platform gotcha that trips up teams: the function is not available in Excel for the web, iOS, or Android. If a shared workbook opens in a browser and the FORECAST.ETS cell shows #NAME?, that is why. The formula recalculates the moment the same file opens in desktop Excel.
#NAME? for the same formula. Microsoft calls this out in the official function reference.
The six arguments, in the order that matters
The signature looks intimidating; only the first three are required, and the rest have sensible defaults you rarely need to touch on well-shaped data.
=FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation])target_date is the future point you want a forecast for. values is your historical y-values (revenue, demand, headcount). timeline is the matching x-values, and it must have a constant step — daily, weekly, monthly, hourly, whatever, but the gap has to stay the same. If your dates are irregular, Excel refuses.
Here is the minimum viable layout. Column A carries the timeline, column B the historical values, and C26 asks for the value at the next timeline point.
| A | B | C | |
|---|---|---|---|
| 1 | Month | Revenue | Forecast |
| 2 | 2024-01-01 | 18,240 | |
| 25 | 2025-12-01 | 31,110 | |
| 26 | 2026-01-01 | =FORECAST.ETS(A26,$B$2:$B$25,$A$2:$A$25) |
Two absolute references keep the historical window pinned so you can drag C26 down a full year and get a rolling forecast without editing anything. That is the whole first-pass workflow — three arguments, one formula, twenty-four months of history in and twelve months of projection out.
Picking the seasonality argument on purpose
The fourth argument is where most tutorials wave their hands. The default is 1, which asks Excel to auto-detect the cycle. Auto-detection works when the pattern is strong and the history covers at least two full cycles; it fails silently when the signal is noisy or the sample is too short. The fix is to state the cycle length yourself.
12 for monthly data with a yearly cycle, 52 for weekly data with a yearly cycle, 7 for daily data with a weekly cycle, 24 for hourly data with a daily cycle. Use 0 to force a non-seasonal (linear-trend) forecast.
The valid range is 0 through 8,760 — the hours in a non-leap year, so hourly data with a yearly cycle is the extreme case. Anything higher throws #NUM!. When you are unsure whether a cycle is real, run the formula twice: once with 0 and once with your suspected integer, then eyeball whether the seasonal version tracks the visible peaks in your history. If it doesn’t, the cycle is probably noise and you should stay with 0.
The two remaining arguments handle rough data. data_completion defaults to 1, which averages the neighbors of a missing timeline point; set it to 0 to treat gaps as zeros — useful if the missing values genuinely mean “nothing happened.” aggregation defaults to averaging duplicate timeline entries (which the formula would otherwise reject), and accepts SUM, COUNT, MIN, MAX, and MEDIAN if averaging would misrepresent your data. Excel tolerates up to 30% missing values before it gives up.
Wrapping the forecast in a confidence interval
A point forecast is a story with no error bar. FORECAST.ETS.CONFINT solves that: same first three arguments, one extra for the confidence level.
=FORECAST.ETS.CONFINT(A26,$B$2:$B$25,$A$2:$A$25,0.95)The result is the half-width of the interval, not the upper bound — so if C26 forecasts 32,400 and D26 returns 4,100, your 95% interval is roughly 28,300 to 36,500. Widen the confidence level to 0.99 for a more conservative band; narrow to 0.80 when you’d rather see the most likely range than the paranoid one.
Plot the forecast column, the upper band (=C26+D26), and the lower band (=C26-D26) on a chart with the history to get a proper fan chart. If you already know how to build a dynamic date list with SEQUENCE, use it to generate the target-date column in one spilled formula rather than typing months out by hand.
FORECAST.ETS vs FORECAST.LINEAR vs Forecast Sheet
FORECAST.ETS is not always the right tool. Two alternatives sit next to it, and each earns its place on a specific shape of data.
| Method | Use it when | Skip it when |
|---|---|---|
| FORECAST.ETS | Data cycles (weekly, monthly, yearly) and you have at least two full cycles of history | History is short, irregular, or has no recurring pattern |
| FORECAST.LINEAR | The relationship is a clean straight line — steady growth, no seasonality | Peaks and troughs repeat; a linear fit averages them away |
| Forecast Sheet | You want the chart, the table, and the confidence bands generated in one click | The forecast has to refresh from formulas on new data every month |
Forecast Sheet — under Data → Forecast Sheet — is a UI wrapper around FORECAST.ETS and FORECAST.ETS.CONFINT. It is the fastest way to produce a one-off report. For a live model that recalculates as this month’s row lands, the formulas are the right pick because they update the moment the source range extends.
The linear-fit fallback
FORECAST.LINEAR takes the same first three arguments but ignores everything after — it fits a straight line and extrapolates. On a genuinely trending series with no cycle (a stable customer count, a linear cost ramp), FORECAST.LINEAR beats FORECAST.ETS because it isn’t tempted to invent a fake seasonal wobble. Use each formula for what it’s actually good at instead of forcing one on both shapes of data.
When the formula errors
FORECAST.ETS is unusually strict about its inputs. Three errors cover almost every failure mode you’ll hit in real workbooks.
- ✓
#NUM!— the timeline steps aren’t constant, or the seasonality argument exceeds 8,760, or your target date sits before the last historical date. Sort by date and check the gaps first. - ✓
#VALUE!— duplicate timeline values. Either dedupe the timeline column or set the aggregation argument to tell Excel how to combine same-date values. - ✓
#N/A— the values range and timeline range are different sizes. This shows up after inserting a row into one column and not the other. - ✓
#NAME?— the workbook is open in Excel for the web, iOS, or Android. Open it in desktop Excel and the same formula resolves.
The “constant step” rule is the one that catches people out. If your monthly dates are the 1st for most rows and the 3rd for two of them, Excel treats the timeline as inconsistent even though a human would read it as monthly. Normalize the dates with a helper column of clean month-starts before running the forecast.
A small workflow that scales
The reliable pattern is a five-step setup you build once and reuse for every forecast.
- Format the history as an Excel Table (Ctrl+T) so the range grows as you append rows.
- Add a target-date column that extends past the last real row.
- Drop
=FORECAST.ETS(...)into the forecast column, seasonality set explicitly if the cycle is known. - Add
=FORECAST.ETS.CONFINT(...)for the interval half-width, and derive upper and lower bounds from it. - Chart history, forecast, and bounds together; a fan chart makes the growing uncertainty visible at a glance.
Structured references (the Table syntax that reads Sales[Revenue] instead of $B$2:$B$25) make the whole model self-extending. Combine that with LET to name the intermediate ranges and each cell is short enough to read six months later.
Where to go from here
Start with a single series you already report on monthly. Run FORECAST.ETS once with seasonality set to 12, then run it again with 0 to see how much of the projection is trend and how much is the cycle. Add the confidence interval before you present the number to anyone — a point without a band tends to get treated as certain, and it isn’t.