To create a trendline in Excel, select a chart, then choose Chart Design > Add Chart Element > Trendline and pick a type, such as Linear. If you have not made a chart yet, arrange your data in columns, insert a chart, and add the trendline to the data series. A trendline helps show the overall direction or pattern in your data; it does not prove why the pattern exists or guarantee what will happen next.
The steps below are for Microsoft Excel for Microsoft 365 on Windows. The chart tools are similar on Mac, though some controls may be arranged differently. Excel for the web has fewer chart-formatting options; if you cannot find a trendline control there, open the workbook in the desktop app.
What You’ll Need
- A worksheet with at least two meaningful data points in a series. A trendline is more useful when you have several observations.
- A chart that supports trendlines, such as a scatter, line, column, or bar chart. A scatter chart is usually the clearest choice when you want Excel to use numeric X and Y values.
- Consistent data: put X values in one column and the corresponding Y values in another, with a header in each column. Remove blank rows and check that numbers are stored as numbers, not text.
Step-by-Step
- Organize and select the data. Put the values that will appear on the horizontal axis in one column and the measured values in the next. For example, enter Month in A1 and Sales in B1, then list each month and its sales below. For an X-Y relationship, use headers such as Hours and Distance. Select the headers and all the data rows. If the columns are not next to each other, you can make the chart first and add or edit a series afterward, but adjacent columns are simpler.
- Insert a suitable chart. For paired numerical values, go to Insert > Insert Scatter (X, Y) or Bubble Chart and choose a scatter option, such as Scatter with only Markers. Excel uses the first selected column for X and the second for Y. For values grouped by dates or categories, a line or column chart may communicate the pattern more clearly. Check the axes after insertion: if the X-axis shows row numbers or the wrong labels, right-click the chart, choose Select Data, select the series, and choose Edit to assign the correct series X values and series Y values.
- Select the data series in the chart. Click one of the plotted markers, bars, or line segments to select the whole series, not just a single point. The chart should show selection handles or highlight the series. If the chart has several series, click the one you want to analyze. A trendline is attached to a series, so you can add separate trendlines to separate series if that is useful.
- Add a trendline. With the chart selected, open Chart Design > Add Chart Element > Trendline, then select Linear for a straight-line fit. You can also use the Chart Elements button, shown as a plus sign next to the chart in many Windows layouts: point to Trendline, use its arrow, and select a type. If Excel asks which series to use, choose the series you selected. The trendline appears over the data.
- Choose a different model if the pattern calls for it. Open Chart Design > Add Chart Element > Trendline > More Trendline Options to open the Format Trendline pane. Choose from Linear, Exponential, Logarithmic, Polynomial, Power, or Moving Average. Linear fits a straight trend; Polynomial can curve to follow a changing pattern; Moving Average smooths short-term variation. Exponential, Logarithmic, and Power models have data restrictions, so Excel may not offer them or may not fit them when values are unsuitable. Avoid choosing a curved model simply because it looks closer to every point.
- Set the trendline options. In the Format Trendline pane, choose the series and trendline type, then adjust the available settings. For a Polynomial trendline, set the order to control the curve’s complexity; a higher order can follow more bends but may overfit the observations. For a Moving Average, set the period, which determines how many points are averaged for each smoothed value. You can also set a forward or backward forecast distance, or set an intercept for applicable models. Forecasting extends the fitted line beyond the observed data; it is an extrapolation, not a promise that future results will follow it.
- Show the equation or R-squared value if useful. In the Format Trendline pane, select Display Equation on chart or Display R-squared value on chart. The equation describes the fitted relationship and can be useful for estimating values within a sensible range. R-squared indicates how closely the model fits the plotted observations, with values nearer 1 indicating a closer fit for that model. It does not show that one variable causes another, and a high value does not automatically make a model appropriate. Excel does not provide these displays for every trendline type, including Moving Average.
- Format and review the result. Use the paint bucket or line settings in the Format Trendline pane to adjust line color, width, or dash style so the trendline is distinct from the data. Add clear chart and axis titles from Chart Design > Add Chart Element if the meaning or units are not obvious. Check that the trendline follows the overall pattern rather than hiding important outliers or changes. Save the workbook when the chart is ready.
On Excel for Mac, select the chart series and look for the Chart Design tab and its Add Chart Element controls, or use the chart’s context menu to format the trendline. The pane layout can differ from Windows, but the trendline types and main settings are similar. On a Mac, use Command in place of Ctrl for common selection shortcuts; the menu path is more reliable than relying on a shortcut for adding a trendline.
Common Mistakes
- Using a line chart for paired numeric X values. A line chart treats its horizontal axis as categories, which may space values evenly even when the numbers are not evenly spaced. Use a scatter chart when the actual numeric spacing between X values matters.
- Adding the trendline to the wrong series. A chart with multiple series can make it easy to select the wrong one. Confirm the selected series before adding the line, and check the legend or series name in the Format Trendline pane.
- Expecting a trendline to connect every point. A trendline summarizes a broad pattern; it is not the same as the series line that connects observations. If you want to show the sequence of data points, format the data series itself.
- Using a model that the values cannot support. Exponential and Power trendlines require positive values, and Logarithmic requires positive X values. If data include zero or negative values, choose a suitable alternative rather than altering the data just to make a model available.
- Treating the equation or R-squared as proof. A fitted equation can produce misleading estimates outside the data range, and R-squared alone does not establish that a model is useful or that the relationship is causal. Consider the data context and the shape of the chart.
- Including totals, blanks, or text in the plotted range. Extra rows or numbers stored as text can distort the chart or omit observations. Select only the intended data and verify the plotted points before interpreting the line.
Tips
- Start with a scatter chart for analysis. When both variables are numeric, it makes the relationship between each X-Y pair explicit and usually makes the choice of trendline easier to assess.
- Keep the chart readable. Use markers for the observations and a contrasting, slightly thicker line for the trendline. If the chart has several series, use a separate color for each and avoid adding trendlines that make the chart difficult to interpret.
- Use polynomial order sparingly. Begin with a linear model, then consider a low-order polynomial only if the data show a genuine curve. A complex curve may match the existing points closely while predicting poorly beyond them.
- Use moving averages to reveal short-term patterns. A larger period produces more smoothing but hides more local movement. Try a period that makes sense for the frequency of your data, such as a 7-point period for daily observations across a weekly cycle, and explain the choice in the chart or notes.
- Keep forecast extensions modest. A trendline extended far beyond the observed range can imply more certainty than the data justify. Label the forecast distance and treat the extension as a visual projection.
- Make the chart self-explanatory. Include units in axis titles and use a descriptive chart title. If you show an equation, move it so it does not cover points, and format its number of decimal places to suit the data.
Troubleshooting
- Trendline is missing from the menu: Confirm that you selected a data series and that the chart type supports trendlines. Pie, doughnut, radar, 3-D, and some stacked chart types do not support them. Change the chart to a supported 2-D type, such as Scatter, Line, Column, or Bar, then try again. If you are using Excel for the web and the option is unavailable, open the file in desktop Excel.
- The trendline does not match the expected direction: Check that the correct series is selected and that X and Y values are assigned to the intended columns. A chart can look plausible even when Excel has interpreted categories or series incorrectly. For a scatter chart, use Select Data > Edit to inspect the X and Y ranges.
- Excel will not add an exponential, logarithmic, or power trendline: Review the plotted values for zero, negative, or nonnumeric entries. These model types have restrictions, and invalid values can prevent Excel from fitting the line. Choose Linear or another model that fits the data, or investigate and correct the source data if it was entered incorrectly.
- The equation is hidden or hard to read: Reopen More Trendline Options and check Display Equation on chart. If the checkbox is unavailable, the selected trendline type may not support an equation display. If the equation overlaps the chart, click its label and drag it to an open area.
- R-squared looks unexpectedly low or high: Confirm the series and model, check for outliers and missing data, and verify that the chart uses the intended values. A different model can change the fit, but do not select a model only to maximize R-squared; make sure its shape makes sense for the situation.
- The trendline disappears after changing the chart: Some chart-type changes remove or alter chart elements. Re-select the series and add the trendline again, then review its type and formatting.
Frequently Asked Questions
Can I add a trendline to a chart with multiple series?
Yes. Select one series and add its trendline, then repeat for another series if needed. Each trendline belongs to a particular series, so verify the selection each time.
Which trendline should I use?
Use Linear when the points show an approximately straight overall pattern. Consider another type only when the data support its shape and assumptions. A scatter chart and a look at the observations are better starting points than choosing a model based only on a high R-squared value.
Can I make a trendline forecast future values?
You can set a forward forecast in the Format Trendline pane. This extends the fitted line beyond the data, but it does not account for new events or guarantee future results. Use it cautiously, especially when extending far beyond the observed range.
Can I add a trendline to a pivot chart?
Excel does not support trendlines on PivotCharts in the same way as standard charts. If the trendline command is unavailable, create a regular chart from the PivotTable results, then select a series and add the trendline.
How do I remove a trendline?
Click the trendline to select it and press Delete. You can also use Chart Design > Add Chart Element > Trendline > None where that option is shown. Removing the trendline does not delete the underlying worksheet data.
Can I show the trendline equation in a worksheet cell?
Excel’s chart option displays the equation as a chart label, which you can select and copy. If you need calculated coefficients in worksheet cells, use Excel’s regression tools or statistical functions; those require a separate setup and should match the model used for the chart.
Related
- How to Add a Header in Excel When Printing a Worksheet
- How to Add Headers in Excel on SharePoint and Format Them
- Changing Date Format in Excel: Steps, Options, and Fixes
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pie Chart: Group Small Categories into OtherTo group data in an Excel pie chart, first summarize the values you want to show, then chart…
- Excel Pie Chart: How to Break Its Link in Microsoft WordTo break a linked Excel pie chart in Word for Microsoft 365 on Windows, open the document and…
- Google Sheets Pivot Tables: Create, Customize, and RefreshYes, you can create a pivot table in Google Sheets using Insert > Pivot table. Select a range…
- Edit Legend in Excel Line Charts: Names, Position, and StyleTo edit a legend in an Excel line chart, select the chart and go to Chart Design >…
- Excel Combo Pivot Chart: How to Build One in Microsoft 365Excel does not support a true combo chart as a PivotChart: you cannot combine chart types such as…
- Excel Gantt Chart Template XLS: How to Find or Build OneYes—an Excel Gantt chart template can be opened or built in Excel, but “XLS” is the older Excel…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.