Excel FORECAST Function: LINEAR vs. ETS

FORECAST.LINEAR predicts a value by fitting a straight line through your history, while FORECAST.ETS predicts a value using exponential smoothing that can detect and project a repeating seasonal pattern. Use LINEAR for a trend with no seasonality; use ETS for anything with a recurring cycle, like monthly sales that spike every December.

How does FORECAST.LINEAR work?

FORECAST.LINEAR fits a simple linear regression line through known_ys against known_xs, then evaluates that line at the x value you supply — it's the same underlying math as SLOPE and INTERCEPT combined into one function.

Data: Month 1-6, Sales 100, 120, 140, 160, 180, 200

=FORECAST.LINEAR(7, B2:B7, A2:A7)
' known_xs (A2:A7) = 1,2,3,4,5,6
' known_ys (B2:B7) = 100,120,140,160,180,200
' Result: 220   (the line continues at +20 per month)

FORECAST.LINEAR was introduced in Excel 2016 as the modern name for this calculation; the older FORECAST function (no suffix) still works identically in every version for backward compatibility — new formulas should use FORECAST.LINEAR since Microsoft recommends it going forward.

How does FORECAST.ETS work, and how is it different from LINEAR?

FORECAST.ETS uses triple exponential smoothing (the ETS/Holt-Winters method), which models three components separately — the overall level, the trend direction, and a repeating seasonal pattern — so it can project a value that accounts for "this month is usually higher than the surrounding months," something a straight line can't do.

=FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation])

target_date:      the future date/period to forecast
values:           historical values (e.g. monthly revenue)
timeline:         historical dates, must be evenly spaced
seasonality:      0 = no seasonality, 1 = auto-detect (default),
                   or a specific cycle length (e.g. 12 for monthly data)
data_completion:  how to fill gaps in the timeline (default 1 = interpolate)
aggregation:      how to combine duplicate timestamps (default AVERAGE)

FORECAST.ETS was introduced in Excel 2016 alongside a family of related functions: FORECAST.ETS.CONFINT for a confidence interval around the forecast, FORECAST.ETS.SEASONALITY to detect the length of the seasonal cycle automatically, and FORECAST.ETS.STAT to return the model's internal statistics (like the smoothing constants).

What is the Forecast Sheet tool, and how do I use it?

Forecast Sheet is a one-click wizard that builds a new worksheet containing a chart of your history plus a projected forecast with shaded confidence-interval bands, using FORECAST.ETS internally so you don't have to write the formula yourself.

1. Select two columns: a date/timeline column and a values column
2. Data tab > Forecast group > Forecast Sheet
3. Choose Line or Column chart, set the forecast end date
4. Click Options to adjust seasonality, confidence interval (default 95%),
   and how to handle missing points
5. Click Create → new sheet with chart + forecast values

Forecast Sheet requires Excel 2016 or later on desktop; it is not available in every legacy or lightweight Excel build, so confirm the Forecast group exists under the Data tab before relying on it in a shared workbook.

What are the requirements and caveats for seasonality in FORECAST.ETS?

FORECAST.ETS needs a timeline with a constant interval between points (daily, weekly, or monthly — but consistently one of those, not mixed) or it returns #NUM!; it also needs at least two full seasonal cycles of history before it can detect a seasonal pattern reliably, so 24 months of monthly data is the bare minimum to detect a 12-month cycle, and more history makes the detected seasonality far more trustworthy.

• Uneven timeline spacing: gaps or irregular intervals must be resolved by data_completion (interpolation) or the function errors.
• Duplicate timestamps: two rows with the same date get combined using the aggregation argument (AVERAGE by default) — decide if that's the right behavior for your data, or pre-aggregate before forecasting.
• Too little history: fewer than 2 full seasonal cycles means seasonality can't be reliably detected; the function may fall back to a weaker or flat pattern.
• Forcing a seasonality length: if you know your cycle is exactly 12 (monthly, annual pattern), pass 12 explicitly rather than trusting auto-detect (seasonality = 1) on noisy or short data.

FORECAST.LINEAR vs. FORECAST.ETS: which should I use?

Use FORECAST.LINEAR for a steady trend with no repeating cycle, and FORECAST.ETS as soon as the data has a seasonal pattern worth modeling.

	FORECAST.LINEAR	FORECAST.ETS
Model	Straight-line regression	Triple exponential smoothing (level + trend + season)
Handles seasonality	No	Yes, automatically or manually specified
Minimum Excel version	2016 (or legacy FORECAST in any version)	2016
Best for	Steady, roughly linear trends with no cycle	Data with a recurring monthly/weekly/annual pattern

For a broader look at when a repeating pattern in your data is real seasonality versus noise, see time series analysis.