To format a trendline in Excel for Microsoft 365 on Windows, select the chart, choose Chart Design > Add Chart Element > Trendline > More Trendline Options, then use the Format Trendline pane to choose a model and adjust its settings. To change only its appearance, right-click the trendline, select Format Trendline, and use the Line options.
Add a trendline and open its settings
A trendline is a line or curve fitted to a chart’s data. Start with a chart that contains a data series, then add the trendline in either of these ways:
- Select the chart and go to Chart Design > Add Chart Element > Trendline. Choose a listed type for a quick addition, or select More Trendline Options to open its settings.
- Right-click the data series you want to analyze, choose Add Trendline, and then select a trendline type in the pane that opens.
To format an existing trendline, click the line once and right-click it, then choose Format Trendline. If the pane title says Format Data Series instead, Excel selected the plotted data rather than the trendline; click the trendline itself and try again. A trendline can be difficult to select when it overlaps the series, so selecting it through the chart’s selection controls or clicking a visible section of the line can help.
On Excel for Mac, the chart-design tab and formatting pane provide similar options, although labels and placement can vary by release. Excel for the web has a more limited chart-editing interface; if a setting is missing, open the workbook in the desktop app.
Choose a chart that fits the data
Trendlines are most useful when the chart makes the relationship between values clear. An XY (Scatter) chart is usually the right choice when both the horizontal and vertical axes represent numeric measurements. It plots each x-value at its actual numeric position, which matters when intervals are unequal. For example, measurements taken at days 1, 2, and 10 should not be treated as equally spaced categories if the actual time gap is important.
A line chart can work well for values measured at regular intervals, such as monthly sales. Its horizontal axis is generally a category axis, so Excel treats the points as successive categories rather than using their date gaps as numeric distances. If the spacing between dates or x-values matters, use a scatter chart instead. Column, bar, area, and other supported two-dimensional charts may also offer trendlines, but the chart type and data arrangement affect which options are available.
Excel does not offer trendlines for every chart format. Three-dimensional, stacked, pie, doughnut, and radar charts are examples where a standard trendline is not available. If the Trendline command is unavailable, check the chart type and whether the chart has a suitable data series. In a chart with several series, add a trendline separately to each series you want to analyze; one series’ trendline does not describe all the chart’s data.
Understand the trendline types
In the Format Trendline pane, under Trendline Options, choose a model that makes sense for the pattern and the subject matter. Excel’s main choices are:
- Linear: A straight line that represents a steady increase or decrease. It is a reasonable starting point when the change per step appears roughly constant.
- Exponential: A curve for values that change at a rate proportional to their current level, such as some patterns of growth or decay. This model requires positive values.
- Logarithmic: A curve that can rise or fall quickly and then level out. It requires positive x-values.
- Polynomial: A curve that can represent bends in the data. Excel lets you set its order; a higher order can follow more turns, but may also fit random variation rather than a meaningful pattern.
- Power: A curve for relationships where one variable changes as a power of another. It requires positive x- and y-values.
- Moving Average: A smoothed line based on averages across a chosen number of points. It emphasizes the local direction of a series rather than fitting a single equation in the same way as the other models.
Do not pick a type just because it creates the smoothest-looking curve. A model should suit the data and the process being measured. For example, a curved fit to a small set of noisy measurements can look convincing while giving a poor explanation of what is happening.
Change the line’s appearance
Formatting changes how the trendline looks; it does not change the model or the underlying data. With the Format Trendline pane open, select the paint-bucket or Fill & Line section, then expand Line. Depending on the Excel build and the selected chart, you can adjust options such as line color, width, dash style, and transparency. A contrasting solid line is often easier to read than a faint line that blends into the data series.
Keep the chart’s meaning intact while styling it. A trendline is not the same as the original observations, so distinguish it with a different color or dash pattern instead of making it look like another measured series. If the chart has several data series, use a consistent, readable style for each trendline and make sure the legend or a note identifies what each one represents.
To change the trendline’s label, use the Trendline Name options in the pane. Excel can use an automatic name or a custom one. A descriptive custom name, such as “Long-term fit,” can be clearer than a generic label, particularly when the chart contains multiple series. To remove a trendline, select it and press Delete, or use Chart Design > Add Chart Element > Trendline to manage the chart element where that option is available.
Show an equation, R-squared, or forecast
Under Trendline Options, Excel can display the fitted equation on the chart and, where available, show the R-squared value. These settings are useful when a reader needs to see the model or assess how closely it follows the plotted values. The equation and the R-squared label are chart annotations; formatting their position or appearance does not improve the fit.
R-squared describes how much of the variation in the plotted y-values is accounted for by the fitted model in that context. It is not proof that the model is correct, that the relationship is causal, or that predictions beyond the observed data will be reliable. A high value can result from a model that is overly complex for a small dataset. Consider the data, the assumptions behind the model, and the chart’s purpose rather than treating one statistic as a verdict.
Excel also provides Forward and Backward forecast fields for extending a trendline beyond the observed x-range. These fields extend the fitted line; they do not add measured data points or guarantee that the pattern will continue. For instance, forecasting several years beyond a short run of monthly observations may be misleading if conditions can change. If the equation is displayed, remember that Excel rounds the digits shown on the chart. Using the rounded printed equation elsewhere can produce slightly different results from Excel’s fitted line.
Common problems and how to fix them
- The Trendline command is unavailable: Confirm that you selected a supported two-dimensional chart and a data series. If the chart is stacked or three-dimensional, change to a compatible chart type if that suits the data.
- The trendline looks wrong: Check that the series contains the intended values and that the chart type represents the x-values correctly. A line chart may space categories evenly even when dates are irregular; a scatter chart can better represent numeric spacing.
- An error appears for a nonlinear model: Review the input values. Exponential and power fits require positive values, and logarithmic fits require positive x-values. A model cannot be fitted appropriately if its mathematical requirements are not met.
- The line is hard to see or select: Increase its contrast or width in the Line settings. If Excel keeps selecting the data series, right-click a clearly visible section of the trendline or use the chart’s selection controls.
- The equation seems not to match: The displayed equation may be rounded, so calculations based on its visible coefficients can differ slightly. The model type, polynomial order, and selected series also affect the fit.
- The line changes when data changes: A trendline is calculated from the series, so editing or adding data can update it. That behavior is expected; a trendline is not a manually drawn annotation fixed independently of the data.
Example: choosing a trendline for monthly sales
Imagine a chart of monthly sales over two years. If sales rise by a similar number of units each month, a linear trendline may summarize the general direction. If the rate of increase grows as the sales level rises, an exponential model might be worth examining, provided the values meet its requirements and the pattern has a defensible explanation. If the actual monthly values fluctuate, a moving average can make the broad direction easier to see, but the selected period matters: a longer period smooths more detail and can lag behind a turning point.
In each case, preserve the original data series on the chart. The trendline summarizes or smooths; it should not be mistaken for the observations. Label the axis and units, identify the trendline when necessary, and avoid extending the line far beyond the period with data unless the purpose and limitations of the projection are clear.
Frequently Asked Questions
How do I change a trendline to a different type in Excel?
Right-click the trendline, select Format Trendline, and choose another option under Trendline Options, such as Linear, Polynomial, or Moving Average. Excel updates the fit using the selected type.
How do I make an Excel trendline thicker or change its color?
Select the trendline and open Format Trendline. In Fill & Line, expand Line and adjust the color or width. The change affects its appearance, not the calculated trend.
Can I add a trendline to a line chart?
Yes, if the chart type and series support trendlines. A line chart is useful for regularly spaced categories, but its category axis does not necessarily represent numeric gaps accurately. Use an XY scatter chart when actual x-value spacing matters.
Why can’t I add a trendline to my chart?
The chart may use an unsupported format, such as a stacked or three-dimensional chart, or you may have selected a chart element rather than a data series. Try a supported two-dimensional chart and select the series first.
Does a trendline predict future values automatically?
No. It summarizes a fitted pattern. The Forward forecast option extends the line beyond the plotted data, but it does not create observations or ensure future values will follow that pattern.
Related
- Excel Split Empty Cells Into Multiple Rows?
- Excel Split Cell Text Into Separate Cells: A Practical Guide
- Excel Split Cell Value by Delimiter: Methods and Tips
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- How to Edit the Chart Area in ExcelTo edit a chart area in Excel, select the chart, open the contextual Format tab, choose Chart Area…
- Excel Graph: How to Switch the X and Y Axes in a ChartTo switch the X and Y values in an Excel chart, use an XY (Scatter) chart, then go…
- Excel Histogram: Set Bins ManuallyTo set histogram bins manually in Excel for Microsoft 365 on Windows, select the chart’s horizontal axis, right-click…
- Pivot Chart Examples for Excel: Sales, Trends, and MoreUseful Excel PivotChart examples include monthly sales by region, expenses by department, inventory by product category, and support…
- Excel Chart With Two Different Scales: How to Make OneYes. In Excel for Microsoft 365 on Windows, put one data series on a secondary axis: select the…
- Excel Add Power Pivot to the Ribbon: Steps and FixesTo add Power Pivot to the ribbon in Excel for Microsoft 365 on Windows, select File > Options…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.