Excel does not have a worksheet function named TRENDLINE. To draw a trendline, select a chart and choose Chart Design > Add Chart Element > Trendline; to calculate predicted values in cells, use Excel’s TREND function or FORECAST.LINEAR.
What Excel means by a trendline
A chart trendline is a line or curve added to a data series to make its general direction easier to see. It can help show whether values tend to rise, fall, or remain relatively steady, even when individual observations fluctuate. The line is calculated from the selected series; it does not change the original data in the worksheet.
The word “function” can therefore mean two different things in Excel. The chart command creates a visual trendline, while worksheet functions calculate values from a trend model. For a straightforward linear forecast, TREND returns values calculated from existing x- and y-values. Choosing between the chart feature and the function depends on whether the goal is to explain a pattern visually or produce numbers for further calculations.
How to add a trendline to an Excel chart
In Excel for Microsoft 365 on Windows, first create a chart and make sure the series you want to analyze is included. Click the chart, then use this ribbon path to add a trendline and open its settings:
- Select the chart, then choose Chart Design > Add Chart Element > Trendline.
- Choose a suggested type, such as Linear, or select More Trendline Options to open the formatting pane.
- In the pane, select the data series and trendline type. Adjust any options that apply, such as a polynomial order or forecast length.
- To show the model on the chart, select Display Equation on chart. Select Display R-squared value on chart if you also want that statistic displayed.
You can also select the chart, use the Chart Elements button beside it, and turn on Trendline. The chart’s plus button is a quick way to add or remove common elements, while the formatting pane gives access to more settings. To format or remove a line later, select it on the chart or return to the Trendline menu. These actions affect the chart display, not the underlying worksheet values.
These paths describe Excel for Microsoft 365 on Windows. Excel for Mac has chart-design controls as well, though the layout and labels can vary by release. Excel for the web has fewer chart-editing controls than the desktop app; if a particular trendline setting is missing, open the workbook in desktop Excel.
Trendline types and when they fit
Excel offers several trendline models. The choice should reflect the shape and meaning of the data, not simply whichever option makes the line look most convincing.
- Linear: Fits a straight line and is a useful starting point when values change by roughly a constant amount as x increases.
- Exponential: Models a curve that rises or falls at a rate related to its current size. It requires positive y-values.
- Logarithmic: Models a pattern that changes quickly at first and then levels off, or the reverse. The x-values must be positive.
- Polynomial: Fits a curve with one or more bends. Excel lets you set its order; a higher order can follow more turns, but may also overfit a small dataset.
- Power: Models a relationship in which y changes according to a power of x. The data must meet the positive-value requirements for this model.
- Moving Average: Smooths short-term variation by averaging a chosen number of neighboring observations. It is useful for seeing a changing level, but it is not the same kind of fitted equation as a linear trendline.
For example, monthly revenue that increases by about the same dollar amount each month may suit a linear model. A quantity that grows by a fairly steady percentage may look exponential instead. A moving average can make a noisy sequence easier to inspect, but it should not be described as proof that the underlying process follows a particular growth law.
How a linear trendline works
A linear trendline uses the least-squares method to find the straight line that minimizes the sum of the squared vertical differences between the observed y-values and the line’s predicted values. The line has the form y = mx + b, where m is its slope and b is its intercept. A positive slope means the fitted values rise as x increases; a negative slope means they fall.
Excel’s chart line and the TREND function use this kind of linear relationship when a linear model is selected or implied. The x-values are the explanatory values, such as time or quantity, and the y-values are the observations being modeled. If the x-values are not supplied to TREND, Excel assumes a sequence of equally spaced values. That assumption is reasonable only when the observations really are evenly spaced.
A chart’s horizontal axis matters. A line chart often treats its labels as equally spaced categories, even if the labels look like dates or numbers. If the actual gaps between x-values are meaningful or uneven, use an XY (Scatter) chart so Excel positions observations according to their numeric x-values. This can change the apparent shape of the trend.
Use the TREND worksheet function
The syntax is TREND(known_y's, [known_x's], [new_x's], [const]). The first argument contains observed results; the optional x-values identify where those results occurred; new x-values specify the positions where you want predictions. The optional const argument controls whether the intercept is calculated normally or forced to zero. In most ordinary analyses, leave it out so Excel estimates the intercept.
Suppose A2:A5 contains x-values 1, 2, 3, and 4, while B2:B5 contains y-values 10, 12, 13, and 15. A6:A7 contains the future x-values 5 and 6. With x-values in A2:A5, observed y-values in B2:B5, and new x-values in A6:A7, enter:
=TREND(B2:B5,A2:A5,A6:A7)
In Microsoft 365, the formula returns two results and spills into the cells below it if they are empty. The fitted line for these four points has a slope of 1.6 and an intercept of 8.5, so the predictions are 16.5 at x = 5 and 18.1 at x = 6. For example, 1.6 × 5 + 8.5 = 16.5. The values are estimates from a straight-line model, not observed outcomes.
If the observations are evenly spaced and you want fitted values for the known observations rather than new x-values, the known-y range alone can be used. With the same observed y-values in B2:B5 and equally spaced positions assumed, enter:
=TREND(B2:B5)
In current Microsoft 365 Excel, this returns fitted values for the four observations and spills into adjacent cells. In older Excel versions that do not support dynamic arrays, an array formula may need to be entered across a selected range. The function produces a linear fit; it does not automatically choose an exponential, polynomial, or other chart trendline type.
Chart trendline or FORECAST.LINEAR?
Use a chart trendline when the goal is to communicate the overall pattern alongside the source data. It is quick to add, can display the fitted equation, and updates when the chart’s data changes. Use TREND when you need several fitted or projected values in worksheet cells, such as values to compare with a target or feed into another calculation.
FORECAST.LINEAR is another worksheet option for estimating one y-value from a linear relationship. With x-values in A2:A5, observed y-values in B2:B5, and a target x-value of 5 in A6, enter:
=FORECAST.LINEAR(A6,B2:B5,A2:A5)
For the sample data above, this returns 16.5. FORECAST.LINEAR is convenient for a single target x-value; TREND is convenient when you want predictions for several x-values at once. Neither function adds a line to a chart. Conversely, a displayed chart equation is not a worksheet formula that automatically fills a prediction table.
Read the equation and R-squared carefully
Displaying the equation on a chart can help explain a linear model, but the equation shown is rounded for display. If you copy its rounded slope and intercept into a calculation, your results may differ slightly from Excel’s internal fit. Use TREND, FORECAST.LINEAR, or suitable regression functions when you need calculated values rather than estimates copied from chart text.
R-squared, often written as R², describes how closely the fitted model accounts for variation in the observed y-values under the chosen model. A value nearer 1 indicates a closer fit to those observations, but it does not establish that the model is appropriate, that one variable causes another, or that future predictions will be accurate. A high R² can also result when a flexible polynomial follows a small set of points too closely.
Inspect the data and the residual differences between observed and fitted values, not just the statistic. Look for patterns the model misses, unusual observations, and whether the fitted relationship makes sense for the subject. Extrapolating far beyond the measured x-values is especially uncertain: a trend that describes the past may not continue indefinitely.
Common trendline problems
- The option is unavailable: Check that a chart and a data series are selected. Trendline support depends on chart type and settings; a 2-D XY (Scatter) chart is a practical choice for numeric x-values. Some chart types, including 3-D charts, do not support trendlines.
- The line looks wrong: Confirm that Excel is using the intended series and that the x-axis represents the spacing you expect. For uneven numeric intervals, use an XY (Scatter) chart rather than relying on equally spaced category labels.
- A model cannot be applied: Some models require positive values. Review the data for zero or negative entries when using exponential, logarithmic, or power fits; the relevant restrictions depend on the model and which values are used.
- The forecast is implausible: Check for too few observations, outliers, a changing pattern, or extrapolation well beyond the data. A mathematical fit is not a guarantee that the same process will continue.
- The equation differs from a manual calculation: The equation printed on the chart may be rounded. Use a worksheet function for calculations, and confirm that your x- and y-ranges contain corresponding observations in the same order.
Frequently Asked Questions
What is the Excel formula for a trendline?
There is no worksheet function named TRENDLINE. Use the chart’s Trendline command to draw one, or use TREND for linear fitted or predicted values in cells. FORECAST.LINEAR can calculate a single linear prediction.
How do I add a trendline in Excel?
Select the chart, then choose Chart Design > Add Chart Element > Trendline. Choose a type or open More Trendline Options to adjust the model and display its equation or R-squared value.
Can I extend a trendline into the future?
Yes. In the Trendline formatting pane, use Forecast Forward to extend it beyond the existing data. The amount is measured in x-axis units, so check that the axis scale and the chosen number of units match your intended forecast period.
Which trendline should I choose?
Start with a linear trendline if a straight-line change is plausible, then compare the data’s shape and subject matter with other models. A better visual fit alone does not prove that a more complex model will predict future values better.
Does an Excel trendline change my data?
No. A chart trendline is a visual model, and worksheet functions return calculated results in cells. Neither overwrites the original observations unless you separately edit or replace those cells.
Related
- Excel Text to Columns: How to Make Results Vertical
- Excel Table Structured References: Rules, Examples & Tips
- Excel Take Data From a Table: Methods and Examples
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pivot Chart: How to Remove Filter Buttons EasilyTo remove filter buttons from an Excel PivotChart in Microsoft 365 for Windows, select the chart and choose…
- Excel Waterfall Chart Data Labels: Add and Format ThemTo add labels to an Excel waterfall chart in Microsoft 365 for Windows, select the chart, then choose…
- Excel Bar Graph Grouping: Create Clustered BarsTo group bars in Excel, arrange each group as a separate data series, select the category labels and…
- Pivot Table in Excel Not Working? Fix Common CausesIf a PivotTable in Excel is not working, first select a cell inside it and choose PivotTable Analyze…
- Excel Pivot Table Value Filter Not Working? FixesIf an Excel PivotTable value filter is not working, first confirm you applied it to a row or…
- Excel Mini Chart in Cell: Add and Format SparklinesIn Excel, a mini chart displayed in a worksheet cell is called a sparkline. In Excel for Microsoft…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.