Excel FORECAST Function Explained Step by Step

Have you ever looked at months of historical sales data and needed to predict what next month or next quarter might look like?

In most cases, you might try to eyeball a trend, draw a trendline on a chart, or manually extend a pattern based on gut feel. Or you might build a complicated model in a separate tool, export the results, and paste them back into Excel.

This is imprecise, time-consuming, and difficult to update when new data comes in.

Excel’s FORECAST functions solve this instantly. You provide your historical data, the known values and the known time periods and Excel uses statistical modelling to calculate predicted values for any future period you specify. The formula updates automatically whenever your historical data changes.

In this guide, we will break down Excel’s FORECAST functions completely: what they are, which version to use, how to write them step by step, real-world examples, and when to use FORECAST versus more advanced forecasting tools.

What Is the FORECAST Function in Excel?

The FORECAST function predicts a future value based on existing data by fitting a statistical model to your historical values and extrapolating along that model to the point you specify.

It answers the question: “Given what has happened so far, what is the most statistically likely value at this future point?”

Simple Analogy

Imagine you have been tracking your monthly electricity bill for two years. You notice it tends to rise slightly each month as you add more devices, with a predictable spike every summer. The FORECAST function is like having a statistician look at all those historical bills and tell you: “Based on this pattern, your bill in August next year will probably be around $187.”

Excel does exactly this for any numeric series — sales, revenue, website traffic, temperatures, or any data that follows a trend or repeating pattern.

The FORECAST Function Family

Excel has three main FORECAST functions and each is suited to different data patterns:

Function	Best For	Method
FORECAST.LINEAR	Data with a straight-line trend	Linear regression
FORECAST.ETS	Data with trend plus seasonal patterns	Exponential smoothing
FORECAST	Legacy version of FORECAST.LINEAR	Linear regression

Which should you use? Use FORECAST.LINEAR for data with a consistent upward or downward trend and no repeating seasonal pattern. Use FORECAST.ETS when your data has seasonality and predictable cycles that repeat at regular intervals (monthly, quarterly, annually). The old FORECAST function is identical to FORECAST.LINEAR and exists only for backward compatibility with older workbooks.

Where to Find FORECAST in Excel

FORECAST functions are standard Excel functions — no add-ins required.

Type directly into any cell:

=FORECAST.LINEAR(
=FORECAST.ETS(
=FORECAST(

Excel also offers a one-click Forecast Sheet tool that builds a full forecast automatically with a chart: Data tab → Forecast group → Forecast Sheet

Step-by-Step: Your First FORECAST.LINEAR Formula

Example 1: Predicting Next Month’s Sales

Setup — monthly sales data:

A	B
Month	Sales
1	12000
2	13500
3	14200
4	15800
5	16100
6	17400

You want to predict sales for Month 7, Month 8, and Month 9.

Step 1: Understand the syntax

=FORECAST.LINEAR(x, known_y's, known_x's)

• x — The future point you want to predict (Month 7, 8, or 9)
• known_y's — Your historical values (the Sales column)
• known_x's — Your historical time periods (the Month column)

Step 2: Write the formula for Month 7

Click on cell B9 and enter:

excel

=FORECAST.LINEAR(7, B2:B7, A2:A7)

• 7 — predict the value at Month 7
• B2:B7 — the known sales values (months 1–6)
• A2:A7 — the known month numbers (1–6)

Step 3: Check the result

Excel returns approximately 18,457 which is the predicted sales for Month 7 based on the linear trend in your historical data.

Step 4: Extend to Month 8 and Month 9

excel

=FORECAST.LINEAR(8, B2:B7, A2:A7)   → approximately 19,514
=FORECAST.LINEAR(9, B2:B7, A2:A7)   → approximately 20,571

Step 5: Lock the ranges for easy copying

If you want to drag the formula down a column with the forecast period in column A:

excel

=FORECAST.LINEAR(A9, $B$2:$B$7, $A$2:$A$7)

The $ signs lock the known data ranges so they do not shift as you copy the formula down and only the x value (A9) changes with each row.

Best practice: Always lock the known_y and known_x ranges with absolute references when you plan to copy the formula to multiple forecast periods.

How FORECAST.LINEAR Works Internally

FORECAST.LINEAR fits a linear regression line through your historical data — the straight line that minimises the total squared distance between the line and all your actual data points. This line is defined by two values:

• Slope (m) — How much the predicted value increases per unit increase in x
• Intercept (b) — The predicted value when x equals zero

The formula Excel uses internally is:

Predicted value = slope × x + intercept

You can verify this yourself using Excel’s SLOPE() and INTERCEPT() functions on your data:

excel

=SLOPE(B2:B7, A2:A7)      → approximately 1,057
=INTERCEPT(B2:B7, A2:A7)  → approximately 11,400

Prediction for Month 7: (1,057 × 7) + 11,400 = 18,799

The small difference from the FORECAST result is due to rounding in this illustration, the underlying calculation is identical.

How FORECAST.ETS Works Internally

FORECAST.ETS uses Exponential Triple Smoothing (ETS). This is a more sophisticated algorithm that models three components of your data separately:

• Level — The baseline value at each point
• Trend — The direction and speed the baseline is moving
• Seasonality — The repeating cyclical pattern layered on top

ETS gives more weight to recent data points than older ones unlike linear regression which treats all historical points equally. This makes it more responsive to recent shifts in your data’s behaviour.

Real-World Examples

Example 2: FORECAST.ETS for Seasonal Sales Data

Goal: Predict Q1 sales for a retail business with strong seasonal patterns (peak in Q4, slow in Q1–Q2).

Setup — quarterly sales over three years:

A	B
Quarter	Sales
1 (Q1 2022)	45000
2 (Q2 2022)	62000
3 (Q3 2022)	78000
4 (Q4 2022)	112000
5 (Q1 2023)	48000
6 (Q2 2023)	67000
7 (Q3 2023)	84000
8 (Q4 2023)	121000
9 (Q1 2024)	52000
10 (Q2 2024)	71000
11 (Q3 2024)	89000
12 (Q4 2024)	128000

Predict Q1 2025 (period 13):

excel

=FORECAST.ETS(13, B2:B13, A2:A13, 4)

Syntax:

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

• 13 — the future period to predict
• B2:B13 — historical sales values
• A2:A13 — the timeline (period numbers 1–12)
• 4 — seasonality length (4 quarters per year)

Result: Approximately 55,200 which correctly reflects the low Q1 pattern while accounting for the overall upward trend across all three years.

Using FORECAST.LINEAR on this same data would give approximately 102,000 — ignoring the seasonal dip entirely and dramatically overpredicting Q1. This illustrates exactly when FORECAST.ETS is essential.

Example 3: Forecasting Website Traffic

Goal: Predict monthly website visitors for the next three months based on 12 months of historical data.

excel

=FORECAST.LINEAR(13, $B$2:$B$13, $A$2:$A$13)  → Month 13 prediction
=FORECAST.LINEAR(14, $B$2:$B$13, $A$2:$A$13)  → Month 14 prediction
=FORECAST.LINEAR(15, $B$2:$B$13, $A$2:$A$13)  → Month 15 prediction

To build a clean forecast table, put future period numbers in column A (13, 14, 15) and enter the formula once in B13 with locked ranges, then copy down:

excel

=FORECAST.LINEAR(A13, $B$2:$B$12, $A$2:$A$12)

Drag this formula to B14 and B15 — the period reference updates automatically while the historical data ranges stay fixed.

Example 4: Using the Forecast Sheet Tool (No Formula Required)

Goal: Generate a complete forecast with confidence intervals and a chart in one click which is ideal for presenting to stakeholders.

Step 1: Select your historical data including headers (both the time column and the values column).

Step 2: Data tab → Forecast group → Forecast Sheet

Step 3: Configure the Forecast Sheet dialog:

• Forecast End — Set the last period you want to predict
• Forecast Start — Where the forecast begins (Excel auto-detects based on your data)
• Confidence Interval — Default 95% — shows upper and lower bounds around the forecast
• Seasonality — Auto-Detect (Excel calculates) or set manually
• Include forecast statistics — Adds a summary table with MASE, SMAPE, MAE, and RMSE accuracy metrics

Step 4: Click Create

Excel generates a new worksheet containing your historical data, forecast values, upper confidence bound, lower confidence bound, and a line chart showing all four series together.

Best practice: Use Forecast Sheet for stakeholder presentations. The confidence interval bands visually communicate that the forecast is a range of likely outcomes, not a guaranteed number. This sets appropriate expectations and makes your analysis look more statistically rigorous.

Example 5: Confidence Intervals With FORECAST.ETS.CONFINT

Goal: Calculate upper and lower bounds around your ETS forecast to communicate uncertainty.

excel

-- Central forecast for period 13
=FORECAST.ETS(13, B2:B13, A2:A13, 4)

-- 95% confidence interval width
=FORECAST.ETS.CONFINT(13, B2:B13, A2:A13, 0.95, 4)

FORECAST.ETS.CONFINT returns the margin of error which is the amount to add and subtract from the central forecast to build the confidence interval:

excel

Upper bound = FORECAST.ETS(...) + FORECAST.ETS.CONFINT(...)
Lower bound = FORECAST.ETS(...) - FORECAST.ETS.CONFINT(...)

A 95% confidence interval means: if the model’s assumptions hold, the actual value will fall within this range 95% of the time. Wider intervals indicate more uncertainty which typically increases the further you forecast into the future.

Example 6: Measuring Forecast Accuracy With FORECAST.ETS.STAT

Goal: Evaluate how well the ETS model fits your historical data before using it to predict the future.

excel

=FORECAST.ETS.STAT(B2:B13, A2:A13, 1, 4)

Syntax:

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

The statistic_type argument controls which metric is returned:

statistic_type	Metric	What It Means
1	Alpha (smoothing value)	Weight given to level — higher = more weight to recent data
2	Beta (smoothing value)	Weight given to trend
3	Gamma (smoothing value)	Weight given to seasonality
4	MASE	Mean Absolute Scaled Error — below 1 means better than naïve forecast
5	SMAPE	Symmetric Mean Absolute Percentage Error — lower is better
6	MAE	Mean Absolute Error — average size of forecast error in original units
7	RMSE	Root Mean Squared Error — penalises large errors more heavily than MAE

Run all seven statistics on your data before presenting a forecast to understand how reliable the model is on historical data before it faces future data it has never seen.

Building a Complete Forecast Model in Excel

A production-ready forecast model combines all the components covered so far into a structured layout:

Column A: Period (1, 2, 3 ... 12 historical + 13, 14, 15 future)
Column B: Actual Sales (historical values, blank for future periods)
Column C: Forecast =FORECAST.ETS(A2, $B$2:$B$13, $A$2:$A$13, 4)
Column D: Lower Bound =C2 - FORECAST.ETS.CONFINT(A2, $B$2:$B$13, $A$2:$A$13, 0.95, 4)
Column E: Upper Bound =C2 + FORECAST.ETS.CONFINT(A2, $B$2:$B$13, $A$2:$A$13, 0.95, 4)

Plot columns B, C, D, and E on a line chart. Format D and E as dashed lines. This gives you a professional forecast chart showing actual historical performance, the model’s central prediction, and the uncertainty band around it.

FORECAST.LINEAR vs FORECAST.ETS — When to Use Each

Both functions predict future values from historical data but they make different assumptions about how your data behaves.

FORECAST.LINEAR:

• Assumes a straight-line relationship between time and value
• Treats all historical data points equally
• Cannot model repeating seasonal patterns
• Fast and simple — two data columns, one formula
• Best for: steadily growing or declining metrics with no seasonal cycle — annual revenue growth, gradual user acquisition, steady cost reduction

FORECAST.ETS:

• Models trend and seasonality separately
• Gives more weight to recent data points
• Handles repeating cycles at any interval
• Requires more historical data to detect patterns reliably (at least two full seasonal cycles)
• Best for: any data with predictable repeating patterns — monthly retail sales, quarterly earnings, weekly website traffic, annual temperature data

Feature	FORECAST.LINEAR	FORECAST.ETS
Trend modelling	Linear only	Linear + curved
Seasonality	No	Yes
Recent data weighting	Equal weight	More weight to recent
Data required	As few as 2 points	2+ seasonal cycles recommended
Confidence intervals	Not built-in	FORECAST.ETS.CONFINT
Complexity	Very simple	Moderate

Use FORECAST.LINEAR when: Your data follows a consistent straight-line trend with no seasonal pattern. Use FORECAST.ETS when: Your data has repeating highs and lows at predictable intervals.

Common Limitations of Excel FORECAST Functions

Only One Variable

Both FORECAST functions predict based on time (x) only. They cannot incorporate multiple explanatory variables like predicting sales based on both time and advertising spend simultaneously. For multi-variable forecasting, use Excel’s LINEST function, regression analysis in the Data Analysis ToolPak, or a dedicated statistical tool.

Assumes the Past Predicts the Future

FORECAST functions extend historical patterns forward. They have no awareness of external events — a new competitor entering the market, a product launch, a supply chain disruption, or a pandemic. Any structural break in your data will cause the model to produce misleading forecasts. Always sense-check predictions against real-world context.

Requires Consistent Time Intervals

FORECAST.ETS expects evenly spaced time periods. If your data has missing months, irregular gaps, or mixed frequencies, the seasonality detection and smoothing calculations can produce unreliable results. Clean your timeline to consistent intervals before forecasting and use the data_completion argument in FORECAST.ETS to handle occasional missing values automatically.

Forecast Accuracy Degrades Over Distance

The further into the future you forecast, the less reliable the prediction. Confidence intervals widen progressively with each additional period. A one-month forecast from 24 months of data is far more reliable than a twelve-month forecast from the same data. Always communicate the forecast horizon alongside the numbers.

Seasonality Detection Needs Sufficient History

FORECAST.ETS with auto-detect seasonality needs at least two full seasonal cycles to reliably identify the pattern. If you have monthly data with annual seasonality, you need at least 24 months of history. Less than two cycles and the seasonality detection may misidentify the pattern length.

Common Mistakes to Avoid

Using FORECAST.LINEAR on seasonal data — If your data has strong seasonal patterns and you use FORECAST.LINEAR, your predictions will ignore those cycles entirely. A retail business using FORECAST.LINEAR will dramatically underpredict Q4 and overpredict Q1. Always plot your data first and look for repeating patterns before choosing the function.

Not locking ranges with $ when copying formulas — If you copy =FORECAST.LINEAR(A9, B2:B8, A2:A8) down without absolute references, the historical data range shifts with each row, producing incorrect results from row to row. Always use $B$2:$B$8 and $A$2:$A$8 for the known data ranges.

Entering the wrong seasonality value — The seasonality argument in FORECAST.ETS is the number of periods in one cycle — not the number of cycles in your data. Monthly data with annual seasonality = 12. Quarterly data with annual seasonality = 4. Weekly data with annual seasonality = 52. Entering the wrong value produces a model that fits the wrong cycle length.

Forecasting too far into the future — Extending a forecast 24 months ahead from 12 months of history produces very wide confidence intervals and highly speculative point estimates. As a general rule, do not forecast further ahead than one third of your historical data length without clearly communicating the high uncertainty involved.

Treating the forecast as a guarantee — FORECAST outputs are statistical estimates with inherent uncertainty. Always present forecasts alongside confidence intervals or at minimum a stated margin of error. A single-point forecast without context implies false precision.

Ignoring the accuracy statistics — Running FORECAST.ETS.STAT takes 30 seconds and tells you whether the model fits your historical data well. An MASE above 1 means the model is performing worse than a simple naïve forecast — a strong signal to reconsider whether FORECAST.ETS is the right approach for your data.

FORECAST Function Cheat Sheet

Task	Formula
Predict future value (linear trend)	=FORECAST.LINEAR(future_x, known_y, known_x)
Predict future value (with seasonality)	=FORECAST.ETS(target, values, timeline, seasonality)
95% confidence interval margin	=FORECAST.ETS.CONFINT(target, values, timeline, 0.95, seasonality)
Upper confidence bound	=FORECAST.ETS(...) + FORECAST.ETS.CONFINT(...)
Lower confidence bound	=FORECAST.ETS(...) - FORECAST.ETS.CONFINT(...)
Check model accuracy (MASE)	=FORECAST.ETS.STAT(values, timeline, 4, seasonality)
Check model accuracy (RMSE)	=FORECAST.ETS.STAT(values, timeline, 7, seasonality)
Auto-build forecast with chart	Data tab → Forecast Sheet
Monthly seasonality (annual cycle)	seasonality = 12
Quarterly seasonality (annual cycle)	seasonality = 4
Weekly seasonality (annual cycle)	seasonality = 52

Excel’s FORECAST functions transform historical data into forward-looking predictions using rigorous statistical methods in a formula that takes seconds to write and updates automatically whenever your data changes.

Here is the simplest summary of everything we covered:

• FORECAST.LINEAR predicts future values using a straight-line trend fitted to your historical data
• FORECAST.ETS adds seasonality modelling using exponential smoothing — essential for data with repeating cycles
• The old FORECAST function is identical to FORECAST.LINEAR — use the newer version in new workbooks
• All three functions require x (future period), known_y (historical values), and known_x (historical periods)
• Use absolute references ($) when copying forecast formulas across multiple rows
• Use FORECAST.ETS.CONFINT to build confidence interval bounds around ETS predictions
• Use FORECAST.ETS.STAT to measure model accuracy before presenting results
• Use the Forecast Sheet tool for one-click forecast charts with confidence bands for stakeholder presentations
• Always plot your data first to determine whether it has a seasonal pattern before choosing between LINEAR and ETS

Start with a simple monthly sales series and FORECAST.LINEAR for the next three months. Once that is comfortable, switch to data with a seasonal pattern and compare LINEAR versus ETS predictions side by side. The difference will immediately show you why choosing the right function matters and from that point, statistical forecasting in Excel becomes a natural part of your analytical workflow.

FAQs