Yes. To calculate a linear trendline in a worksheet cell, use Excel’s TREND function: with known x-values in A2:A6, known y-values in B2:B6, and the x-value to predict in A7, enter =TREND($B$2:$B$6,$A$2:$A$6,A7). The result is the trendline’s estimated y-value for the x-value in A7; it is a calculated worksheet result, not a chart object.
What an Excel trendline in a cell means
A trendline summarizes the relationship between two sets of values. A chart trendline is drawn over a data series so you can see that relationship visually. A trendline calculated in a cell uses a formula to return a number, such as an estimated sales total, temperature, or demand value for a particular input. The two approaches can use the same underlying data, but they serve different purposes.
For a straight-line trend, Excel fits a model of the form y = mx + b. Here, x is the input, y is the estimated output, m is the slope, and b is the intercept. The slope describes the average change in y for a one-unit increase in x. The intercept is the model’s estimated y-value when x is zero. Once Excel has estimated m and b, it can calculate a value for any x you supply.
For example, a store might use weekly advertising spend as x and weekly sales as y. A trendline formula can estimate sales at a proposed advertising spend. That estimate describes the pattern in the selected historical observations; it does not establish that advertising caused the sales change or guarantee that future sales will follow the same pattern.
Calculate a linear trend with TREND
Put the input values in one column and the values you want to predict in another, keeping each pair on the same row. In this example, enter 1, 2, 3, 4, and 5 in A2:A6 and 10, 12, 13, 16, and 19 in B2:B6. Put 6 in A7. With known y-values in B2:B6, known x-values in A2:A6, and the new x-value in A7, enter =TREND($B$2:$B$6,$A$2:$A$6,A7) in B7. Excel returns 20.6.
The dollar signs keep the known-data ranges fixed if you copy the formula to other rows. The reference A7 remains relative, so a copied formula can use A8, A9, and so on. TREND fits a least-squares straight line to the known observations: it chooses the line that minimizes the sum of squared differences between the observed y-values and the values on the line. This is why the line need not pass through every data point.
For a simple forecast column, add the future x-values below the historical ones, then fill the TREND formula down. Keep the historical ranges fixed so the model does not accidentally absorb earlier forecast results as new observations. If your x-values are dates, Excel stores dates as numbers, so a linear model can use them; however, predictions are based on elapsed date units, and irregularly spaced observations should be interpreted carefully.
Show the slope and intercept separately
Sometimes it is useful to show the fitted equation rather than only its predicted result. With x-values in A2:A6 and y-values in B2:B6, enter =SLOPE(B2:B6,A2:A6) in a cell to return the line’s slope, 2.2. With the same ranges, enter =INTERCEPT(B2:B6,A2:A6) in another cell to return the intercept, 7.4. These results make the fitted equation y = 2.2x + 7.4.
Then, with the calculated slope in D2, intercept in D3, and the new x-value in A7, enter =$D$2*A7+$D$3 in B7. It returns 20.6 for x = 6. This method is handy when you want to display or reuse the model’s parameters. TREND is usually more convenient when you want forecasts directly, while SLOPE and INTERCEPT make the equation’s components visible.
Another option for a single prediction is FORECAST.LINEAR. With known y-values in B2:B6, known x-values in A2:A6, and the target x-value in A7, enter =FORECAST.LINEAR(A7,B2:B6,A2:A6). It also returns 20.6 in this example. FORECAST.LINEAR predicts one value at a time; TREND can return predictions for one or more new x-values.
Make a chart trendline and compare the result
If you want the line displayed on a chart, create an XY (Scatter) chart from the paired numeric data, select the data series, and use Chart Design > Add Chart Element > Trendline > More Trendline Options in Excel for Microsoft 365 on Windows. In the Format Trendline pane, select the model type and, if useful, enable Display Equation on chart or Display R-squared value on chart. These chart options add information to the graphic; they do not put a forecast formula into a worksheet cell.
A chart’s displayed equation is useful for communicating a relationship, but the equation label may round its coefficients. A forecast calculated from the chart label can therefore differ slightly from one calculated with worksheet functions using full precision. For calculations, use TREND or FORECAST.LINEAR, or calculate the slope and intercept in cells. You can increase the displayed equation’s decimal places in its formatting options, but worksheet calculations remain preferable when precision matters.
On Mac, select the chart and use the Chart Design tab’s chart-element controls; names and placement can vary slightly by Excel release. Worksheet formulas such as TREND, SLOPE, INTERCEPT, and FORECAST.LINEAR work in Microsoft 365 for Mac as well. Excel for the web supports worksheet formulas, though chart-editing controls may differ from the Windows desktop ribbon and can vary by build.
Choose the right trendline model
A linear model is a reasonable starting point when changes in y are roughly constant for equal changes in x. It is not the only model available. A curved pattern may call for another model, but choosing a more complex curve simply because it follows the historical points closely can make future estimates less dependable.
- Linear: A straight line, suitable when the change is approximately steady. TREND and FORECAST.LINEAR calculate this type of model in cells.
- Exponential: A pattern that grows or declines by a roughly constant percentage rather than a constant amount. Excel’s GROWTH function calculates exponential predictions; its known y-values must be positive.
- Polynomial: A curved model that can represent bends in the observed data. Chart trendline settings let you choose a polynomial order. Higher orders can fit historical points closely but may behave erratically outside the observed range.
- Logarithmic or power: Models for particular curved relationships. Excel offers them as chart trendline types, with data restrictions; for example, logarithmic models require positive x-values, and power models require positive x- and y-values.
- Moving average: A smoothed series that averages nearby observations. It can help reveal direction in a chart, but it is not the same as a fitted regression equation for predicting a new x-value.
For an exponential cell-based prediction, GROWTH is a relevant alternative. With positive known y-values in B2:B6, known x-values in A2:A6, and a target x-value in A7, enter =GROWTH(B2:B6,A2:A6,A7). This fits an exponential relationship, not a straight line, so its output should not be compared with a linear estimate as if they were interchangeable. Check whether the selected model makes sense for the subject and the data.
Check the data before trusting a forecast
Trendline formulas can return a result even when the input data is poorly suited to prediction. A number appearing in a cell does not prove that the model is appropriate. Before relying on a result, check the observations and the intended use:
- Confirm that each x-value is paired with the correct y-value on the same row. Sorting only one column breaks those pairs.
- Use numeric x-values. Text that looks like a number may not behave as expected, and category labels such as product names are not meaningful linear inputs.
- Check for blank cells, errors, unusual outliers, and changes in how the data was collected. A single extreme observation can noticeably change a fitted line.
- Plot the data as an XY (Scatter) chart. A graph can reveal curvature, clusters, or a sudden change that one straight-line model hides.
- Distinguish interpolation from extrapolation. Estimating between observed x-values is generally less speculative than extending the line far beyond the historical range.
Also consider whether the relationship changes over time. A long history may mix different conditions, such as a product launch, price change, or new measurement method. A single trendline treats all included rows as one pattern. Restricting the input ranges to a relevant period can be more informative, but the choice of period should have a real reason rather than being selected just to produce a preferred forecast.
Common problems and fixes
If the answer is unexpected, first check the order of the arguments. TREND takes known y-values first and known x-values second; SLOPE and INTERCEPT use the same y-then-x order. FORECAST.LINEAR instead starts with the target x-value, followed by the known y-values and known x-values. Make sure every known-x range has the same number of rows as its known-y range.
A formula returning an error may point to mismatched ranges, nonnumeric inputs, or invalid data for the selected model. An unexpected but plausible number often indicates a layout issue: the x and y columns may be reversed, the forecast input may refer to the wrong cell, or a copied formula may have shifted its historical ranges. Inspect the formula bar and use absolute references for the fixed observation ranges.
Another common misunderstanding is that a trendline formula predicts the next row automatically. It predicts for the x-value you provide. If the historical x-values are 1 through 5, the formula only forecasts the next period if you explicitly provide 6, and only if that numbering corresponds to the real spacing of your data. For dates, use the actual target date rather than assuming each row represents an equal interval when it does not.
When a chart, formula, or other tool is best
Use a chart trendline when the main goal is to explain a pattern visually, compare a model with the observations, or show an equation and R-squared value on a graphic. Use TREND or FORECAST.LINEAR when a later calculation needs a numeric estimate in a cell, when you need a column of estimates, or when other formulas will use the output. You can use both: show the scatter chart for context and keep cell formulas for reproducible calculations.
For a more formal forecast, Excel’s Forecast Sheet feature can create a forecast worksheet and chart from time-based data. Select the data, then choose Data > Forecast Sheet in supported desktop versions of Excel. It is intended for time-series forecasting and may apply methods beyond a simple straight-line trend, so read the generated output and settings rather than treating it as identical to a chart trendline. For complex seasonal patterns or decisions with significant consequences, a basic trendline may not be an adequate forecasting method.
Frequently Asked Questions
Can I put a trendline equation in an Excel cell?
Yes. Calculate the slope with SLOPE and the intercept with INTERCEPT, then use those values in a formula such as slope times x plus intercept. TREND is simpler when you only need predicted values.
Is TREND the same as adding a chart trendline?
No. TREND returns one or more calculated values in cells. A chart trendline is a visual chart element. Both can fit a linear pattern, but chart equation labels can round coefficients.
Why does my trendline forecast differ from the chart equation?
The equation shown on a chart may display rounded coefficients. A cell formula generally calculates with greater precision. Also check that the chart and formula use the same observations and model type.
Can Excel calculate a curved trendline in a cell?
Yes, for some models. GROWTH handles an exponential relationship, while chart trendline options include polynomial and other types. Use the function and model that match the data; a chart-only model type may not have a direct one-function equivalent for every use.
Can I use dates as the x-values?
Yes. Excel dates are stored as serial numbers, so linear functions can use them. Ensure the target date is a real Excel date and remember that a straight line models change per date unit, not necessarily change per business period.
Related
- Excel Solver Simplex LP: When to Use It and How It Works
- Excel Solver Slow? Causes and Ways to Speed It Up
- Excel Solver Sensitivity Report: How to Read It
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- 3D Pie Chart in Excel: Create, Format, and Improve ItTo make a 3-D pie chart in Excel for Microsoft 365 on Windows, select a two-column range containing…
- Excel Power Pivot vs Power Query: What’s the Difference?Power Query prepares data by connecting to sources, cleaning it, and reshaping it; Power Pivot builds a data…
- Excel Pivot Table zh: Chinese Terms and SetupIn an Excel search, “zh” usually means Chinese: it is the two-letter language code commonly used for Chinese,…
- Best Free Excel Gantt Chart Templates: 5 PicksFor most people, Vertex42’s Gantt Chart Template is the best free Excel Gantt chart template: it gives you…
- Excel Pivot Table: Generate Multiple Sheets?Yes, you can generate multiple sheets from a single Excel pivot table, but not with a built-in one-click…
- Enable Power Pivot in Excel: Steps, Versions, and FixesTo enable Power Pivot in Excel for Microsoft 365 on Windows, go to File > Options > Add-ins,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.