In Excel: use =TREND(known_ys, known_xs, new_xs) — it returns a whole array of linear predictions at once, spilling down the sheet in modern Excel.
On this page8
Syntax
Arguments
| Argument | required / optional | Description |
|---|---|---|
known_ys | required | The observed outcomes. |
known_xs | optional | The observed inputs. Defaults to 1, 2, 3 … if omitted. |
new_xs | optional | The points to predict. Defaults to known_xs. |
const | optional | FALSE forces the line through zero; TRUE or omitted fits the intercept normally. |
Related functions
Select a cell with empty space beneath it so the array can spill.
Type =TREND( and select the known y values.
Optionally add the known x values, then the new x values to predict.
Press Enter in Excel 365, or Ctrl+Shift+Enter in older versions.
Need it as an auditable file?
Ships inside the linked template — formula-driven, unlocked, audit-ready.
What this does
TREND fits the same least-squares line as FORECAST but returns predictions for many x values in one call, spilling the results in Excel 365 and requiring Ctrl+Shift+Enter in older versions. Omitting known_xs makes it assume evenly spaced periods 1, 2, 3 …, which is exactly right for a monthly series and saves building a helper column. It also handles multiple independent variables, making it a lightweight multiple regression. Setting the final argument to FALSE forces the line through the origin, which is occasionally correct and usually not. Keep the inputs visible and clearly labelled and the whole thing stays auditable — anyone who opens the file later, including you, can see at a glance exactly what feeds the result and change one assumption without hunting through the formula. Treat “trend formula in excel” as a small repeatable workflow rather than a one-off click you hope to remember next time. Use a small test block before the live file, so any surprise in the affected formula shows up while it is still harmless. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. That turns a calculation you can defend to a CFO or an auditor into a method you can reuse, explain, and defend when the workbook leaves your screen.
A worked example
Revenue for 24 months in B2:B25 with no x column: =TREND(B2:B25) spills the fitted values for those same months, showing the underlying line against the noisy actuals. To project the next six months, put 25 to 30 in D2:D7 and use =TREND(B2:B25, A2:A25, D2:D7). TREND draws the fitted line as data rather than decoration, so the smoothed series can feed further calculations. One habit worth forming early: name the cells that hold your inputs, so the formula reads in plain language instead of a string of cell addresses. A reviewer — or you in three months — can then follow the logic without decoding what B7 and D2 were supposed to mean, which is most of what makes a sheet maintainable.
In Google Sheets
Google Sheets handles this almost identically to Excel. The formula syntax above is the same, and the menu lives under a slightly different label rather than a ribbon tab. Use the platform toggle at the top of the page to switch every keyboard shortcut between Windows and Mac, and expect at most cosmetic differences in naming. The aim was to get you unstuck fast and leave you a little more capable than a copy-paste would. The answer is at the top and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. The short version of “trend formula in excel”: the answer is at the top of this page, the tool proves it on your own numbers, and the sections above explain why it holds so the next variation does not stump you. Excel rewards people who reference cells instead of typing values and who keep inputs separate from formulas, because that is what makes a result you can audit months later. Build it once, deliberately, with the live tool as a check, and you convert a one-off lookup into a reusable skill — which is the whole point of learning the why and not just the what.
Common mistakes
- Placing the formula where cells below are occupied, which blocks the spill and returns
#SPILL!. - Setting const to FALSE without a reason — forcing the intercept to zero distorts most business series.
- Using TREND on exponential growth; GROWTH fits that shape, TREND only fits a straight line.
Frequently asked questions
What is the difference between TREND and FORECAST?
FORECAST returns one value, TREND returns an array of them and supports multiple x variables.
Do I need a column of x values?
No. Omit known_xs and TREND assumes 1, 2, 3 …, which suits an evenly spaced time series.
Why does TREND return #SPILL!?
The cells where the array needs to land are not empty. Clear them and the result spills.