Excel’s TREND function returns values along a least-squares straight line fitted to existing data.
You can estimate values between known observations or extend the fitted relationship to new inputs. It can return several predictions from one formula.
The calculation can use multiple predictor columns, including transformed inputs for a polynomial fit. The chosen model still needs to suit the data.
In this article, I’ll show you how to forecast several periods, use multiple predictors, and fit a polynomial trend.
TREND Function Syntax in Excel
The TREND function uses the following syntax:
=TREND(known_y's,[known_x's],[new_x's],[const])
- known_y’s: Required. The existing results that TREND uses to fit the line.
- known_x’s: Optional. The existing input values paired with known_y’s. If omitted, Excel uses 1, 2, 3, and so on.
- new_x’s: Optional. The input values for which you want projected results. If omitted, Excel uses known_x’s.
- const: Optional. TRUE or omitted calculates the intercept normally. FALSE forces the intercept to zero.
TREND fits the known values with the least-squares method, then returns the projected y-values for the new x-values.
When to Use TREND Function
- Project several future values from a linear pattern with one formula.
- Forecast equally spaced observations without listing known x-values.
- Predict an outcome from two or more independent variables.
- Compare a normal fitted line with a line forced through zero.
- Fit a polynomial curve by adding columns for powers of x.
Example 1: Forecast Several Future Weeks
Here is a simple forecast that returns several results at once.
Below are completed service calls for weeks 1 through 12, plus four future week numbers.
I want to project completed service calls for weeks 13 through 16.
Enter this formula in cell E2:
=TREND(B2:B13,A2:A13,D2:D5)
The formula spills four projections: 110.97, 113.38, 115.78, and 118.19.
TREND fits a straight line through the known weeks and completed calls. It then calculates the corresponding points for the four future weeks.
Pro Tip: Keep E3:E5 empty so the results can spill. A blocked output range causes a #SPILL! error.
Example 2: Omit Known X Values
You can leave out known_x’s when the observations use consecutive, equally spaced positions.
Below are average handling times for six training sessions, followed by session numbers 7 through 9.
I want Excel to project handling time for the next three sessions.
Enter this formula in cell E2:
=TREND(B2:B7,,D2:D4)
The formula returns 49.87, 52.50, and 55.12 minutes.
The empty second argument tells TREND to treat the six known observations as x-values 1 through 6. The values in D2:D4 become the new x-values.
Pro Tip: Supply an explicit known_x’s range when observations are not equally spaced. Omitting it would assign consecutive positions and change the model.
Example 3: Use Multiple Predictor Columns
TREND can use more than one independent variable in the same model.
Below are shipping costs with their distance and shipment weight, plus three new distance-and-weight pairs.
I want to project shipping cost from both distance and weight.
Enter this formula in cell G2:
=TREND(C2:C9,A2:B9,E2:F4)
The projected shipping costs are $73.50, $111.00, and $163.00.
The known_x’s range has two columns, one for distance and one for weight. The new_x’s range follows the same two-column order.
Use LINEST when you need the fitted coefficients or model statistics. TREND is more direct when you only need the projected values.
Example 4: Force the Intercept to Zero
The optional const argument controls whether Excel calculates an intercept.
Below are assembled units and labor hours, with three future production levels.
I want to compare the normal trend with a model whose fitted line must pass through zero.
Enter the default TREND formula in cell E2:
=TREND(B2:B8,A2:A8,D2:D4)
The default projections are 160.00, 179.79, and 199.57 hours.
Enter the zero-intercept formula in cell F2:
=TREND(B2:B8,A2:A8,D2:D4,FALSE)
With const set to FALSE, the projections become 161.03, 181.16, and 201.29 hours.
Forcing the intercept to zero changes the fitted slope. Use FALSE only when a zero input should logically produce a zero output in your model.
Example 5: Fit a Polynomial Trend
TREND normally fits a straight line, but extra predictor columns can represent powers of the same x-value.
Below are support requests by quarter, including a second column containing each quarter number squared.
You can create that helper column with the POWER function or the ^2 operator.
I want to project support requests for quarters 9 through 11 using a second-degree curve.
Enter this formula in cell G2:
=TREND(C2:C9,A2:B9,E2:F4)
The formula returns 508.86, 583.29, and 665.12 requests.
Columns A and B provide x and x squared. TREND fits both predictors together, which lets the projections follow a curved pattern.
FORECAST.ETS is designed for seasonal time series, while GROWTH fits an exponential trend. Neither is a direct replacement for this polynomial model.
Tips & Common Mistakes
- Keep each spill range clear. In Excel 2021 and later, enter the formula in the top-left output cell and press Enter.
- In Excel 2019 and earlier, select the complete output range and confirm an array formula with Ctrl+Shift+Enter.
- Match the number and order of predictor columns in known_x’s and new_x’s.
- Remember that TREND fits a straight line unless you deliberately add columns for squared, cubed, or higher-power x-values.
- Use FORECAST.LINEAR for one linear prediction. Use LINEST when you need slopes, intercepts, or regression statistics.
- Do not treat TREND as a seasonal forecasting function. Use a model built for seasonality when that pattern matters.
I covered spilled forecasts, omitted x-values, multiple predictors, the const argument, and a polynomial pattern.
I hope you found this article helpful.
Other Excel articles you may also like:
