The Excel TREND function fits a straight line to your known data with least squares and returns the predicted y-values along that line — for the existing x's or for new ones you supply.
Syntax
| Argument | Description | |
|---|---|---|
known_ys | Required | The known dependent (y) values. |
known_xs | Optional | The known independent (x) values. If omitted, Excel uses {1,2,3,…}. |
new_xs | Optional | The x-values for which you want predicted y's. Defaults to known_xs (the fitted values). |
const | Optional | TRUE (or omitted) fits a normal intercept; FALSE forces the line through the origin. |
How to use it
TREND returns an array of fitted values, so it spills down a column (or you confirm it with Ctrl+Shift+Enter in older Excel). It is the multi-point cousin of FORECAST: feed it several new_xs and it returns a predicted y for each.
With no new_xs, TREND returns the fitted y for every original x — the points on the regression line. Setting const to FALSE forces the line through the origin (intercept = 0).
| A (x) | B (y) | |
|---|---|---|
| 2 | 1 | 2 |
| 3 | 2 | 4 |
| 4 | 3 | 5 |
| 5 | 4 | 8 |
| 6 | 5 | 9 |
| 7 | 6 | =TREND(B2:B6,A2:A6,A7) → 11 |
TREND vs FORECAST: FORECAST predicts a single y for one x; TREND predicts a whole array of y's at once. Both use the same least-squares line, so a single TREND value matches the FORECAST result.
Try it: interactive demo
Pick a TREND example to see the formula and its result.
Practice workbook
Frequently asked questions
Why does TREND spill into several cells?
What happens if I omit new_xs?
How is TREND different from FORECAST?
What does the const argument do?
TRUE (the default) lets Excel calculate the intercept normally. FALSE forces the regression line through the origin, so the predicted y is 0 when x is 0.Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class