TREND Function
Returns values along a straight-line trend, for one or many new x values.
Category
Statistical
Available in
All versions; spills in Microsoft 365 and Excel 2021+ (older versions: select the output cells and press Ctrl+Shift+Enter)
Returns
A number or an array
Overview
TREND fits the same least-squares line as LINEST and returns its y values. Give it new x values to predict, or leave them out to get the fitted value for every known x.
Because it accepts a whole range of new x values and spills the answers, it's the quickest way to extend a straight-line projection. It also works with several x columns.
Syntax
Arguments
| Argument | Required? | What it does |
|---|---|---|
| known_y's | Yes | The y values. |
| known_x's | No | The x values. If left out, 1, 2, 3 … is used. |
| new_x's | No | The x values to predict for. If left out, the known x's are used. |
| const | No | TRUE or omitted calculates the intercept; FALSE forces it to 0. |
Examples
Example 1 — Predict two new spend levels
New spend levels in E2:E3; one formula in F2 spills both predictions.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Ad spend ($k) | Sales ($k) | New spend ($k) | Predicted sales | |
| 2 | Jan | 2 | 21 | 9 | 49.18 | |
| 3 | Feb | 3 | 25 | 10 | 53.29 | |
| 4 | Mar | 5 | 33 | |||
| 5 | Apr | 4 | 28 | |||
| 6 | May | 6 | 38 | |||
| 7 | Jun | 7 | 40 | |||
| 8 | Jul | 8 | 46 | |||
| 9 | Aug | 5 | 31 |
The same answers as FORECAST.LINEAR for 9 and 10, without copying a formula.
Example 2 — The fitted line for every month
Leave new_x's out to get the line's value at each known x — handy for charting the trend or calculating residuals.
Spills 20.43, 24.54, 32.75 … next to the actual sales.
Common mistakes
- #SPILL! Cells below the formula aren't empty.
- #VALUE! A cell in the known ranges is empty or holds text.
- Straight lines only. For data that grows by a percentage, use GROWTH.
Notes
- Related functions: FORECAST.LINEAR, LINEST, GROWTH, SLOPE.

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.