Yes. Excel can estimate a value from a trendline: use a chart’s trendline forecast controls to extend the line visually, or use the FORECAST.LINEAR function to calculate a predicted value in a worksheet cell. The right method depends on whether you need a chart projection or a reusable numeric result, and on whether a straight-line model fits your data.
What an Excel trendline predicts
A trendline is a mathematical model fitted to a set of observed data. It summarizes a pattern—such as steady growth or curved growth—and estimates what the pattern would imply at another input value. In a chart, Excel can draw the fitted line over the existing data and, for supported trendline types, extend it forward or backward. In a worksheet, a forecasting function can return the predicted value directly.
A prediction is not a newly observed result. If monthly sales have increased by about the same amount each month, for example, a trendline may estimate next month’s sales from that pattern. It cannot know about a price change, a stockout, a new competitor, seasonality, or any other event absent from the data. Treat the result as a model-based estimate, not a guarantee.
Trendlines are most useful when the data has a meaningful order and the relationship is reasonably consistent. The input values might be time periods, distance, temperature, or another numeric measurement. The output values are the measurements you want to explain or predict.
Extend a trendline on a chart
In Excel for Microsoft 365 on Windows, create a chart from the input and output columns, select the chart, then choose Chart Design > Add Chart Element > Trendline and select a type, such as Linear. To adjust it, choose Chart Design > Add Chart Element > Trendline > More Trendline Options. In the Format Trendline pane, use the Forecast fields to extend the line forward or backward. The number is measured in units of the horizontal axis, so a forward forecast of 2 means two x-axis units—not automatically two calendar months.
For example, if the horizontal values are 1, 2, 3, 4, and 5, a forward extension of 2 projects the line to x-values 6 and 7. To show the model equation on the chart, select Display Equation on chart in the Format Trendline pane. You can also select Display R-squared value on chart as a quick fit statistic. These options add information to the visual; they do not create forecast values in worksheet cells.
On Excel for Mac, select the chart and use the chart design controls to add a trendline, then open its formatting options to set the forecast and equation display. Ribbon labels and pane layouts can vary slightly by release. Excel for the web has more limited chart editing controls than the desktop apps, so open the workbook in desktop Excel if the needed trendline settings are not available.
Calculate a predicted value in a cell
For a straight-line relationship, FORECAST.LINEAR is usually the simplest option. It estimates the output for a specified x-value using the existing x-values and y-values. The x-values and y-values must correspond row by row, and the ranges should contain numeric data rather than headers.
Assume x-values are in A2:A6 as 1, 2, 3, 4, and 5, and corresponding y-values are in B2:B6 as 10, 12, 14, 16, and 18. Put the target x-value 6 in E2. With that layout, enter:
=FORECAST.LINEAR(E2,B2:B6,A2:A6)
The result is 20. In this example, y rises by 2 for each one-unit increase in x, so extending the same straight-line pattern from x = 5 to x = 6 gives 20. The first argument is the x-value to predict; the second is the known y-range; the third is the known x-range. The formula recalculates if the source values or target x-value change.
You can also calculate the line’s slope and intercept separately when you want to inspect the model. With x-values in A2:A6 and y-values in B2:B6 as in the example above, the slope formula is:
=SLOPE(B2:B6,A2:A6)
It returns 2. With those same ranges, the intercept formula is:
=INTERCEPT(B2:B6,A2:A6)
It returns 8. Those values describe the line mathematically: each one-unit increase in x corresponds to a two-unit increase in predicted y, and the line crosses the y-axis at 8. For routine linear prediction, FORECAST.LINEAR combines the calculation without requiring you to build the equation yourself.
Choose the right trendline type
A straight line is a useful starting point, not a universal choice. Excel offers several chart trendline types, and each describes a different shape. Pick one based on the process behind the data as well as the way the plotted points look.
- Linear: Use when the output tends to change by a similar amount for each equal step in the input. It is often suitable for a short, steady trend.
- Polynomial: Use for a curved pattern that changes direction or rate. Excel lets you set the order; a higher order can fit more bends but can also follow random fluctuations too closely.
- Exponential: Use when growth or decline is proportional to the current level, producing a curve that accelerates or decelerates. The data must support this type of model; nonpositive y-values are not suitable for the usual exponential fit.
- Logarithmic: Use for a pattern that changes quickly at first and then levels off, where the input values are positive.
- Power: Use for a relationship in which output changes according to a power of the input. Both x and y values need to be positive for this model.
- Moving average: Use to smooth short-term ups and downs and make the underlying pattern easier to see. It is a smoothing method, not a straightforward equation for predicting future values.
Excel’s chart trendline options are not interchangeable with FORECAST.LINEAR, which fits a straight-line relationship. If the chart suggests a curved pattern, a linear forecast function will still calculate a straight-line estimate; it will not automatically use the chart’s polynomial, logarithmic, or other trendline. For a nonlinear model, use an appropriate model-specific method or the equation from the selected chart trendline, taking care with its precision and valid input range.
Use the equation and R-squared carefully
The equation displayed on a chart is useful for understanding the chosen model, but Excel may show only a limited number of decimal places. If you copy those rounded coefficients into a calculation, the result can differ from the chart’s internally fitted line, especially for large x-values or a sensitive curved model. Increase the displayed decimal places in the equation’s number formatting if you need a closer manual calculation, but a rounded chart label is still not a substitute for the full underlying precision.
R-squared describes how closely a fitted line accounts for the variation in the data used to fit it. A value closer to 1 can indicate a closer fit on those observations, but it does not prove that the model is appropriate or that a future prediction will be accurate. A high-order polynomial can produce a high R-squared by following the existing points while behaving wildly beyond them. Check the chart, the subject matter, and predictions on data that was not used to fit the model when reliability matters.
A trendline forecast also does not normally provide a prediction interval showing a range of uncertainty. A single displayed line can make an uncertain estimate look more precise than it is. If decisions depend on the forecast, examine historical errors, test alternative assumptions, and communicate that uncertainty rather than presenting one projected number as certain.
Dates, category axes, and extrapolation
Date values are stored by Excel as serial numbers, so a worksheet forecast can use actual dates as numeric x-values. Keep the dates in chronological order and ensure the y-values align with them. A prediction target should be a real date in the same scale as the known dates. If the observations are irregularly spaced, using actual dates can matter: treating each row as one equal step would ignore the gaps.
Chart axis behavior deserves attention. A Line chart often uses a category axis, where positions represent categories rather than proportional numeric distances. If your x-values are measurements or dates with unequal gaps, an XY (Scatter) chart is generally a better way to represent their spacing. It is especially useful when you want a trendline whose horizontal scale reflects actual numeric x-values. Confirm that the axis and forecast units mean what you intend before interpreting an extension.
Extending a fitted line beyond the observed range is called extrapolation. It assumes the pattern continues where there is no data to confirm it. Forecasting one step beyond a stable series may be more defensible than projecting far into the future, but neither is guaranteed. The farther the target lies outside the observed x-values, the more strongly the estimate depends on an unverified assumption.
Common problems and checks
If a chart projection looks wrong or a formula returns an unexpected result, check the data and model before changing the forecast. Common causes include:
- Misaligned ranges: Each x-value must pair with the y-value from the same observation. A shifted range can produce a plausible-looking but incorrect result.
- Numbers stored as text: Text-formatted numbers, blank cells, or error values can prevent a clean calculation or distort the chart. Check the source columns and convert values where needed.
- Wrong scale or units: A forecast of 12 means 12 units on the x-axis. If the axis represents years, that differs greatly from 12 months.
- Wrong chart type: A category axis may space observations evenly even when dates are irregular. An XY Scatter chart can show numeric spacing more faithfully.
- Overfitting: A complex curve may match a small set of points but give unrealistic future values. Prefer the simplest model that makes sense for the data.
- Seasonality or structural changes: A basic trendline does not automatically account for recurring seasonal patterns, policy changes, or one-off events.
It is also worth comparing predictions with actual outcomes as new data arrives. Keep the original observations separate from forecasts, label projected values clearly, and avoid replacing actuals with model estimates. This makes it possible to see whether the forecasting approach continues to work.
Chart trendline or worksheet formula?
Choose the chart method when the main goal is to communicate a direction visually or show how a line would extend. It is quick and helps reveal whether a straight or curved pattern seems plausible. Choose a worksheet function when you need a value to reference in another calculation, repeat for many target periods, or update automatically as data changes.
These methods can complement each other. Use a chart to inspect the points, identify outliers, and judge whether a linear pattern is reasonable. Then use a worksheet formula to calculate explicit estimates and compare them with later actual values. Remember that a chart’s forecast extension and a cell formula may differ if they use different model types, axis interpretations, or rounded coefficients.
Frequently Asked Questions
Can Excel predict a value from a trendline equation?
Yes. For a linear trend, use FORECAST.LINEAR with the target x-value and the known x- and y-ranges. You can also use the displayed equation, but rounded chart coefficients may make its result slightly different from Excel’s fitted line.
How do I forecast the next value in Excel?
Place the next x-value in a cell, then use FORECAST.LINEAR with that cell and the matching historical x- and y-ranges. For a chart-only projection, add a trendline and set its forward forecast in the Format Trendline pane.
Why does Excel’s trendline prediction differ from my chart?
The chart may use a nonlinear trendline while FORECAST.LINEAR always uses a linear model. Differences can also come from rounded displayed coefficients, a category rather than numeric axis, or mismatched data ranges.
Can I use a trendline to predict dates?
Yes, when the dates are valid Excel dates and the observations are paired consistently with their values. Use an appropriate target date and consider an XY Scatter chart if the time intervals are unequal or you need spacing to reflect the actual dates.
Does a high R-squared mean the forecast is reliable?
No. R-squared measures fit to the data used to build the trendline, not certainty about future values. Check whether the model makes sense, whether the data is representative, and how well earlier predictions performed.
Related
- Excel Power Query Editor: What It Does and How to Use It
- Excel Power Query: How to Extract and Refresh Data
- Excel Power Query Date Format: Types, Changes, and Fixes
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Data Modeling in Excel Power Pivot: A Practical GuideData modeling in Excel Power Pivot means connecting related tables in one workbook so they can be analyzed…
- How to Apply an Outline to a Chart Area in ExcelTo outline a chart in Excel for Microsoft 365 on Windows, right-click the chart’s outer background, choose Format…
- Can’t See Power Pivot in Excel? Fix the Missing TabIf you can’t see Power Pivot in Excel for Microsoft 365 on Windows, first enable its COM add-in:…
- Excel Stacked Column Chart With Two Data Sets ExplainedYes. In Excel for Microsoft 365 on Windows, place the two data sets in adjacent columns beside the…
- Org Chart in Excel or Word? Choosing the Right ToolFor a straightforward org chart that is mainly a document, use Word; for a chart that sits alongside…
- How to Make a Histogram Start at 0 in ExcelTo make histogram bins start at 0 reliably in Excel, create bin intervals beginning at 0, count the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.