In Excel, FORECAST.LINEAR predicts one value from a linear relationship, while TREND calculates one or more values from that same kind of relationship. For a single future point, use FORECAST.LINEAR; choose TREND when you want forecasts for several x-values at once or need the option to force the line through zero.
What Excel users mean by forecast and trend
The terms can refer to different things in Excel. FORECAST.LINEAR and TREND are worksheet functions that fit a straight line to known data. A chart trendline is a visual line added to a chart, and Forecast Sheet is a separate tool that can project time-series data using a seasonal model. These features are related, but they are not interchangeable.
When someone asks about Excel forecast versus trend, they usually mean the FORECAST.LINEAR function versus TREND. Both use linear regression: Excel estimates the line that best fits the supplied known x- and y-values, then uses that line to estimate y for a specified x. They do not simply extend the last observed change. A high or low data point can influence the fitted line, and the resulting estimate may not match the latest month’s direction.
In a typical business worksheet, x-values might be dates or month numbers, and y-values might be units sold, website visits, or expenses. The functions can calculate a straight-line estimate, but they do not know what the values represent or whether extending the relationship is sensible.
How FORECAST.LINEAR works
FORECAST.LINEAR has three arguments: the x-value to estimate, the known y-values, and the corresponding known x-values. It returns a single predicted y-value. The x and y ranges must correspond: each known x must pair with the y recorded for that observation. In Microsoft 365 for Windows, enter the function in a worksheet cell like any other formula.
Suppose A2:A5 contains month numbers 1, 2, 3, and 4, while B2:B5 contains sales of 100, 120, 130, and 150 units. To estimate sales for month 5, put 5 in A6. With month numbers in A2:A5, sales in B2:B5, and the target month in A6, enter =FORECAST.LINEAR(A6,B2:B5,A2:A5). The result is 165 units.
The estimate comes from the best-fit line, not from adding the most recent increase of 20 units to the last observation. In this example, the fitted line has a slope of 16 units per month and an intercept of 85, so its estimate at month 5 is 85 + 16 × 5 = 165. That distinction matters whenever the period-to-period changes vary.
Excel also retains the older FORECAST function for compatibility with older workbooks. For a linear estimate, FORECAST and FORECAST.LINEAR are equivalent in current Excel, but the newer name makes the linear method explicit. Use FORECAST.LINEAR in new work unless you have a reason to preserve an existing formula.
How TREND works
TREND uses the known y-values and, optionally, their matching x-values to calculate a linear fit. Its new_x’s argument can contain one x-value or several. With one new x-value, it returns one estimate; with several x-values, Microsoft 365 can return a corresponding set of estimates. Its optional const argument controls whether Excel calculates an intercept. By default, const is TRUE, so the line is fitted normally. Set it to FALSE only when a zero intercept is justified.
Using the same sales data, with months in A2:A5, sales in B2:B5, and month 5 in A6, enter =TREND(B2:B5,A2:A5,A6). The result is 165 units, matching FORECAST.LINEAR because both functions use the same linear fit with an intercept.
TREND is especially useful when you need a run of projections. With known months in A2:A5, sales in B2:B5, and future months 5, 6, and 7 in A6:A8, enter =TREND(B2:B5,A2:A5,A6:A8) in C6. In Excel for Microsoft 365, the results spill into C6:C8 as 165, 181, and 197. The three estimates follow the fitted slope of 16 units per month. Leave the spill area empty; an occupied cell can block the results and produce a #SPILL! error.
Which function should you choose?
For most worksheets, the choice is about the shape of the output and the options you need, not a different forecasting method. Use this quick guide:
- Choose FORECAST.LINEAR when you need one predicted y-value for one target x-value and want a direct, readable formula.
- Choose TREND when you need estimates for multiple future x-values, or when you need its const option.
- Choose neither as a substitute for a seasonal forecast if the data rises and falls in recurring cycles.
- Use a chart trendline when the main goal is to show a fitted line on a chart rather than use estimates in calculations.
For example, a manager estimating next month’s demand from a few historical observations might use FORECAST.LINEAR for the single target month. A planning sheet with a column of future months may use TREND once to return the whole series. Either way, use the same x-values and y-values if you expect the same linear estimates.
There is one meaningful mathematical option: TREND can omit the intercept by setting const to FALSE. With months in A2:A5, sales in B2:B5, and month 5 in A6, =TREND(B2:B5,A2:A5,A6,FALSE) returns about 221.67 for the example data. That line is constrained to pass through (0,0), so it is a different model and a substantially different answer. Do not impose that constraint just to make the formula simpler; it is appropriate only if a zero x-value should logically produce a zero y-value.
Linear forecasting versus Excel Forecast Sheet
Forecast Sheet is a built-in Excel feature for projecting time-based data, not another name for FORECAST.LINEAR or TREND. In Excel for Microsoft 365 on Windows, select a timeline and its associated values, then choose Data > Forecast Sheet. Excel creates a new worksheet with a forecast and a chart. The dialog lets you set an end date and adjust options such as seasonality and the confidence interval.
Forecast Sheet uses exponential smoothing, which can account for seasonal patterns when the data supports them. This is useful for a series such as monthly sales that tends to rise every December and fall in January. A straight-line FORECAST.LINEAR or TREND estimate does not model that repeating seasonal pattern; it summarizes the overall linear relationship instead. The Forecast Sheet result is not automatically better: irregular data, too little history, or a structural change in the business can make any projection unreliable.
A chart trendline is different again. To add one, create or select a chart and use the chart controls to add a Trendline, then choose a type such as Linear. A trendline helps readers see a pattern, and chart options can display its equation and R-squared value. Those visual settings do not turn it into a set of worksheet forecasts. If you need forecast values for calculations, use a worksheet function or Forecast Sheet.
Prepare the data before forecasting
Good inputs matter more than choosing between two functions that fit the same straight line. Check the source data before extending it:
- Keep each x-value paired with the correct y-value, and make sure both ranges cover the same number of observations.
- Use numeric x-values. Excel stores valid dates as serial numbers, so dates can work as x-values, but labels such as Jan or Q1 entered as text are not numeric measures of time.
- Sort observations in time order when reviewing the series. The regression calculation depends on the paired values rather than row order, but sorted data makes mismatched rows and unusual changes easier to spot.
- Investigate blanks, text, errors, and outliers. Do not assume an unusual observation is a typo; it may reflect a promotion, outage, policy change, or other event.
- Choose a target x-value that makes sense relative to the history. Projecting far beyond the observed range is extrapolation and generally adds uncertainty.
For dates, a common practical choice is to use actual Excel dates as x-values, or a consecutive month index such as 1, 2, 3. Do not use month names as text and expect the function to understand their calendar order. If monthly observations have gaps, use actual dates or a carefully defined time index rather than assuming every row represents one month.
Common problems and interpretation
A frequent source of error is reversing the argument order. FORECAST.LINEAR takes the target x first, then known y-values, then known x-values. TREND starts with known y-values, followed by known x-values and then new x-values. Remembering that difference helps prevent a formula that calculates an unintended result.
Another problem is confusing a forecast with a guarantee. A result of 165 does not mean sales will be exactly 165. It is the estimate produced by a chosen model from the supplied observations. The functions do not automatically provide a business-specific risk range, account for promotions, or adjust for a competitor entering the market. Compare estimates with actual outcomes and explain assumptions when sharing a forecast.
Check whether a linear pattern is plausible. If growth compounds, a straight line may understate later increases; if a series is seasonal, a straight line can hide recurring highs and lows. Plot the historical data, look for changes in level or direction, and compare the model’s estimates with known context. A forecast can be mathematically valid yet unsuitable for the decision at hand.
Finally, distinguish interpolation from extrapolation. Estimating a value between known x-values is interpolation; estimating beyond them is extrapolation. The latter is often what people intend by forecasting, but it relies on the assumption that the historical relationship continues. The farther the target lies beyond the data, the less support the observations provide for that assumption.
Frequently Asked Questions
Are FORECAST.LINEAR and TREND the same?
For the same known data and a standard linear fit with an intercept, they use the same regression relationship. FORECAST.LINEAR returns one estimate, while TREND can return estimates for multiple new x-values and offers an option to constrain the intercept.
Does TREND forecast seasonality?
No. TREND fits a straight line and does not identify recurring seasonal patterns. For time series with seasonality, consider Excel’s Forecast Sheet or a method designed to represent the seasonal behavior.
Why do FORECAST.LINEAR and TREND return different values in my workbook?
Check that the known x- and y-ranges contain the same paired observations, that the target x-value is the same, and that TREND’s const argument has not been set to FALSE. Also check for accidentally shifted ranges or inconsistent data.
Can I use dates with FORECAST.LINEAR?
Yes. Excel dates are numeric serial values, so they can serve as x-values when entered as real dates. Use matching dates and observations, not date labels stored as text.
Which is better for sales forecasting?
Neither is universally better. For one estimate from a plausible linear relationship, FORECAST.LINEAR is straightforward. For a series of linear estimates, TREND is convenient. If sales have seasonality or major business changes, consider a model or Forecast Sheet that addresses those features and review the assumptions.
Related
- Excel Custom Number Format for Millions
- Excel File With Table Filters: How to Add and Use Them
- Excel File Too Large? How to Make It Smaller in Excel
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for a Whole Column: Options and ExamplesFor a calculation that should use every cell in a column, use a whole-column reference such as =SUM(B:B);…
- Excel Formula for Number of Years From a Date, ExplainedTo calculate completed years from a date in Excel, use =DATEDIF(A2,TODAY(),"Y"), where A2 contains the starting date. For…
- Excel REDUCE Function Examples With NumbersIf your numbers are in A2:A6, add them with =REDUCE(0,A2:A6,LAMBDA(acc,n,acc+n)). With 12, -3, 8, 0, and 5 in…
- Excel Conditional Formatting: Nested IFs for Dates?Yes. Excel conditional formatting can use a nested IF formula, but the formula must ultimately return TRUE or…
- Excel LET Function: How to Name and Reuse CalculationsUse Excel’s LET function to give a name to a value or calculation, then use that name elsewhere…
- Excel Filter by Color and Value: How It WorksYes. In Excel for Microsoft 365 on Windows, open a column’s filter menu and choose a color under…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.