Written and maintained by CASRAI Editorial Board
Last updated
The TREND formula in Excel fits a straight line through your known data by least squares and returns the y-values that line gives for the x-values you ask about. Its syntax is =TREND(known_y's, [known_x's], [new_x's], [const]). Give it your observed y and x values plus the new x-values you want predicted, and it returns one predicted y for each. This guide works one five-point dataset through TREND by hand so you can check your own result, and shows when FORECAST.LINEAR or LINEST is the better choice.
What the TREND function is used for in research
TREND is the worksheet function for predicting values along a fitted straight line. In research work it appears wherever a simple linear relationship is summarised and read forward or back: a calibration line used to estimate values for new readings, a time trend in monthly counts, or a quick predictor-outcome fit before moving to a statistics package. Microsoft documents it as fitting a line by the method of least squares, the same fit behind LINEST. If you only need the slope itself, see how to find a slope in Excel; the predicted values TREND returns are what the y-hat notation stands for.
Which function do you need: TREND, FORECAST.LINEAR or LINEST?
All three use the same least-squares line. They differ in what they hand back, which decides the choice.
| Function | Syntax | What it returns | Use it when |
|---|---|---|---|
| TREND | =TREND(known_y's, [known_x's], [new_x's], [const]) |
An array of predicted y-values, one per new x (or per known x if new_x’s is omitted) | You want many predictions at once, or your model has more than one x variable. |
| FORECAST.LINEAR | =FORECAST.LINEAR(x, known_y's, known_x's) |
One predicted value, a + bx, for a single x | You want one prediction in one cell. Note that x comes first and the arguments are in a different order from TREND. |
| LINEST | =LINEST(known_y's, [known_x's], ...) |
The fitted line’s coefficients rather than predictions | You want the slope and intercept themselves. See Excel statistics functions for how LINEST sits among the others. |
Microsoft states that FORECAST.LINEAR and the older FORECAST have the same syntax and usage, and that the older FORECAST function “will eventually be deprecated”.
Where TREND is available
TREND is a built-in worksheet function, so nothing needs to be installed. Microsoft’s function page lists it for Excel for Microsoft 365 (Windows and Mac), Excel 2024 (Windows and Mac), Excel 2021 (Windows and Mac), Excel 2019 and Excel 2016. The documentation is on Microsoft Support (see Sources).
How to use the TREND formula in Excel, step by step
The data below is the five-point set Microsoft uses on its INTERCEPT page (x: 6, 5, 11, 7, 5; y: 2, 3, 9, 1, 8), so the intercept can be checked against the documentation. The goal is to predict y for x = 8, 9 and 10.
- Enter the known x values in A2:A6 (6, 5, 11, 7, 5) and the known y values in B2:B6 (2, 3, 9, 1, 8). Both ranges must have the same number of points.
- Enter the new x values in A9:A11 (8, 9, 10). TREND needs one column of new_x’s for each x variable in known_x’s, and here there is one.
- Select cell B9 and type
=TREND(B2:B6,A2:A6,A9:A11). The order is known_y’s first, then known_x’s, then new_x’s. - Press Enter. In current Microsoft 365 the three results fill B9:B11 on their own. In older versions, select B9:B11 first, type the formula in B9 and press Ctrl+Shift+Enter; Excel adds curly brackets around the formula.
Output and how to read it
TREND returns the points on the fitted line, not the original data. Checking the dataset by hand: the mean of x is 6.8 and the mean of y is 4.6, the sum of (x minus mean x)(y minus mean y) is 16.6 and the sum of (x minus mean x) squared is 24.8. The slope is 16.6 / 24.8 = 0.669355 and the intercept is 4.6 minus 0.669355 times 6.8 = 0.048387 (Microsoft’s INTERCEPT page gives approximately 0.0483871 for this data). The fitted line is y = 0.669355x + 0.048387, so TREND returns:
| new x | TREND result (rounded) | Hand check: 0.669355x + 0.048387 |
|---|---|---|
| 8 | 5.4032 | 5.4032 |
| 9 | 6.0726 | 6.0726 |
| 10 | 6.7419 | 6.7419 |
Each step of one in x adds the slope, 0.6694, to the prediction. If you omit new_x’s, TREND returns the fitted value for every known x instead (here 4.0645, 3.3952, 7.4113, 4.7339 and 3.3952), which is handy for plotting the line over the points or computing residuals as observed minus fitted.
A prediction is only as trustworthy as the line behind it. The example data span x = 5 to 11, so the predictions at 8, 9 and 10 are interpolations inside the observed range; a prediction at x = 20 would be an extrapolation beyond anything the data covered. Before relying on a fitted line, look at the scatter plot and the assumptions of simple regression; TREND will return a line for any numbers, whether or not a line suits them.
What the optional arguments do
If known_x’s is omitted
Excel assumes the array {1,2,3,…} with the same size as known_y’s. That makes TREND a time trend for evenly spaced observations: =TREND(B2:B13) fits the line against period numbers 1 to 12.
If new_x’s is omitted
Excel uses known_x’s, so the output is the fitted value at each observed x. If both known_x’s and new_x’s are omitted, it uses {1,2,3,…}.
What does const = FALSE do?
With const TRUE or omitted, the constant b in y = mx + b is calculated normally. With FALSE, b is forced to 0 and the m-values are adjusted so that y = mx, a line through the origin. On the example data, =TREND(B2:B6,A2:A6,A9:A11,FALSE) uses slope = sum(xy) / sum(x squared) = 173 / 256 = 0.675781 and returns 5.4063, 6.0820 and 6.7578, slightly different from the normal fit. Use FALSE only when the relationship must pass through zero by design, not to tidy up an inconvenient intercept.
Multiple x variables
known_x’s can hold more than one variable. In that case known_y’s must be a vector (a single column or single row), each column of known_x’s is a separate variable when the y values are in a column, and new_x’s needs a matching column for each variable.
Can TREND fit a curve?
Not directly: TREND fits straight lines. Microsoft notes you can do polynomial curve fitting by regressing against the same variable raised to different powers, that is, by adding columns of x squared, x cubed and so on to known_x’s.
Troubleshooting
The result shows #SPILL!
In current Microsoft 365, a formula that returns several values spills into neighbouring cells. Microsoft’s explanation is that array formulas “can’t be input if there is anything blocking the output range”. Clear or move whatever sits in the cells below or beside the formula and the result fills in.
Only one value appears, or the formula has curly brackets
In versions before dynamic arrays, enter TREND by selecting the whole output range, typing the formula in its top-left cell and pressing Ctrl+Shift+Enter. Excel then shows the formula in curly brackets. In legacy array formulas the result has a fixed size, so the selected range must match the number of new x-values. Microsoft says these legacy array formulas are still supported for backward compatibility but should no longer be used where dynamic arrays are available.
Editing one cell of the result
Only the top-left cell of a spilled result holds the formula and can be edited; to change it, edit that cell.
The ranges do not line up
With a single x variable, known_x’s can be any shape as long as its dimensions match known_y’s. With several variables, known_y’s must be a vector and new_x’s needs one column (or row) per variable. If a result looks wrong, check the layout against these rules first.
The numbers differ from FORECAST.LINEAR
For one x variable they should agree, because both use the least-squares line. If they do not, check the argument order: FORECAST.LINEAR takes x first, then known_y’s, then known_x’s. FORECAST.LINEAR returns #N/A if known_y’s or known_x’s are empty or have different numbers of points, and #DIV/0! if the variance of known_x’s is zero, that is, all x values are identical.
Free alternatives outside Excel
| Tool | Function | Documented syntax |
|---|---|---|
| Google Sheets | TREND | TREND(known_data_y, [known_data_x], [new_data_x], [b]). Google describes it as fitting an ideal linear trend by least squares. Omitting known_data_x defaults to {1,2,3,…}, and b = FALSE forces the line through the origin. See also how to add a trendline in Google Sheets. |
| LibreOffice Calc | FORECAST | FORECAST(Value; DataY; DataX), which LibreOffice’s help describes as extrapolating future values from existing x and y values; Value is the x for which the y on the linear regression is returned. |
The Google Sheets signature mirrors Excel’s, so a TREND formula usually transfers with only the argument names changed. For a different forecasting approach, see Monte Carlo forecasting in Excel.
Frequently asked questions
What is the difference between TREND and FORECAST.LINEAR?
TREND returns an array of predicted y-values for many new x-values and accepts several x variables; FORECAST.LINEAR returns a single prediction for one x. Their argument order also differs.
What is the difference between TREND and a chart trendline?
TREND puts the fitted or predicted values in cells that you can use in further calculations. A chart trendline draws the best-fit line over the plotted points. Both describe a fitted line; the formula gives you the numbers.
Do I need Ctrl+Shift+Enter?
Not in current Microsoft 365: enter the formula in the top-left cell of the output range and press Enter. In older versions, select the full output range and press Ctrl+Shift+Enter.
Can TREND take more than one x variable?
Yes. Put each variable in its own column of known_x’s, keep known_y’s as a single column, and give new_x’s the same number of columns.
Sources
- Microsoft Support, TREND function, checked 2026-10-29.
- Microsoft Support, FORECAST and FORECAST.LINEAR functions, checked 2026-10-29.
- Microsoft Support, Dynamic array formulas and spilled array behavior, checked 2026-10-29.
- Microsoft Support, INTERCEPT function, checked 2026-10-29.
- Google Docs Editors Help, TREND, checked 2026-10-29.
- LibreOffice Help, Statistical Functions Part Five, checked 2026-10-29.
