Forecast a Value with a Trend Line

Excel Formulas › Statistics

All versionsFORECASTTREND

To project a future value from a trend — next month’s sales, the temperature at an unmeasured point — FORECAST fits a straight line through your data and reads off the prediction.

Quick formula: to predict Y at a new X, given known Y and X columns:
=FORECAST(newX, known_Ys, known_Xs)
FORECAST fits a least-squares line to the known points and returns the Y value on that line at newX.

Functions used (tap for the full reference guide):

FORECASTTRENDSLOPE

The example

Sales by month (1–4). Forecast month 5.

AB
1MonthSales
21$120
32$150
43$190
54$210
6Forecast mo 5:$246

The formula

Predicted sales for month 5:

=FORECAST(5, B2:B5, A2:A5) // trend line at month 5 ≈ $246

How it works

FORECAST is linear regression in one function:

  1. It fits the best straight line (least-squares) through the known X/Y pairs — the same line a scatter-chart trendline draws.
  2. It then returns the Y value on that line at your new X (5) — the projection.
  3. The data climbs ~$30/month, so month 5 lands around $246.
  4. For several future points at once, TREND spills predictions for a whole range of new Xs.

Forecasts assume the trend holds. FORECAST extends a straight line — great for steady trends, misleading for seasonal or curved data. For seasonality, use FORECAST.ETS (Excel 2016+) or a proper model.

Try it: interactive demo

Live demo

Pick a future month; see the trend-line forecast.

Forecast:

Variations

Forecast several points (TREND)

Spill predictions for a range of new Xs:

=TREND(B2:B5, A2:A5, E2:E6)

Modern name

FORECAST.LINEAR is the current alias:

=FORECAST.LINEAR(5, B2:B5, A2:A5)

Seasonal forecast

For data with seasonality (Excel 2016+):

=FORECAST.ETS(newDate, values, dates)

Pitfalls & errors

Argument order: newX, known_Ys, known_Xs. It’s easy to swap the Y and X ranges — the Y (what you predict) comes before the X.

Straight line only. FORECAST can’t see curves or seasonality. Plot the data first; if it’s not roughly linear, the forecast will mislead.

Extrapolation risk. Predicting far beyond your data is shaky — the trend may not continue.

Practice workbook

📊
Download the free Forecast a Value with a Trend Line practice workbook
Monthly sales with live FORECAST, the TREND multi-point and SLOPE/INTERCEPT variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I forecast a value in Excel?
Use =FORECAST(newX, known_Ys, known_Xs). It fits a straight trend line through your data and returns the predicted Y at the new X. FORECAST.LINEAR is the modern name.
How do I forecast several future points at once?
Use TREND: =TREND(known_Ys, known_Xs, new_Xs) returns predictions for a whole range of new X values.
What if my data is seasonal?
FORECAST assumes a straight line. For seasonal data use FORECAST.ETS (Excel 2016+), which models trend and seasonality.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Correlation between two columns · Moving average · Percent change

Function references: FORECAST · TREND · SLOPE