Forecasting in Excel: FORECAST.ETS and Trendlines

Excel can forecast future values with two main tools: the FORECAST.ETS function family, which detects trend and seasonality automatically, and chart trendlines, which fit a simple line or curve to your data. For most monthly or weekly sales data, FORECAST.ETS (or the one-click Forecast Sheet) gives the better result; trendlines are best for quick visual checks and steady, non-seasonal patterns.

This guide walks through both methods with a sample sales table, explains every argument, and shows how to judge whether a forecast is trustworthy.

What Excel forecasting can (and can't) do

Every forecast in Excel works the same way: it looks at a history of values over time and projects that pattern forward. That means the forecast is only as good as two things: the quality of your history and the assumption that the future will behave roughly like the past.

• Good fit: monthly sales, weekly orders, website traffic, inventory usage, call volumes, anything with a regular time step and at least a couple of seasonal cycles of history.
• Poor fit: brand-new products with little history, one-off events (a store opening, a price change, a supply shortage), or data with irregular dates that you haven't aggregated.

Excel does not know about promotions, holidays that move, or a new competitor. Treat the output as a baseline that you adjust with business knowledge, not as a guarantee.

Prepare your data first

Forecast functions need two columns: a timeline (dates or period numbers) and the values to forecast. Put them in an Excel Table so ranges expand as you add months.

The figures above are made up for illustration. A few rules matter:

1. Constant step. The timeline must move in even steps: every day, every week, or the first of every month. If you have raw transactions, summarize them first with a pivot table or SUMIFS.
2. Sorted is not required, but helps. FORECAST.ETS sorts internally, yet a sorted table is easier to audit.
3. Few gaps. ETS tolerates up to about 30% missing points and fills them in, but fewer gaps means a better model.
4. Enough history. For seasonal data, aim for at least two to three full cycles (for monthly data with yearly seasonality, two or three years).

If your data needs cleaning, our Excel data cleaning checklist covers duplicates, text-numbers and broken dates.

FORECAST.ETS: the main forecasting function

FORECAST.ETS uses the AAA version of exponential triple smoothing (error, trend and seasonality). In plain terms, it gives more weight to recent periods, tracks the overall direction, and repeats any seasonal pattern it finds.

Syntax

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

• target_date: the future date (or period number) you want a value for.
• values: the historical numbers.
• timeline: the matching dates or periods.
• seasonality (optional): 1 or omitted lets Excel detect it; 0 turns seasonality off; a number such as 12 forces a 12-period cycle.
• data_completion (optional): 1 or omitted fills missing points with the average of neighbors; 0 treats them as zero.
• aggregation (optional): how to combine duplicate dates; the default is AVERAGE. Use 7 for SUM if you have several rows per date.

Example

With the history in A2:B34 and the next month in A35, this forecasts October 2026:

=FORECAST.ETS(A35, $B$2:$B$34, $A$2:$A$34, 1, 1)

Fill the dates down (for example, =EDATE(A35,1) in A36) and copy the formula to forecast several months ahead.

Companion functions

To build upper and lower bounds, compute the margin once and add or subtract it:

Margin:  =FORECAST.ETS.CONFINT(A35, $B$2:$B$34, $A$2:$A$34, 0.95, 1, 1)
Upper:   =C35 + D35
Lower:   =C35 - D35

The Forecast Sheet: forecasting in one click

If you prefer not to write formulas, the Forecast Sheet builds the whole model for you, using FORECAST.ETS behind the scenes.

1. Select your two columns (timeline and values), including headers.
2. Go to Data > Forecast Sheet.
3. Choose a line or column chart and set the Forecast End date.
4. Open Options to set the confidence interval, seasonality (automatic or manual), how to fill missing points, and how to aggregate duplicates.
5. Click Create. Excel adds a new sheet with a table of forecasts, lower and upper bounds, and a chart.

The output table contains real formulas, so you can inspect them, copy them into your own report, or point them at an updated range. The Forecast Sheet is available in desktop Excel for Windows and Mac; feature availability can vary by version, so check Microsoft's support page if you don't see the button.

Trendlines: quick visual forecasts on a chart

Trendlines are the fastest way to see a direction. Create a line or scatter chart, click the data series, then choose Add Chart Element > Trendline (or right-click the series and pick Add Trendline). In the Format Trendline pane you can:

• Pick a type: Linear, Exponential, Logarithmic, Polynomial, Power or Moving Average.
• Set Forecast Forward to a number of periods to extend the line.
• Tick Display Equation and Display R-squared to see how well the line fits.

Which trendline type to choose

R-squared ranges from 0 to 1; closer to 1 means the line explains more of the variation. A high R-squared on past data still doesn't prove the future will follow, and trendlines ignore seasonality entirely.

To put the linear trend into cells instead of a chart, use FORECAST.LINEAR or TREND:

=FORECAST.LINEAR(A35, $B$2:$B$34, $A$2:$A$34)
=TREND($B$2:$B$34, $A$2:$A$34, A35:A40)

In Excel 365, the TREND version spills six forecasts at once.

FORECAST.ETS vs trendlines: which one to use

A good habit is to test accuracy with a holdout: hide the last six months, forecast them using only the earlier data, and compare. A simple error measure is the mean absolute percentage error, calculated in a helper column as =ABS(actual-forecast)/actual and averaged.

Common mistakes to avoid

• #NUM! errors usually mean the timeline has an inconsistent step or duplicate dates without the aggregation argument set correctly.
• Forecasting raw transactions instead of totals per period. Summarize first.
• Too little history for seasonality to be detected, which produces a flat or misleading line.
• Extending polynomial trendlines far into the future, where they can curve wildly.
• Ignoring known events like a planned price increase. Add a manual adjustment column and document it.

If you outgrow Excel, Python offers more models and testing options; see our sales forecasting with Python project.

Next steps

Take a real dataset, such as 24 or more months of sales, and build both a Forecast Sheet and a linear trendline. Compare the two against a six-month holdout and keep the one with the lower error. Then add the forecast and its bounds to a chart in your interactive Excel dashboard so managers can see the expected range, not just a single number.

Watch: video lessons

▶

▶

Frequently asked questions