Excel FORECAST.ETS & Forecast Sheet in 2026: Predict Business Trends with AI-Powered Data Analysis

Published: June 6, 2026 | Category: Excel | Reading Time: 6 min

What if you could predict next quarter’s sales, forecast staffing needs, or anticipate seasonal demand—all inside Excel, without a data science degree? In 2026, Excel’s built-in forecasting tools make that a reality for every business professional. The FORECAST.ETS function and the one-click Forecast Sheet turn historical data into actionable predictions using exponential smoothing and seasonal pattern detection. This guide walks you through everything you need to know to start forecasting like a pro.

What Is FORECAST.ETS and Why Does It Matter?

FORECAST.ETS stands for Exponential Triple Smoothing. Unlike the older FORECAST function, which simply draws a straight trend line, FORECAST.ETS detects and accounts for seasonality—the repeating patterns that naturally occur in business data, such as higher sales in Q4 or lower traffic on weekends. This makes it dramatically more accurate for real-world planning.

In 2026, FORECAST.ETS is available across all Microsoft 365 plans and works seamlessly with Excel’s Copilot to provide AI-assisted commentary on your predictions. The function is ideal for:

Sales and revenue forecasting

Inventory and supply chain planning

Website traffic and engagement projections

Workforce and hiring demand estimates

Budget and financial planning

Understanding the FORECAST.ETS Syntax

The FORECAST.ETS function takes the following arguments:

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

Here is what each argument means:

target_date: The date or period you want to forecast. This can be a cell reference or a date value.

values: Your historical data range—the numbers you want to forecast from (e.g., monthly sales figures).

timeline: The corresponding date or time range for your historical values.

seasonality: Optional. Set to 1 to let Excel auto-detect seasonality, or enter a number (e.g., 12 for monthly data with annual cycles). Use 0 to disable.

data_completion: Optional. Set to 1 (default) to handle missing data by interpolation, or 0 to treat missing points as zero.

aggregation: Optional. If duplicate dates exist, choose how to aggregate: 1=AVERAGE (default), 2=COUNT, 3=COUNTA, 4=MAX, 5=MEDIAN, 6=MIN, 7=SUM.

Step-by-Step: Forecasting Monthly Sales with FORECAST.ETS

Let’s walk through a practical example. Suppose you have 24 months of sales data in column B, with corresponding month-end dates in column A (A2:A25). You want to forecast the next 6 months.

Step 1: Prepare Your Data

Ensure your dates are in a consistent interval (monthly, weekly, daily) and your values have no large unexplained gaps. FORECAST.ETS handles some missing data, but clean, consistent data yields better predictions.

Step 2: Create the Forecast Dates

In cell A26, enter the first future month-end date. Then use the fill handle or EDATE to extend through A31 (6 months ahead). For example:

=EDATE(A25,1)

Step 3: Enter the FORECAST.ETS Formula

In cell B26, enter the following formula and fill down to B31:

=FORECAST.ETS(A26,$B$2:$B$25,$A$2:$A$25,1,1,1)

The dollar signs lock the historical data references, so the formula always looks back at the same source data as you fill down.

Step 4: Add Confidence Intervals (Optional but Recommended)

Use the companion function FORECAST.ETS.CONFINT to add upper and lower bounds. In column C26 enter:

=FORECAST.ETS.CONFINT(A26,$B$2:$B$25,$A$2:$A$25,0.95,1,1,1)

This returns the confidence interval width at 95% confidence. Subtract from and add to the forecast to create lower/upper bounds, giving stakeholders a clear picture of the range of likely outcomes.

Using the Forecast Sheet: One-Click Forecasting

If formulas feel intimidating, Excel’s Forecast Sheet feature does all the heavy lifting for you. Here’s how to access it:

Select any cell in your date-value data range.

Go to the Data tab on the Ribbon.

Click Forecast Sheet in the Forecast group.

In the dialog box, choose an end date for your forecast.

Expand Options to set seasonality, confidence interval, and whether to fill missing data.

Click Create Forecast.

Excel creates a new worksheet with a professional chart showing historical data, forecast values, and confidence bands. It also inserts the underlying FORECAST.ETS formulas in a table, which you can customize and reference in other sheets.

FORECAST.ETS with Copilot in 2026

One of the most exciting upgrades in 2026 is how Copilot integrates with Excel forecasting. Once you have a Forecast Sheet, you can ask Copilot in Excel natural language questions such as:

“What are the key factors driving the forecast spike in Q4?”

“Summarise the confidence intervals for the next three months.”

“Create a bar chart comparing actual vs. forecast values.”

Copilot can interpret the seasonal patterns it detects and explain them in plain English, making it easier to communicate findings to non-technical stakeholders. It can also suggest when to override automatic seasonality detection based on known business events.

Pro Tips for More Accurate Forecasts

Use at least two full seasonal cycles: For monthly data with annual seasonality, use at least 24 months of history.

Check FORECAST.ETS.SEASONALITY: Use this function to see what seasonal length Excel auto-detects. If it returns an unexpected value, manually specify seasonality.

Account for known events: FORECAST.ETS does not know about a product launch or a market disruption. Adjust forecasts manually for known future events.

Combine with conditional formatting: Highlight cells where actuals fall outside confidence bands to instantly spot anomalies.

Use FORECAST.ETS.STAT for diagnostics: This returns 8 statistical metrics about the model quality, including MASE (Mean Absolute Scaled Error) and smoothing parameters.

Common Mistakes to Avoid

Mixing time granularities: Do not mix daily and monthly data in the same range. FORECAST.ETS expects a consistent time step.

Forecasting too far ahead: The further out you forecast, the wider the confidence bands. For most business data, forecasting more than one to two seasonal cycles ahead is unreliable.

Ignoring the confidence interval: A single forecast line looks precise but is always an estimate. Always present the range alongside the point forecast.

Conclusion

Excel’s FORECAST.ETS and Forecast Sheet give every professional access to powerful, statistically sound predictions without needing a specialized analytics tool. Combined with Copilot’s AI-driven interpretation in 2026, forecasting has never been more accessible or actionable. Start by applying the Forecast Sheet to your existing data this week—you may be surprised by the patterns Excel uncovers. If this tutorial helped you, explore our guides on Excel Power Pivot and Copilot in Excel for even deeper data analysis capabilities.

officelearner.net — MS Office Tips, Tricks & Tutorials