How to Choose the Right Trendline in Excel

Choose an Excel trendline by matching its shape to the pattern and likely relationship in your data, then check whether it fits sensibly: use linear for a steady straight-line change, exponential for growth or decay by a roughly constant percentage, logarithmic for a rapid change that levels off, polynomial for a curved pattern, power for a scale relationship, or moving average to smooth short-term fluctuations. Start with a scatter chart when both axes contain numeric values, and do not pick a curve only because it produces a higher R-squared value.

What You’ll Need

A worksheet with paired observations: one column for the independent variable (often time, distance, or quantity) and one for the measured result.
A chart that displays the data as points or a series. An XY (Scatter) chart is usually best when the x-values are numbers and their spacing matters.
Microsoft Excel for Microsoft 365 on Windows. Trendline options are also available in current Excel for Mac, though some ribbon and pane layouts differ. Excel for the web has fewer chart-editing capabilities, so open the workbook in the desktop app if a trendline option is missing.

Step-by-Step

Check the data before charting. Put x-values in one column and the corresponding y-values in the next, with one observation per row and clear headers. Remove blank rows inside the data, text values where numbers are expected, and accidental duplicates. Check that dates are real Excel dates rather than text. Do not sort just one column: that would detach the x-values from their matching results. A trendline describes the values you provide, so data-entry errors can make a legitimate pattern look wrong.
Decide whether the x-axis is numeric or categorical. If the horizontal values represent measurable quantities with meaningful gaps, such as 1, 2, 10, and 20, create an XY (Scatter) chart so Excel uses those numeric distances. Select the paired data and choose Insert > Insert Scatter (X, Y) or Bubble Chart > Scatter. If you use a line chart, Excel generally spaces category labels evenly; that can distort the apparent slope when dates or numeric intervals are irregular. Line charts can still suit equally spaced periods, such as monthly measurements taken at regular intervals.
Look at the plotted points before adding a curve. Select a chart that shows the observations clearly, then inspect the overall direction, curvature, and outliers. A cloud that follows a fairly straight path suggests a linear model. A curve that climbs or falls faster as x changes may be exponential or power-shaped. A curve that changes quickly and then flattens may be logarithmic. A smooth bend or turning point may call for a polynomial. If points bounce around a gradual pattern, a moving average may help reveal that pattern without claiming that the original observations follow a specific equation.
Add a first trendline from the chart ribbon. Click the plotted data series, then choose Chart Design > Add Chart Element > Trendline > More Trendline Options. In the Format Trendline pane, select one type to begin with. Depending on the chart, you can also right-click the data series and choose Add Trendline. On Mac, select the series and use the Chart Design tab or the chart’s contextual controls; the Format Trendline pane may be arranged differently. If Trendline is unavailable, confirm that you selected a supported two-dimensional data series, rather than the chart area or an unsupported chart type.
Match the type to a plausible relationship. Choose Linear when each increase in x is associated with a roughly constant change in y. Choose Exponential when y changes by a roughly constant proportion over equal x-steps, as can happen with growth or decay; Excel requires positive y-values for this fit. Choose Logarithmic when y changes quickly at first and then levels out, with positive x-values. Choose Power when a relationship is plausibly expressed as one positive quantity raised to a constant power; both x and y must be positive. Choose Polynomial when the points have a genuine smooth bend or turn. Choose Moving Average when the aim is to smooth local noise across ordered observations, not to describe a permanent mathematical relationship.
Use polynomial order sparingly. If you select Polynomial in the pane, set the Order to the lowest value that captures the real shape. Order 2 can represent one broad bend; higher orders can introduce extra bends. A complicated curve may pass close to every plotted point yet behave wildly between observations or beyond the data. Do not raise the order just to improve the chart’s R-squared value. A higher-order fit is more defensible only when the shape is supported by the subject matter and enough observations.
Compare reasonable candidates on the same chart. Add another trendline if you want to compare plausible types, and use a different color or line style so they remain distinguishable. Look at how the line sits relative to the points, especially at the beginning and end of the data. Check whether one or two unusual observations are controlling the shape. If you display R-squared, treat it as a descriptive fit statistic: a value closer to 1 means the model explains more of the variation in this dataset, but it does not prove that the model is correct, causal, or useful for prediction.
Inspect the equation and residual pattern when the choice matters. In the Format Trendline pane, select Display Equation on chart and, if useful, Display R-squared value on chart. The equation helps you identify the form Excel fitted; it is not a guarantee that the relationship will continue. For a more careful check, calculate predicted values using the displayed equation and compare them with the observed values, or examine a residual plot (observed minus predicted). Residuals should not show an obvious curve or other systematic pattern if the chosen shape captures the main relationship. Excel rounds the displayed coefficients, so use more decimal places if you need to reuse them.
Set forecast options only after selecting a sensible model. In the pane, use Forecast Forward or Forecast Backward to extend the line for a specified number of x-axis units. Forecast length is not evidence of reliability: a model can fit historical points and still fail outside their range. Keep the extension modest, consider whether the process could change, and explain that it is an extrapolation. If the x-axis is categorical rather than numeric, the meaning of a forecast distance may not match the actual elapsed time.
Label the chart so the choice can be understood later. Add a chart title, meaningful axis titles, units, and a legend if you have multiple series or candidate lines. If showing the equation, ensure it does not cover data points. A useful note can state why the type was chosen, such as “linear fit across the observed range.” This makes it clear that the trendline is a model of the plotted data, not another set of measured observations.

Common Mistakes

Choosing by R-squared alone. More flexible curves often achieve a higher value simply because they can follow the sample more closely. Prefer a simple, meaningful model over a complicated curve with no reason behind it.
Using a line chart for uneven numeric intervals. Equally spaced category labels can make an irregular timeline look evenly spaced. Use an XY scatter chart when the actual x-values and gaps matter.
Extending a curve far beyond the observations. Extrapolated values depend on the model continuing to hold. A visually smooth line is not proof that future behavior will follow it.
Applying an incompatible fit. Exponential, logarithmic, and power trendlines have positive-value requirements in Excel. A zero or negative value can prevent the fit or make it unsuitable.
Letting one outlier dictate the curve. Check the source record first. Correct genuine data errors, but do not delete a valid unusual observation merely because it spoils the line.

Tips

Use the simplest trendline that explains the visible pattern and makes sense for the process being measured.
For a moving average, choose a period that fits the data frequency. A short period preserves more local movement; a longer one smooths more but can hide turning points and respond slowly to changes.
Compare candidate models over the same observed range and against the same data. Do not compare a forecasted line with a fit calculated on a different subset without noting the difference.
Keep a copy of the original data and record any exclusions or transformations. A chart should not conceal how the dataset was prepared.

Troubleshooting

The Trendline option is missing: Click an actual data series, not the chart background or axis. If needed, change to a supported two-dimensional chart such as Scatter or Line. Stacked charts and some specialized chart types do not support trendlines in the same way; in Excel for the web, open the file in the desktop version if chart options are limited.

Excel will not add the trendline: Check for blank or text cells in the plotted values and confirm that the selected series contains numeric observations. If you chose exponential, logarithmic, or power, check the required positive values. Try a scatter chart to make the x- and y-ranges explicit.

The line looks wrong or does not follow the points: Verify that each x-value is paired with the right y-value, that dates are stored as dates, and that the chart is plotting the intended series. Check whether an outlier, uneven category spacing, or an unsuitable trendline type explains the mismatch. A trendline is not required to pass through every point.

The equation or R-squared is missing or confusing: Reopen Format Trendline and tick the appropriate display option. Move the label if it overlaps the chart. If coefficients look too rounded, increase their displayed decimal places. Remember that an R-squared value is meaningful only in the context of the chosen model and data.

Frequently Asked Questions

Which trendline is best for most Excel charts?
There is no universally best type. Linear is a sensible starting point when the points have a roughly straight pattern, but choose a different type when the observed shape and subject matter support it.

How do I know whether to use linear or exponential?
Use linear when equal x-steps correspond to roughly equal changes in y. Consider exponential when equal x-steps correspond to roughly equal percentage changes, and confirm that the y-values meet Excel’s positive-value requirement.

Should I choose the trendline with the highest R-squared?
No. Use R-squared as one comparison, not the decision rule. A high-order polynomial can raise it while overfitting. Check the plotted shape, residual pattern, data quality, and whether the model makes sense.

What is the difference between a trendline and a moving average?
A trendline fits a selected mathematical shape to the series. A moving average smooths neighboring observations over a chosen period. The latter is useful for showing a short-term underlying pattern but is not the same kind of fitted relationship.

Can I use an Excel trendline to predict future values?
You can extend a trendline with the Forecast options, but that is an extrapolation based on the fitted shape. Use caution, especially when conditions may change or the forecast extends far beyond the observed x-values.

Why does my trendline equation not match every point?
Most trendlines estimate a best fit rather than connecting each observation. The differences between points and the fitted line are expected; they are the residuals. A trendline is intended to summarize a pattern, not reproduce every measurement.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.