To draw a trendline in Excel for Microsoft 365 on Windows, select a chart, click the green Chart Elements button (+), check Trendline, and choose a type. For more control, use Chart Design > Add Chart Element > Trendline > More Trendline Options to select the model, format its line, and display its equation or R-squared value.
What a trendline shows
A trendline is a line or curve fitted to the data in a chart. It summarizes the overall direction of a series, which can make a pattern easier to see when individual observations vary. A rising linear trendline, for example, suggests that higher values of the horizontal variable tend to be associated with higher values of the vertical variable.
A trendline is a statistical summary, not a connection between every pair of observations. It does not prove that one variable causes another, guarantee future results, or explain why the data changes. It is most useful when the underlying observations have a meaningful order or relationship, such as monthly sales over time, distance and travel time, or advertising spend and leads.
How to add a trendline in Excel
- Arrange the data in columns or rows, with a clear heading for each series. For a time-based chart, put the dates or time periods in one column and the measured values in another.
- Select the data and create a suitable chart from the Insert tab. For numeric x-values, choose Insert > Insert Scatter (X, Y) or Bubble Chart > Scatter. For values across categories or dates, a line chart may be more appropriate.
- Click the chart to select it. Click the Chart Elements button (+) beside the chart and check Trendline. Excel adds a default trendline to the selected data series.
- To change the model or its settings, click the arrow beside Trendline and choose More Options. Alternatively, use Chart Design > Add Chart Element > Trendline > More Trendline Options.
- In the Format Trendline pane, choose a trendline type and adjust its options. To show the fitted equation or R-squared value on the chart, check the corresponding display option.
Excel generally adds a trendline to one series at a time. If the chart contains several series, select the particular series before adding or formatting its line. On Excel for Mac, select the chart and use Chart Design > Add Chart Element > Trendline; the available controls are similar, though the chart-selection buttons and pane layout can differ. Excel for the web has a more limited chart-editing interface, so some detailed trendline settings may require opening the workbook in the desktop app.
Choose the right chart and data layout
The chart type affects what Excel treats as the horizontal values. An XY scatter chart uses numeric x-values, so uneven intervals are represented as uneven distances. This is usually the clearest choice when you are comparing two measured quantities, or when dates are irregularly spaced. A line chart usually treats its horizontal axis as categories; equally spaced labels can therefore appear equally spaced even if the dates between them are not.
For example, suppose measurements were taken on days 1, 2, 10, and 30. A scatter chart places those observations at their actual numerical positions. A line chart may show four evenly spaced categories instead. If the intervals matter to the question, use a scatter chart and make sure the x-values are numeric Excel values rather than text labels.
Trendlines are available for several common two-dimensional chart types, including scatter, line, column, and bar charts. Excel does not support them for every chart design; 3-D, pie, doughnut, radar, and stacked charts are examples where trendlines are not available. If the Trendline option is missing or disabled, try a supported two-dimensional chart, and check that a data series—not just the chart background—is selected.
Which trendline type should you choose?
Choose a model based on the shape and meaning of the data, not simply on which curve looks closest. A more flexible curve can fit the observed points better while giving a misleading impression of what will happen outside the data range.
- Linear: Use when the data changes at a roughly constant rate. The model is y = mx + b, where m is the slope and b is the intercept. It is a sensible starting point for a steady increase or decrease.
- Exponential: Use when change is proportional to the current value, producing a curve that rises or falls increasingly quickly. Excel requires positive y-values for an exponential trendline.
- Logarithmic: Use when values change quickly at first and then level off, or the reverse. The x-values must be positive.
- Power: Use when a relationship follows a power pattern, such as a quantity scaling with another quantity. Both x- and y-values must be positive.
- Polynomial: Use when the data has one or more bends. Excel lets you set the order from 2 through 6. Higher orders can create dramatic curves that fit a small dataset without representing a reliable underlying pattern.
- Moving average: Use to smooth short-term fluctuations and make a local direction easier to see. The period controls how many points are averaged. It is a smoothed series rather than a fitted equation, so it serves a different purpose from the other models.
If the data has a clear physical or business rationale, let that guide the choice. For example, a steady per-month increase may suit a linear model, while a process that grows by a consistent percentage may suit an exponential model. If you do not know the process, inspect the scatterplot, test a simple model first, and treat more complex fits cautiously.
Show and interpret the equation and R-squared
In the Format Trendline pane, select Display Equation on chart to place the fitted equation on the chart. For a linear model, Excel displays a form such as y = 2x + 1. The coefficient of x is the estimated change in y for a one-unit increase in x; the constant is the estimated y-value when x is zero. That intercept may not have a useful real-world meaning if zero is outside the observed range.
Select Display R-squared value on chart to show a measure of how closely the fitted line accounts for variation in the plotted data. For a simple linear fit, a value nearer 1 indicates a closer fit to the observations than a value nearer 0. It is not a measure of causation, does not show that the model is appropriate, and should not be used alone to choose a trendline. A high-order polynomial can have a high R-squared while behaving poorly beyond the observed data.
The equation printed on a chart may be rounded for display. If you use its coefficients to calculate results, small differences can arise because the underlying fitted coefficients are more precise than the labels shown. You can format the equation label to show more decimal places, but for exact worksheet calculations it is better to calculate the model from the source data.
Worked example: check a linear trendline in cells
Suppose A2:A6 contains x-values 1, 2, 3, 4, and 5, and B2:B6 contains y-values 3, 5, 7, 9, and 11. A scatter chart with a linear trendline should show the equation y = 2x + 1: each one-unit increase in x corresponds to a two-unit increase in y. The following worksheet functions let you calculate and use those coefficients independently of the chart.
With x-values in A2:A6 and y-values in B2:B6, enter this in a cell to calculate the slope:
=SLOPE(B2:B6,A2:A6)
The result is 2. With the same layout, calculate the intercept with:
=INTERCEPT(B2:B6,A2:A6)
The result is 1. Together, those results give the fitted equation y = 2x + 1. If D2 contains a new x-value of 6, and the original x- and y-values remain in A2:A6 and B2:B6, enter the following in another cell to calculate the corresponding fitted y-value:
=SLOPE($B$2:$B$6,$A$2:$A$6)*D2+INTERCEPT($B$2:$B$6,$A$2:$A$6)
The result is 13, because 2 × 6 + 1 = 13. This calculation extends the straight-line relationship; it does not establish that real observations will continue following it. The dollar signs keep the source ranges fixed if you copy the formula to other rows.
Format the line and extend a trend
In the Format Trendline pane, use the line-format controls to change the line’s color, width, or dash style. Choose a line that is visually distinct from the data series without obscuring the observations. You can also use the Forecast controls to extend a supported trendline forward or backward. Forecast distances are measured in units along the horizontal axis, so they are easiest to interpret when that axis has meaningful numeric spacing.
For example, on a monthly chart, a forward forecast of three units can represent three months if the x-axis is set up as a suitable date or numeric scale. On a category chart, do not assume that the forecast distance corresponds neatly to calendar time; check how Excel is spacing the categories. Extending a line is an extrapolation, meaning it projects beyond the observations used to fit it. Uncertainty generally increases farther from the measured data, and a trendline alone does not provide a complete forecast interval.
Common trendline problems
- The option is unavailable: Confirm that a supported chart type is selected and that you clicked a data series. Convert an unsupported chart to a two-dimensional scatter, line, column, or bar chart if that suits the data.
- The curve will not appear: Check the model’s data requirements. Exponential and power trendlines cannot be fitted to data with zero or negative values under Excel’s restrictions; logarithmic models need positive x-values. Remove or correct unsuitable values only when doing so is justified by the data, not merely to force a fit.
- The line looks wrong: Verify that the intended x-values and y-values are assigned to the series. A line chart may treat dates or numbers as categories, while a scatter chart uses numeric x-values. Switching to scatter can correct misleading spacing.
- The equation does not match a hand calculation: Check that the same observations are included, confirm the selected series, and increase the equation label’s displayed decimal places. Rounded chart coefficients can differ from full-precision calculations.
- The forecast looks implausible: A trendline describes the historical pattern under the chosen model. It cannot account automatically for seasonality, policy changes, one-off events, or a process that changes over time. Avoid extending it far beyond the data without a reason to expect the same pattern to continue.
Frequently Asked Questions
Can I add a trendline to a table without making a chart?
A chart trendline is a chart element, so you need a chart to display it. You can calculate a fitted line in worksheet cells with functions such as SLOPE and INTERCEPT, then use those results in calculations or a separate chart.
How do I add a trendline to only one series?
Select the chart, then click the specific series you want to analyze before using Chart Elements > Trendline. If the chart has multiple series, confirm that the new line is attached to the intended one; each series can have its own trendline settings.
Can Excel show the trendline formula?
Yes. Open More Trendline Options and check Display Equation on chart. The displayed coefficients may be rounded, so show additional decimal places or calculate the coefficients in cells if precision matters.
What is the difference between a trendline and a moving average?
A trendline fits a selected mathematical model to the series, such as a straight line or polynomial curve. A moving average smooths nearby observations using a chosen period. The first summarizes a modelled relationship; the second helps reveal local movement by reducing short-term variation.
Can a trendline predict future values?
You can extend a trendline with the Forecast settings, but the extension is a projection of the selected model, not a guarantee. It assumes the historical relationship remains relevant and does not automatically account for seasonal patterns, changing conditions, or uncertainty.
Related
- How to Data Validate in Excel with Drop-Down Lists
- How to Add Colour to Data Validation Cells in Excel
- Data Validation in Excel: Rules, Lists, and Setup Tips
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Group Dates into Quarters in Excel: A Step-by-Step GuideTo group dates into quarters in Excel, put the date field in a PivotTable, right-click a date, choose…
- Track Changes in Word on Mac: Review and Accept EditsTo track changes in Word on a Mac, open your document, select Review > Track Changes, and then…
- How to Calculate an Average in an Excel ColumnIf your numbers are in B2:B10, select an empty cell and enter =AVERAGE(B2:B10), then press Enter. Excel returns…
- How to Clear the Cache in Outlook on Windows and MacTo clear Outlook’s local cache, use the option that matches what is causing trouble: in classic Outlook for…
- Excel Find and Replace Asterisk CharactersTo find a literal asterisk in Excel for Microsoft 365 on Windows, press Ctrl+H, enter ~* in Find…
- Inventory Spreadsheet Template for Google Sheets: GuideYes. You can make an inventory spreadsheet template in Google Sheets by creating columns for each item’s identifier,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.