Excel TREND Function

last modified October 5, 2026

The TREND function returns values along a linear trend. It fits a straight line by the method of least squares to a set of known y-values and known x-values, then evaluates that line at the x-values you specify. This tutorial provides a comprehensive guide to using TREND with detailed examples. You'll learn the basic syntax, the regression model behind it, a worked example, how to force the line through the origin, how to run a multiple regression, and how TREND compares with LINEST, FORECAST, and GROWTH.

TREND Function Basics

The TREND function performs linear regression analysis to predict values along the best-fit line through known data. It is the linear counterpart of GROWTH: where GROWTH fits an exponential curve, TREND fits a straight line. It handles a single predictor or several predictors at once and returns one predicted value or an array of them.

Component	Description
Function Name	TREND
Syntax	=TREND(known_y's, [known_x's], [new_x's], [const])
Arguments	Required: known_y's Optional: known_x's, new_x's, const
Return Value	One predicted y-value, or an array of predicted y-values

This table breaks down the essential components of the TREND function. It shows the function name, the syntax format, the argument requirements, and the value it returns.

Argument	Meaning
known_y's	The dependent y-values you already know. This range comes first in the argument list.
known_x's	Optional. The independent x-values that pair with known_y's. If omitted, TREND uses the array {1,2,3,...} the same size as known_y's. With one variable the ranges may be any shape as long as they match; with several variables known_y's must be a single row or column.
new_x's	Optional. The x-values for which to return predicted y-values. If omitted, TREND evaluates the line at the known_x's themselves. The number of columns (or rows) must match known_x's.
const	Optional logical value. If TRUE or omitted, the intercept b is calculated normally. If FALSE, b is forced to 0 and the line becomes y = m*x.

The argument order is a common source of mistakes: the dependent y-range comes first and the independent x-range comes second, the opposite of many chart tools. If known_y's and known_x's are not the same size, TREND returns the #REF! error. If the x-values have no variance, it returns #DIV/0!.

The Linear Model Behind TREND

For one predictor, TREND fits the straight line y = intercept + slope * x. The slope is the covariance of the two ranges divided by the variance of the x-values, and the intercept places the line through the means of both ranges. The definitions below match Excel's calculation exactly.

slope     = COVARIANCE.P(known_ys, known_xs) / VAR.P(known_xs)
intercept = AVERAGE(known_ys) - slope * AVERAGE(known_xs)
TREND(known_ys, known_xs, new_xs) = intercept + slope * new_xs

Because the same number of points is used for the covariance and the variance, the sample and population versions of those functions give the same slope. Excel's SLOPE and INTERCEPT functions return those two values directly, so a single-variable TREND is equivalent to INTERCEPT(known_ys, known_xs) + SLOPE(known_ys, known_xs) * new_xs. With several predictors the model generalizes to y = m1*x1 + m2*x2 + ... + b, which is the same equation that LINEST reports.

Basic TREND Example

This example demonstrates the simplest use of TREND with a perfectly linear dataset. The known y-values increase by 2 for each step in x.

A (x)	B (y)
1	3
2	5
3	7
4	9
5	11

=TREND(B1:B5, A1:A5, 6)

This formula fits the line y = 2x + 1 to the five known points and evaluates it at x = 6, returning 13. It is identical to =INTERCEPT(B1:B5,A1:A5)+SLOPE(B1:B5,A1:A5)*6, a useful way to check the result by hand.

TREND with Multiple Predictions

TREND can return several predictions at once when new_x's holds several values. This example reuses the same pattern with a steeper line.

A (x)	B (y)	C (new_x)
1	100	6
2	150	7
3	200	8
4	250
5	300

=TREND(B1:B5, A1:A5, C1:C3)

The known data rise by 50 per step, so the fitted line is y = 50x + 50. The formula returns the array {350;400;450} for x = 6, 7, and 8. In Microsoft 365 and Excel 2021 this single formula spills down three cells; in Excel 2019 and earlier you must select those three cells first and confirm with Ctrl+Shift+Enter.

TREND Worked Example

Real data rarely fall on a perfect line. This example walks through the calculation with the x-values 1 through 5 and the y-values 1, 3, 2, 5, and 4.

A (x)	B (y)
1	1
2	3
3	2
4	5
5	4

=TREND(B1:B5, A1:A5, 6)

The mean of x is 3 and the mean of y is 3. The covariance is 1.6 and the variance of x is 2.0, so the slope is 1.6 / 2.0 = 0.8. The intercept is 3 - 0.8 * 3 = 0.6, giving the line y = 0.8x + 0.6. The prediction for x = 6 is 0.6 + 0.8 * 6 = 5.4. Evaluated at the known x-values, TREND returns the fitted values 1.4, 2.2, 3.0, 3.8, and 4.6, whose average is again 3.

Forcing the Intercept to Zero

The optional const argument forces the regression line through the origin. This is useful when a zero input must produce a zero output.

A (x)	B (y)
1	2
2	4
3	6
4	8
5	10

=TREND(B1:B5, A1:A5, 6, FALSE)

With const set to FALSE, the intercept is fixed at 0 and the slope is recomputed to fit y = m*x. The slope becomes SUM(x*y) / SUM(x²) = 110 / 55 = 2, so the prediction for x = 6 is 12. Here the known points already lie on y = 2x, so the ordinary fit has an intercept of 0 as well and gives the same answer. The argument changes the result only when the data do not pass through the origin, in which case it tilts the line so that it must.

TREND with Time Series Data

TREND works well when the x-values are time periods. This example forecasts monthly sales that grow by a steady 500 per month.

A (Month)	B (Sales)
1	12000
2	12500
3	13000
...	...
12	17500

=TREND(B1:B12, A1:A12, 13)

The sales rise by 500 each month from 12,000 in month 1 to 17,500 in month 12, so the fitted line is y = 500x + 11,500. The forecast for month 13 is 18,000. Because the x-values are regular numbers, the same formula can project several future months by passing an array such as {13;14;15} as new_x's.

TREND with Multiple Regression

TREND can fit more than one predictor. When it does, known_y's must be a single column or row and known_x's must supply one column for each predictor. This example uses two predictors and a response that follows y = 2*x1 + 3*x2 + 5.

A (x1)	B (x2)	C (y)	D (new x1)	E (new x2)
1	2	13	7	6
2	1	12	8	7
3	4	23
4	3	22
5	5	30

=TREND(C1:C5, A1:B5, D1:E2)

The response column C1:C5 is a vector, and the two predictors in A1:B5 form the known_x's matrix. The new_x's block D1:E2 holds the pairs (7,6) and (8,7). The fitted surface is y = 2*x1 + 3*x2 + 5, so the formula returns {37;42}. Keep the predictor columns free of duplicates or near-duplicates: if two x columns are collinear, their individual coefficients are no longer unique and the results become unreliable.

TREND vs LINEST vs FORECAST vs GROWTH

Several Excel functions expose the same least-squares machinery. FORECAST and FORECAST.LINEAR return one prediction from a simple line, LINEST returns the coefficients and optional statistics, and GROWTH fits an exponential curve instead of a straight line. TREND occupies the middle ground: it uses the same linear model as LINEST but returns predicted values like FORECAST, and it can handle several predictors.

Function	Model	Returns
TREND	Linear, y = m1*x1 + ... + b	One value or an array of predicted y-values
LINEST	Same linear model as TREND	The coefficients and intercept, plus statistics when requested
FORECAST / FORECAST.LINEAR	Linear with one predictor	A single predicted y-value for a given x
GROWTH	Exponential, y = b*m1^x1*...	One value or an array of predicted y-values

For the basic data above (x = 1 to 5 and y = 3, 5, 7, 9, 11), the linear functions agree.

=TREND(B1:B5, A1:A5, 6)              ' 13
=FORECAST(6, B1:B5, A1:A5)           ' 13
=INTERCEPT(B1:B5, A1:A5)             ' 1
=SLOPE(B1:B5, A1:A5)                 ' 2
=LINEST(B1:B5, A1:A5)                ' {2, 1}

TREND and LINEST report the same regression from different angles, just as GROWTH and LOGEST are counterparts for the exponential model. Use GROWTH when the values are multiplied by a steady factor and TREND when they change by a steady amount.

Array Entry and Spilling

When new_x's holds a single value, TREND returns a single value and a plain Enter is enough. When it holds several values, TREND returns an array with one prediction per value. In Microsoft 365 and Excel 2021 that array spills automatically into the neighbouring cells. In Excel 2019 and earlier you must select the exact output range first and confirm the formula with Ctrl+Shift+Enter, which wraps it in curly braces. If the spill range is blocked in a modern version, Excel returns #SPILL! until the obstructing cells are cleared.

=TREND(B1:B5, A1:A5, {6;7;8})

The array constant {6;7;8} lists the new x-values down a column, so this formula produces three predictions. Commas separate values in the same row and semicolons separate rows, matching the layout Excel expects.

TREND Errors and Troubleshooting

Cause	Symptom	Fix
known_y's and known_x's have a different number of data points	#REF! error	Make both ranges the same size and shape
All known_x's values are identical (zero variance)	#DIV/0! error	Include x-values that actually vary
A range contains text, logical values, or blanks	#VALUE! error or a wrong result	Use only numeric cells
new_x's has a different number of columns than known_x's	#REF! error or misaligned predictions	Give new_x's the same predictor layout as known_x's
Several predictors, but known_y's is not a single row or column	#REF! error	Put the dependent values in one column or one row
Only one x-y pair is supplied	#DIV/0! error	Supply at least two numeric pairs
Result fills only one cell in Excel 2019 or earlier	Only the first prediction appears	Select the full output range and press Ctrl+Shift+Enter
The spill range is blocked in Excel 365 or 2021	#SPILL! error	Clear the cells that block the spill range
A referenced cell already holds an error	That same error is returned	Fix the source error first

The TREND function turns a set of known x-y pairs into predicted values along a least-squares line. It fits a single line or a multiple regression, evaluates that fit at any x-values you supply, and returns the result as a single number or an array. Its power comes with two obligations: keep the y-range and the x-range the same size and shape, and make sure the predictors actually vary and are not collinear. Because TREND assumes a linear relationship, plot the data before you trust its forecasts, and remember that a projection is only as reliable as the trend it extends. Use LINEST when you need the coefficients or regression statistics, FORECAST for a single prediction, and GROWTH when the data compound instead of add. Used with that care, TREND is a dependable tool for forecasting in Excel.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Excel Formulas.