Excel FORECAST function: FORECAST.LINEAR and FORECAST.ETS, with examples

The Excel FORECAST function predicts a future value from past data. =FORECAST.LINEAR(x, known_y's, known_x's) returns the point at x on the straight line that best fits the history; FORECAST is the older name and gives the same result. =FORECAST.ETS(target_date, values, timeline) uses exponential smoothing and can detect a seasonal pattern, which suits monthly sales.

The FORECAST functions in one table

Microsoft's page on FORECAST and FORECAST.LINEAR notes that "in Excel 2016, the FORECAST function was replaced with FORECAST.LINEAR as part of the new Forecasting functions." For a straight-line fit, =TREND(known_y's, known_x's, new_x) returns the same number again.

FORECAST.LINEAR: syntax and worked example

Eight quarters of sales, quarter number in A2:A9 and sales in B2:B9.

The forecasts for quarters 9 and 10, with the line's slope and intercept beside them:

=FORECAST.LINEAR(9,B2:B9,A2:A9)     returns 560.39
=FORECAST.LINEAR(10,B2:B9,A2:A9)    returns 579.04
=SLOPE(B2:B9,A2:A9)                 returns 18.64
=INTERCEPT(B2:B9,A2:A9)             returns 392.61
=RSQ(B2:B9,A2:A9)                   returns 0.996

The fitted line rises about 18.6 per quarter, so quarter 9 lands at about 560.4 and quarter 10 at about 579.0.

The check

Microsoft gives the equation behind the function: the line is a + bx, with b = Σ(x − x̄)(y − ȳ) / Σ(x − x̄)² and a = ȳ − b x̄. By hand, x̄ = 4.5 and ȳ = 476.5. The sum of (x − x̄)(y − ȳ) across the eight rows is 783, and the sum of (x − x̄)² is 42:

b = 783 / 42 = 18.643

a = 476.5 − 4.5 × 18.643 = 392.607

Quarter 9: 392.607 + 18.643 × 9 = 560.39

Quarter 10: 392.607 + 18.643 × 10 = 579.04

In the sheet, the same check is one cell that must equal the FORECAST.LINEAR result:

=INTERCEPT(B2:B9,A2:A9)+SLOPE(B2:B9,A2:A9)*9

RSQ of 0.996 says the line explains 99.6% of the variation in the history, and the residuals stay within ±3.6. That says the history is straight. It does not say the future will be. The regression in Excel page covers the same fit with LINEST and the ToolPak.

FORECAST.ETS: syntax and arguments

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

From Microsoft's FORECAST.ETS reference:

• target_date: the date or number to forecast.
• values: the history.
• timeline: the dates, which "must have a consistent step between them and can't be zero."
• seasonality: 1 (the default) detects it automatically, 0 means no seasonality, and a positive whole number sets the length of the cycle, up to 8,760.
• data_completion: 1 (the default) fills a missing point with the average of its neighbors; 0 treats it as zero.
• aggregation: how duplicate dates are combined; the default 0 uses AVERAGE, and SUM, COUNT, COUNTA, MIN, MAX and MEDIAN are the alternatives.

Microsoft describes the method as "the AAA version of the Exponential Smoothing (ETS) algorithm": additive level, trend and seasonality, each smoothed with weights that favor recent data. It is in the family of Holt-Winters seasonal methods described in Forecasting: Principles and Practice. The fitted parameters are chosen by Excel, so the result cannot be reproduced on paper the way FORECAST.LINEAR can.

We ran it in Excel for Microsoft 365 on October 1, 2026, on 24 months of sales: dates 1/1/2024 to 12/1/2025 in A2:A25 and sales in B2:B25. The 2025 months are the series from moving average in Excel; 2024 is the year before.

=FORECAST.ETS(DATE(2026,1,1),B2:B25,A2:A25,12)

With seasonality forced to 12, Excel returned 891.39 for January 2026, 836.10 for February and 975.49 for March: the familiar shape of a soft start to the year, about 8% to 10% above 2025.

Confidence intervals and detected seasonality

=FORECAST.ETS.CONFINT(DATE(2026,1,1),B2:B25,A2:A25,0.95,12)

That returned 20.01, so the 95% interval for January 2026 is 891.39 ± 20.01, or 871.37 to 911.40. Microsoft's CONFINT reference puts it this way: 95% of future points "are expected to fall within this radius" of the forecast. Report the range, not only the point.

Now let Excel choose:

=FORECAST.ETS.SEASONALITY(B2:B25,A2:A25)    returns 6
=FORECAST.ETS(DATE(2026,1,1),B2:B25,A2:A25)  returns 990.79

On two years of data, automatic detection found a 6-month cycle rather than 12, and the January forecast jumped to 990.79 with an interval of ±84.18, four times wider. With seasonality set to 0 the result was 1,045.48. When you know the business is yearly, set 12 yourself; seasonality in sales measures shows how to confirm it first.

Forecast Sheet

Select the date and value columns and choose Data > Forecast Sheet. According to Microsoft's Forecast Sheet guide, Excel creates a new worksheet with a table of historical and predicted values and a chart, using FORECAST.ETS and FORECAST.ETS.CONFINT. The options cover the forecast start, the confidence interval (95% by default), seasonality (detected or set manually), how missing points are filled, how duplicates are aggregated, and whether to include forecast statistics. It tolerates up to 30% missing points. Set seasonality manually there too, for the reason above.

Which to use

• FORECAST.LINEAR for a steady trend with no seasonal pattern: quarterly totals, annual figures, a single line that has grown by roughly the same amount each period.
• FORECAST.ETS for monthly or weekly data with a repeating pattern, at least two full cycles of history, and dates on a regular step.

Neither knows about your pipeline, a price increase or a lost customer. Both project the history forward. On the 24-month data, FORECAST.LINEAR on the dates gives 1,065.35 for January 2026, because a straight line averages the seasonal low away. Where these fit among other methods is covered in predictive analytics.

Where it goes wrong

• FORECAST.LINEAR on seasonal monthly data. It averages peaks and troughs away: 1,065.35 for a January that ETS puts at 891.39.
• Irregular or duplicated dates in FORECAST.ETS. Duplicates are aggregated silently, by AVERAGE unless you say otherwise. Repeating one date in our timeline moved January from 891.39 to 881.10 with no warning. Microsoft lists #NUM! for a timeline with no constant step.
• Too little history. On 12 months alone with seasonality 12, Excel returned 1,425.83 for January 2026, far above any January on record. Use at least two full seasons.
• Treating the point as a promise. Read FORECAST.ETS.CONFINT next to it, every time.
• Forecasting a total that mixes very different customers. Forecast by segment and add up.

Forecasts from your own sales file

Covirage fits statistical models on your uploaded sales history per segment, reports the interval as well as the point, and tracks accuracy against actuals; the external AI model explains the forecast and never produces the figures. See forecasts for sales teams, checked against actuals every month, and how to measure forecast accuracy and bias in Excel to test any of the forecasts above once the months arrive. For a bottom-up annual method, see sales projection; for a workbook that combines the baseline with the pipeline, the sales forecast template.