How to Use the FORECAST.ETS Functions in Microsoft Excel for Seasonal Time Series Forecasting

Time series forecasting is one of the most valuable analytical capabilities in any spreadsheet, allowing you to project future values based on historical patterns, seasonal trends, and underlying growth trajectories. While Microsoft Excel offers several forecasting tools, the FORECAST.ETS family of functions provides the most sophisticated built-in approach, using Exponential Triple Smoothing (ETS) — also known as the Holt-Winters method — to automatically detect and account for seasonality in your data.

This guide covers all four FORECAST.ETS functions in detail: FORECAST.ETS, FORECAST.ETS.SEASONALITY, FORECAST.ETS.CONFINT, and FORECAST.ETS.STAT, with practical examples and explanations of every parameter.

What Is Exponential Triple Smoothing?

Exponential Triple Smoothing is a time series forecasting algorithm that decomposes your data into three components:

• Level — the baseline value of the series at any given point.
• Trend — the upward or downward direction and rate of change over time.
• Seasonality — repeating patterns at regular intervals (e.g., monthly sales peaks in December, weekly website traffic dips on weekends).

The algorithm applies exponential weighting to each component, giving more influence to recent observations while still accounting for historical patterns. This makes it highly effective for real-world data that exhibits both trend and seasonal behaviour.

Excel’s implementation automatically detects the seasonal period in your data, although you can override this with a manual value if you know the correct cycle length.

The FORECAST.ETS Function

The primary function in the family, FORECAST.ETS, generates a predicted value for a specified future date or time point.

Syntax

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

Parameters

• target_date (required) — the date or numeric time point for which you want to generate a forecast.
• values (required) — the range of historical values (the dependent variable, e.g., sales figures).
• timeline (required) — the range of dates or time points corresponding to each value (the independent variable).
• seasonality (optional) — the number of data points in one seasonal cycle. Use 0 for no seasonality, 1 for automatic detection (default), or a specific integer for a known cycle length.
• data_completion (optional) — how to handle missing data points. 1 (default) fills gaps by interpolation; 0 treats missing points as zeroes.
• aggregation (optional) — how to handle multiple values at the same time point. Options: 1 = AVERAGE (default), 2 = COUNT, 3 = COUNTA, 4 = MAX, 5 = MEDIAN, 6 = MIN, 7 = SUM.

Practical Example

Suppose you have monthly sales data in cells B2:B25 (24 months) with corresponding dates in A2:A25, and you want to forecast sales for the next month (the date in cell A26):

=FORECAST.ETS(A26, B2:B25, A2:A25, 1, 1, 1)

This formula:

1. Analyses the 24 months of historical sales data.
2. Automatically detects the seasonal pattern (likely a 12-month cycle for monthly data).
3. Fills any missing data points by interpolation.
4. Returns the predicted sales value for the target date.

The FORECAST.ETS.SEASONALITY Function

Before relying on automatic seasonality detection, it is good practice to verify what seasonal period Excel has detected. The FORECAST.ETS.SEASONALITY function returns this value.

Syntax

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

Parameters

The parameters are the same as FORECAST.ETS, minus the target_date and seasonality parameters. The function analyses your data and returns the number of data points in one detected seasonal cycle.

Practical Example

=FORECAST.ETS.SEASONALITY(B2:B25, A2:A25)

If your data has a clear 12-month seasonal pattern, this returns 12. If no seasonality is detected, it returns 0. If the returned value does not match your expectations, you may need to supply a manual seasonality value in your FORECAST.ETS formula.

The FORECAST.ETS.CONFINT Function

Any forecast is an estimate, and understanding the range of uncertainty is critical for sound decision-making. The FORECAST.ETS.CONFINT function returns the width of the confidence interval at a specified confidence level for a given forecast point.

Syntax

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

Parameters

All parameters are the same as FORECAST.ETS, with one addition:

• confidence_level (optional) — a value between 0 and 1 representing the desired confidence level. The default is 0.95 (95% confidence).

Interpreting the Result

The function returns the half-width of the confidence interval. To calculate the full range:

• Lower bound = FORECAST.ETS result – FORECAST.ETS.CONFINT result
• Upper bound = FORECAST.ETS result + FORECAST.ETS.CONFINT result

Practical Example

=FORECAST.ETS.CONFINT(A26, B2:B25, A2:A25, 0.95)

If this returns 150 and your FORECAST.ETS value is 1200, the 95% confidence interval is 1050 to 1350. This means you can be 95% confident that the actual value will fall within this range, assuming the historical patterns continue.

Using Different Confidence Levels

• 0.80 (80%) — a narrower interval, useful for aggressive planning scenarios.
• 0.90 (90%) — a balanced interval for most business applications.
• 0.95 (95%) — the statistical standard, suitable for formal reporting.
• 0.99 (99%) — a very wide interval, useful for worst-case scenario planning.

The FORECAST.ETS.STAT Function

For advanced analysis, FORECAST.ETS.STAT returns specific statistical metrics about the ETS model fitted to your data.

Syntax

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

Statistic Types

The statistic_type parameter accepts the following values:

• 1 — Alpha (level smoothing constant)
• 2 — Beta (trend smoothing constant)
• 3 — Gamma (seasonality smoothing constant)
• 4 — MASE (Mean Absolute Scaled Error)
• 5 — SMAPE (Symmetric Mean Absolute Percentage Error)
• 6 — MAE (Mean Absolute Error)
• 7 — RMSE (Root Mean Squared Error)
• 8 — Step size detected in the timeline

Key Metrics Explained

Alpha, Beta, and Gamma are the smoothing constants the algorithm has optimised. Values closer to 1 mean the model relies more heavily on recent data; values closer to 0 mean it weighs historical data more evenly.

MASE (Mean Absolute Scaled Error) is a scale-independent accuracy metric. A MASE value below 1 indicates that your model outperforms a naïve seasonal forecast (which simply repeats last season’s values). Values above 1 suggest the model may not be a good fit.

SMAPE (Symmetric Mean Absolute Percentage Error) expresses forecast accuracy as a percentage. Lower values indicate better accuracy. Unlike traditional MAPE, SMAPE handles near-zero actual values more gracefully.

Practical Example

=FORECAST.ETS.STAT(B2:B25, A2:A25, 4)

This returns the MASE value for your model. If it returns 0.72, your ETS model is 28% more accurate than a naïve seasonal baseline — a strong result.

Building a Complete Forecast Dashboard

To create a comprehensive forecast view, combine all four functions in a single worksheet:

1. Column A — future dates (e.g., A26:A37 for the next 12 months).
2. Column B — forecast values: =FORECAST.ETS(A26, $B$2:$B$25, $A$2:$A$25)
3. Column C — confidence interval half-width: =FORECAST.ETS.CONFINT(A26, $B$2:$B$25, $A$2:$A$25, 0.95)
4. Column D — lower bound: =B26-C26
5. Column E — upper bound: =B26+C26

Chart columns A, B, D, and E together as a line chart to visualise the forecast with its confidence band. The widening confidence interval for more distant future dates visually communicates the increasing uncertainty of longer-range predictions.

Handling Data Quality Issues

Missing Data Points

If your timeline has gaps (e.g., missing months), the data_completion parameter controls how FORECAST.ETS handles them. Setting it to 1 (the default) interpolates the missing values, which is usually the best approach. Setting it to 0 treats gaps as zeroes, which can distort the model if the missing data is not genuinely zero.

Duplicate Timestamps

If your timeline contains multiple entries for the same date, the aggregation parameter determines how they are combined. For sales data, 7 (SUM) is usually correct. For measurement data, 1 (AVERAGE) or 5 (MEDIAN) may be more appropriate.

Irregular Time Intervals

FORECAST.ETS requires a regular timeline with consistent intervals. If your data uses irregular spacing (e.g., business days only), consider using helper columns to standardise the timeline to regular intervals, filling non-business days with interpolated values.

When to Use FORECAST.ETS vs Other Forecasting Functions

• FORECAST.LINEAR — use when your data follows a simple linear trend with no seasonality.
• TREND — use for linear trend extrapolation across multiple points simultaneously.
• GROWTH — use for exponential growth patterns without seasonal components.
• FORECAST.ETS — use when your data has both a trend and a seasonal component, or when you are unsure and want the algorithm to detect patterns automatically.

FORECAST.ETS is the most versatile of these options and should be your default choice for most real-world forecasting tasks where the data spans at least two full seasonal cycles.

By combining FORECAST.ETS with its companion functions for seasonality detection, confidence intervals, and model statistics, you gain a complete forecasting toolkit directly within your Excel workbook — no external software or programming required.