An Excel trendline is a line or curve added to a chart to summarize the general direction or pattern of a data series. It helps you see whether values tend to rise, fall, stay steady, or follow a curved pattern; it is a statistical fit to the charted data, not a guarantee of what will happen next.
What an Excel trendline represents
A trendline is a visual model of the relationship between plotted values. For example, if a chart shows monthly website visits, a trendline can make the overall increase easier to see even when individual months fluctuate. It is different from the line connecting points in a line chart: that connecting line joins observations in order, while a trendline summarizes the pattern across them.
Trendlines are most useful when the chart has a meaningful sequence or relationship. A time series might use dates on the horizontal axis and sales on the vertical axis. A scientific or operational chart might compare one measured quantity with another. In either case, the fitted line can help communicate the direction of the data and provide a starting point for analysis.
Excel calculates the trendline from the values in the chart’s data series. Changing the source values, the selected series, or the trendline type can change its shape. A trendline therefore describes a particular set of data under a particular model; it is not an independent measurement.
How Excel fits a trendline
Excel offers several mathematical models, each suited to a different kind of pattern. In broad terms, Excel chooses a line or curve that fits the selected series according to the chosen model. The resulting line can smooth visual complexity, but the model does not explain why the data changed or establish that one variable caused another.
A straight line is appropriate when the data changes at a roughly consistent rate. A curve may be more suitable when change accelerates, slows, or bends. A moving average works differently: it averages nearby values to make short-term fluctuations less prominent. It is a smoothing aid rather than a single fitted equation in the same sense as the other trendline types.
The chart’s horizontal axis matters. In an XY (scatter) chart, Excel uses numeric x-values and their spacing, so observations at x-values 1 and 10 are farther apart than observations at 1 and 2. In a line chart, the horizontal axis is generally treated as categories with equal spacing. If dates or measurements occur at irregular intervals, a scatter chart may represent their spacing more faithfully.
How to add a trendline in Excel
In Excel for Microsoft 365 on Windows, select a chart and use Chart Design > Add Chart Element > Trendline, then choose a type. To adjust the model or its display, choose More Trendline Options. Another route is to select the chart, open the Chart Elements button (the plus sign beside the chart), and check Trendline. The available controls can vary with the chart type and selected series.
- Make a chart with the data you want to examine, and click the specific data series to which the line should apply.
- Add a trendline using the chart controls. If the chart contains multiple series, make sure you chose the intended series rather than the chart area or another series.
- Open the trendline formatting options and select a model that makes sense for the data.
- Optionally set a forecast distance, change formatting, or display the equation and R-squared value when those options are available.
On a Mac, select the chart and use the Chart Design tab to add a chart element, or use the chart’s formatting controls to select and format the trendline. Ribbon labels and control placement can differ slightly by Excel release. Excel for the web may expose fewer chart-formatting options than the desktop app; if a specific trendline setting is unavailable, open the workbook in desktop Excel.
Excel trendline types and when to use them
Excel’s main trendline choices are linear, exponential, logarithmic, polynomial, power, and moving average. Choosing one just because it makes a chart look smooth can be misleading. Consider the shape of the data, the context, and whether the model makes sense beyond the observations shown.
- Linear: Fits a straight trend. Consider it when increases or decreases are broadly steady over the observed range, such as a gradual change in output per week.
- Exponential: Fits a curve that changes at a rate related to its current level. It can suit some forms of compounding growth or decay, but it is not appropriate for every series that rises quickly. The data must meet Excel’s requirements for this model.
- Logarithmic: Fits a curve that changes quickly at first and then levels off, or the reverse. It requires suitable positive x-values.
- Polynomial: Fits a curve with bends. In Excel, you can choose the order, up to six. A higher order can follow more turns in the plotted data, but may also create a curve that overfits a small or noisy sample.
- Power: Fits a curved relationship in which one quantity varies as a power of another. The data must meet the model’s requirements, including positive values.
- Moving average: Smooths a series by averaging values across a chosen number of periods. A larger period usually creates a smoother line but can hide brief changes or make turning points appear later.
Excel may disable a model or option when the chart or data is unsuitable. In particular, exponential, logarithmic, and power fits have restrictions on the values they can use. If Excel reports that it cannot calculate a trendline, inspect the source values for zeros, negative values, blanks, or nonnumeric entries and check the requirements of the selected model.
Choosing a model that fits the question
Start with the question, not with the menu. If you want to summarize a steady increase over time, try a linear trendline first. If the relationship has a clear bend, consider a curved model, but make sure that the bend is meaningful rather than a result of one unusual observation. If your goal is to reduce visual noise in a time series, a moving average may be clearer than a fitted curve.
For example, imagine quarterly orders that generally rise from 100 to 160 over several years but dip in one quarter. A linear line may summarize the broad upward direction without following every dip. If orders instead rise slowly at first and then accelerate, a linear line may understate the later change; an exponential model could be worth examining if its assumptions fit. The chart alone cannot establish why orders changed, and a model should not be selected solely because its curve looks persuasive.
Use enough observations to support the pattern you are trying to describe. A high-order polynomial applied to only a few points can bend sharply between them and suggest behavior that the data do not justify. It is also sensible to compare the line with the original points: the observations remain important, and the trendline should not make their spread or outliers disappear from consideration.
Equation, R-squared, and forecasting options
In the trendline options, Excel can display the fitted equation on the chart for many trendline types. The equation expresses the model Excel fitted and can be useful for understanding or reusing that relationship. Its coefficients depend on the selected series, model, and chart data. A displayed equation is not automatically a suitable forecasting formula for every purpose, and rounding the displayed coefficients can reduce calculation accuracy.
Excel can also display R-squared, a statistical measure of how closely the fitted model corresponds to the data under the model’s definition. A value closer to 1 indicates a closer fit within the observed data, but it does not prove causation, guarantee accurate future predictions, or establish that the chosen model is the right one. Comparing R-squared values across different kinds of models should be done thoughtfully rather than treating the largest value as an automatic winner.
Trendline options can extend the line forward or backward by a specified number of units. This is a visual extrapolation: Excel carries the selected model beyond the observed data. The farther the extension goes, the more the result depends on the assumption that the pattern will continue. A forecast line on a chart is not the same as a validated forecast, and unusual events or changing conditions can make extrapolation unreliable.
Common problems and misleading interpretations
A trendline can look authoritative because it is smooth and precisely drawn, but its meaning depends on data quality and model choice. Check for missing values, duplicated observations, extreme outliers, and inconsistent measurement intervals before interpreting it. An outlier can pull a fitted line toward itself, while a chart that omits relevant observations can make a weak pattern appear stronger.
Another common issue is using a line chart when the x-values are unevenly spaced numeric measurements. Because category positions in a line chart are equally spaced, the trendline may not reflect the actual distances between those measurements. Use an XY scatter chart when the horizontal values are genuine numeric quantities and their spacing matters.
Do not confuse correlation with cause and effect. If two variables move together, their trendlines do not show that one variable caused the other to change. Likewise, a trendline is not a promise that future values will follow the same path. Treat it as a concise description of a pattern in the available data, and use additional evidence for decisions that require stronger conclusions.
Trendline versus moving average and forecast
These terms are related but not interchangeable. A fitted trendline describes a series using a selected mathematical model. A moving average smooths local variation by averaging neighboring observations. A forecast estimates future values using a forecasting method and its assumptions. Excel’s chart forecast extension simply continues a selected trendline visually; it should not be confused with a full forecasting analysis.
Choose a trendline when you want to show a broad relationship or direction on a chart. Choose a moving average when the main purpose is to make short-term noise easier to see while preserving the underlying sequence. For planning, where seasonality, changing patterns, and uncertainty matter, a chart trendline alone may be insufficient. Consider a forecasting method designed for those features and assess its errors against past data.
Frequently Asked Questions
What does a trendline mean in an Excel graph?
A trendline is a fitted line or curve that summarizes the overall pattern of a chart’s data series. It can show a general direction or relationship, but it does not prove cause and effect or ensure future values will follow the line.
How do I show the trendline equation in Excel?
Select the trendline, open Format Trendline, and select the option to display the equation on the chart. The option is available for supported trendline types; the equation changes if you change the data or model.
What does R-squared mean on an Excel trendline?
R-squared indicates how closely the selected model fits the observed data according to that measure. A higher value can indicate a closer in-sample fit, but it does not prove that the model is meaningful or that it will predict future observations well.
Why can’t I add a trendline to my Excel chart?
Check that you selected a compatible chart and a data series, rather than the chart background. Trendlines are not available for every chart type, and some model choices require values that meet specific conditions. Switching to a supported two-dimensional chart or correcting unsuitable data may resolve the issue.
Which Excel trendline should I use?
Use a linear trendline for a broadly steady rate of change, a moving average to smooth short-term fluctuations, and a curved model only when the data and context support that shape. Compare the result with the plotted observations rather than choosing by appearance alone.
Related
- Excel Split Cell Contents by Delimiter
- Excel Split Cell Based on Character: Methods That Work
- Excel Split Cell at a Specific Character?
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Production Dashboard in Excel: Build and Track OutputYes. You can build a production dashboard in Microsoft Excel for Microsoft 365 by keeping production records in…
- How to Change the Key on a Pie Chart in Excel Step by StepTo change the key, or legend labels, on a pie chart in Excel, edit the category names in…
- Stacked Waterfall Chart in Excel: How It WorksExcel does not offer a built-in stacked waterfall chart: its native Waterfall chart plots one series, not a…
- Bubble Chart in Excel: Make Each Bubble a Different SizeYes. To make bubbles different sizes in an Excel bubble chart, give each data point its own size…
- Yearly Comparison Chart in Excel: Build It ClearlyTo make a yearly comparison chart in Excel, put each year in its own column and each measure…
- Excel Pivot Table Show Report Filter Pages ExplainedIn Excel for Microsoft 365 on Windows, select the PivotTable, open PivotTable Analyze > Options, and choose Show…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.