Excel cannot put a chart’s native trendline object inside a worksheet cell. For a one-cell numeric estimate, enter a formula such as =FORECAST.LINEAR(A7,B2:B6,A2:A6); for a small visual trend in a cell, use a line sparkline instead.
What “trendline in a cell” can mean
The phrase can describe two different things: a calculated value based on a trend, or a tiny graphic that shows a sequence rising and falling. Excel handles these differently. A formula can calculate a forecast in one cell, but it does not draw the fitted line there. A sparkline fits a chart-like visual into a cell, but it does not perform a regression or create a chart trendline.
Excel’s chart trendline is a chart element. It is fitted to plotted data and displayed over the chart’s plot area. Its equation can optionally be shown on the chart, but that equation label is not a worksheet cell value. Keeping these distinctions in mind makes it easier to choose the right method rather than trying to force a chart object into a cell.
Calculate a one-cell forecast with FORECAST.LINEAR
For Microsoft 365 on Windows, FORECAST.LINEAR is a direct way to predict a y-value for a specified x-value using a straight-line relationship in existing data. Arrange the historical x-values in one range, the matching y-values in another, and place the x-value you want to estimate in a separate cell. The function returns one numeric result in the cell where you enter it.
For example, with years in A2:A6, corresponding sales in B2:B6, and the year 2026 in A7, enter the formula below in B7. The ranges must contain the same number of observations, with each x-value paired to its y-value on the same row.
With years in A2:A6, sales in B2:B6, and 2026 in A7, enter in B7: =FORECAST.LINEAR(A7,B2:B6,A2:A6)
The result is an estimate, not an added line or a certainty about future sales. You can format B7 as currency or a number, and use a cell reference to the forecast in other calculations. If you change the historical data or the target year, the result recalculates automatically.
Worked example: sales from 2021 to 2025
Suppose the worksheet contains these pairs: 2021 and 120, 2022 and 135, 2023 and 128, 2024 and 150, and 2025 and 167. The x-values are years and the y-values are sales, measured in whatever consistent unit the business uses. Put 2026 in A7 and use the formula in B7 shown above.
For these values, the best-fit straight line has a slope of 9.9 sales units per year and predicts 169.7 for 2026. That arithmetic follows from the least-squares line through the five observations; it does not simply add the latest year’s change of 17. The prediction is therefore a trend-based estimate, not a guarantee and not necessarily a sensible forecast if business conditions have changed.
Using real dates rather than year numbers is often useful for monthly or irregularly spaced observations. Excel stores valid dates as numbers, so a formula can use dates as x-values. Make sure the target date is later than the observations when you intend to forecast forward, and keep the date intervals meaningful: a gap of several months should be represented by actual dates, not by consecutive row numbers.
What the formula calculates
FORECAST.LINEAR fits a straight line to the known x- and y-values, then evaluates that line at the requested x-value. In plain language, it finds a line that balances the squared vertical differences between the observed values and the line. The predicted value is the point on that fitted line for the x-value you supplied.
This is the same basic linear-regression model used by a linear chart trendline, assuming both methods use the same observations and the same numeric x-values. It does not mean every chart trendline can be reproduced by this one function. A polynomial, logarithmic, exponential, or moving-average trendline uses a different model or calculation.
The function is useful for getting a forecast into a cell, but it does not provide a confidence interval by itself. A single output can hide considerable uncertainty. Review the history, consider whether a straight-line model is appropriate, and avoid treating an extrapolated value far beyond the observed period as reliable just because Excel returns a number.
Show a compact visual with a sparkline
If you want a miniature picture of values in a worksheet cell, insert a sparkline. Select the cell where the visual should appear, then choose Insert > Sparklines > Line. In the dialog, specify the range of values to plot and confirm the location range. Excel places the small line graphic in the selected cell while the source data remains in its original cells.
A line sparkline shows the sequence’s shape at a glance. It can help readers compare many rows, such as monthly sales for several products, without placing a full chart in every row. With the sparkline selected, the Sparkline tab provides options for its style, markers, and axis. The graphic is tied to its source range, so changes to the source values update the display.
A sparkline is not a fitted trendline. It connects or represents the supplied observations; it does not automatically draw a regression line, calculate a future value, or show a chart’s trendline equation. If a compact visual and a numeric forecast are both needed, put a sparkline in one cell and a forecast formula in a neighboring cell.
Excel for Mac also offers sparklines, though the ribbon layout can differ slightly by release. Excel for the web supports many common worksheet and chart tasks, but ribbon availability and editing options can differ from the Windows desktop app. If a particular sparkline control is not visible, use the desktop app or consult the commands available in that version rather than assuming the sparkline is a regression line.
Add a real trendline to a chart
When the goal is to show a fitted line alongside the data, create a chart. For numeric x-values such as dates, distances, or elapsed time, an XY scatter chart is often appropriate because its horizontal axis treats x-values as numbers. Select the paired data and choose Insert > Insert Scatter (X, Y), then select the chart. On the Windows ribbon, choose Chart Design > Add Chart Element > Trendline and select Linear or another available trendline type.
To display the fitted equation or an R-squared value, open the trendline formatting options and select the relevant display option. Those labels appear on the chart, not in a worksheet cell. If you need the calculated estimate in the grid as well, use a formula such as FORECAST.LINEAR with the same observations. A category line chart can be suitable for evenly spaced categories, but its horizontal axis may not behave like a numeric x-axis, so the chart and formula can represent the data differently.
Choose the right option for the job
- One forecast number: Use
FORECAST.LINEARin a cell when a straight-line estimate is suitable and you need the result for further calculations. - A tiny trend visual: Use a line sparkline when readers need to see the pattern of the observations within the worksheet grid.
- A fitted line people can inspect: Use a chart trendline when the relationship, plotted data, equation, or model type needs to be visible.
- A different model: Use a chart’s other trendline types only when the data and purpose justify them; a linear forecast formula is not interchangeable with every trendline type.
These options can also be combined. A report might show a sparkline for each product, a forecast value in the adjacent column, and a separate chart for the products that need closer analysis. The choice depends on whether the reader needs a number, a quick visual, or a fuller explanation of the fitted model.
Common problems and checks
- Unequal ranges: The known x- and y-ranges must have the same number of values. Check that each year or date lines up with the correct observation.
- Text instead of numbers: Numeric-looking values imported as text may not behave as intended. Check for leading apostrophes, spaces, or inconsistent data types.
- Wrong x-values: Using row numbers for irregular dates assumes equal spacing. Use actual dates or another meaningful numeric measure when intervals vary.
- Unexpected forecast: One unusually high or low observation can influence a fitted line. Review the source data and chart it before relying on the result.
- Wrong trendline type: A formula for a straight-line forecast will not match a polynomial or exponential chart trendline. Compare like with like.
Also check whether a forecast is extrapolating well beyond the data. A line that fits a short historical period can project implausibly when extended far into the future, especially when the process has seasonality, a ceiling, a sudden change, or an underlying cycle. Excel’s calculation does not decide whether those assumptions make sense.
Frequently Asked Questions
Can Excel put a chart trendline inside a cell?
No. A chart trendline is a chart element. Use a forecast formula for a single numeric result, or a sparkline for a small visual in a cell.
What formula predicts a value from a linear trend?
Use FORECAST.LINEAR(x, known_y's, known_x's), replacing the arguments with the target x-value and matching ranges. It returns one linear estimate.
Is a sparkline the same as a trendline?
No. A sparkline is a miniature chart of supplied values. A trendline is a fitted model over plotted data; it can estimate the overall relationship rather than simply show the sequence.
Can I put the trendline equation in a worksheet cell?
The chart option to display an equation puts it on the chart. To get a calculated value in a cell, use a suitable worksheet formula. For a linear estimate, FORECAST.LINEAR is the direct option.
Why does my forecast not match the chart?
Check that the chart uses the same data and numeric x-values, and that its trendline is linear. A category axis, different selected points, or another trendline type can produce a different result.
Related
- Unlock an Excel Slicer: Steps, Protection, and Fixes
- Excel File Could Be Corrupted or Unsafe? What to Do
- State Heat Map in Excel: Create, Format, and Troubleshoot
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Google Sheets Chart Legend Labels: Edit and FixIn Google Sheets, chart legend labels usually come from the headings in the chart’s source data: edit the…
- Excel Pivot Chart Drill Down: Explore Data in ExcelTo drill down in an Excel PivotChart, select or right-click the chart data point and use an available…
- Excel Chart Axis Range: Set Minimum and Maximum ValuesTo change an Excel chart’s numeric axis range in Microsoft 365 for Windows, select the chart axis, right-click…
- Microsoft Excel Org Chart Add-Ins and Built-In OptionsMicrosoft Excel for Microsoft 365 does not require an add-in to make a basic org chart: use the…
- How to Open a Pivot Table Menu in Google SheetsTo open the pivot table controls in Google Sheets, click any cell inside the pivot table; the Pivot…
- How to Save a Graph in Excel as a Picture: PNG or JPGTo save a graph (which Excel calls a chart) as an image in Microsoft Excel for Microsoft 365…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.