FORECAST.ETS Function in Excel

Excel’s FORECAST.ETS function predicts a value from historical time-series data using exponential smoothing.

It can account for a repeating seasonal pattern, making it useful for forecasts such as recurring sales cycles. The timeline needs a consistent underlying interval.

Seasonality and missing-data settings affect the forecast. This function is available in supported desktop Excel versions, not Excel for the web.

In this article, I’ll show you how to forecast future months, control seasonality, and handle missing observations while adding a confidence range.

FORECAST.ETS Function Syntax in Excel

The FORECAST.ETS function uses exponential smoothing to predict a future value from a timeline and its historical values.

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

• target_date is the future date or numeric point for which you want a forecast.
• values contains the historical values you want Excel to analyze.
• timeline contains the dates or numeric points that correspond to the historical values.
• [seasonality] is optional. Use 1 or omit it for automatic detection, 0 for no seasonality, or a positive whole number for a known cycle length.
• [data_completion] is optional. Use 1 or omit it to fill a missing point with the average of its neighbors. Use 0 to treat it as zero.
• [aggregation] is optional. It tells Excel how to combine values with the same timestamp. The default 0 uses AVERAGE.

When to Use the FORECAST.ETS Function

• Forecast monthly, quarterly, or yearly values when the history contains a repeating seasonal pattern.
• Generate forecasts for several future dates with one spilling formula.
• Specify a known cycle length instead of relying on Excel’s automatic seasonality detection.
• Model a regular timeline that has a small number of missing points.
• Build formula-based forecasts and confidence bounds inside an existing worksheet.

Example 1: Forecast Several Future Months

Let’s start with a monthly forecast that returns several results at once.

Below is a 12-month history of subscription renewals, followed by three future months and an empty forecast column.

I want to forecast renewals for January, February, and March 2026 with one formula.

Enter this formula in cell E2:

=FORECAST.ETS(D2:D4,$B$2:$B$13,$A$2:$A$13)

The target_date argument is the three-cell range D2:D4. Excel returns one forecast for each date and spills the results from E2 through E4.

The forecasts are 212.1, 218.2, and 224.2 renewals. The absolute references keep the historical values and timeline fixed.

Pro Tip: Keep E2:E4 clear before entering the formula. Any content in the intended spill range causes a #SPILL! error.

Example 2: Set Quarterly Seasonality

You can also tell Excel the exact length of a seasonal cycle.

Below are eight quarters of community-room reservations, plus two future quarters and an empty forecast column.

I want Excel to forecast the next two quarters using the known four-quarter pattern.

Enter this formula in cell E2:

=FORECAST.ETS(D2:D3,$B$2:$B$9,$A$2:$A$9,4)

The fourth argument is 4, so Excel uses a four-quarter seasonal cycle instead of detecting the cycle length automatically.

The formula spills 102.7 reservations for March 2026 and 141.5 for June 2026.

Pro Tip: If you do not know the cycle length, omit the fourth argument. You can also use FORECAST.ETS.SEASONALITY to see the pattern Excel detects.

Example 3: Forecast Without Seasonality

Some timelines trend steadily without a repeating seasonal cycle.

Below is a monthly maintenance backlog that falls through 2025, with January 2026 ready for two forecast methods.

I want to turn off ETS seasonality, then compare the result with a straight-line forecast.

Enter this FORECAST.ETS formula in cell E2:

=FORECAST.ETS(D2,$B$2:$B$13,$A$2:$A$13,0)

The fourth argument is 0, which tells FORECAST.ETS not to use a repeating seasonal pattern. The result is 247.1 open requests.

Enter this FORECAST.LINEAR formula in cell F2 for comparison:

=FORECAST.LINEAR(D2,$B$2:$B$13,$A$2:$A$13)

FORECAST.LINEAR fits a straight regression line and returns 247.0. The two results are close here, but they come from different forecasting methods.

For a clearly nonseasonal trend, FORECAST.LINEAR is usually the more direct choice.

Example 4: Handle a Missing Month

FORECAST.ETS can work with a regular timeline even when a few time points are missing.

The service-visits history below skips April 2025. September is the target month, with two empty result cells for different missing-data settings.

I want to compare Excel’s default missing-point treatment with treating the missing month as zero.

Enter this formula in cell E2 to fill the missing point with the average of its neighbors:

=FORECAST.ETS(D2,$B$2:$B$8,$A$2:$A$8,0,1)

The fifth argument is 1. Excel estimates the missing April value from the neighboring points and returns a forecast of 108.0 visits.

Enter this formula in cell F2 to treat the missing point as zero:

=FORECAST.ETS(D2,$B$2:$B$8,$A$2:$A$8,0,0)

Changing data_completion to 0 treats April as zero. That produces a slightly lower forecast of 107.7 visits.

Excel supports up to 30% missing points. If more of the timeline is missing, clean or complete the source data before forecasting.

Example 5: Add a 95% Confidence Range

A point forecast is more useful when you can also see the uncertainty around it.

Below are 12 months of repair-parts usage, three future months, and empty columns for the point forecast and its lower and upper bounds.

I want a point forecast for each month, followed by a 95% confidence range around each result.

Enter this point-forecast formula in cell E2:

=FORECAST.ETS(D2:D4,$B$2:$B$13,$A$2:$A$13)

The formula spills forecasts of 539.2, 535.9, and 561.8 parts for the three target months.

Enter this lower-bound formula in cell F2:

=E2:E4-FORECAST.ETS.CONFINT(D2:D4,$B$2:$B$13,$A$2:$A$13,0.95)

FORECAST.ETS.CONFINT returns a confidence radius for each date. Subtracting it from the point forecasts gives lower bounds of 517.5, 513.6, and 538.8.

Enter this upper-bound formula in cell G2:

=E2:E4+FORECAST.ETS.CONFINT(D2:D4,$B$2:$B$13,$A$2:$A$13,0.95)

Adding the radius gives upper bounds of 560.8, 558.3, and 584.8. The range describes uncertainty in the model, not a guarantee about future demand.

Pro Tip: Keep the optional arguments consistent between FORECAST.ETS and FORECAST.ETS.CONFINT when you specify them. Otherwise, the point forecast and confidence radius may use different models.

Tips & Common Mistakes

• Use a timeline with a constant step, such as the first day of each month. Excel returns #NUM! when it cannot identify a regular interval.
• Keep values and timeline the same size. Mismatched ranges return #N/A.
• Use a target date after the historical timeline. An earlier target date returns #NUM!.
• Aggregate detailed transactions into one value per time point before forecasting. The optional aggregation argument handles duplicate timestamps, but a clean timeline is easier to audit.
• Use enough history to support the seasonal pattern you expect. A yearly cycle based on a few months of data has little evidence behind it.
• FORECAST.ETS is available in desktop Excel 2016 and later, but Microsoft says it is unavailable in Excel for the web, iOS, and Android.
• In Excel 2019 and earlier, use one target date per formula and fill down. Desktop Excel 2021 and later can spill results from a target-date range.
• A blocked spill range causes #SPILL!. The implicit-intersection operator before the function would reduce a range calculation to one result.

I have shown you how to create seasonal forecasts, set or remove seasonality, handle missing points, and calculate confidence bounds with FORECAST.ETS.

I hope you found this article helpful.

List of All Excel Functions

Other Excel articles you may also like:

• TREND in Excel
• Excel LOGEST function