To display a trendline equation in Excel, select a chart, add a trendline, open its formatting options, and select Display Equation on chart. The equation summarizes the relationship between the chart’s data points, so you can see the model Excel fitted without calculating it separately. For the clearest result, start with a chart that has numeric X and Y values, such as an XY (Scatter) chart.
The steps below apply to Excel for Microsoft 365 on Windows. The same option is available in desktop Excel for Mac, though ribbon labels and pane placement may vary slightly. Excel for the web can have fewer chart-formatting controls; if you cannot find the equation option there, open the workbook in the desktop app.
What You’ll Need
- An Excel worksheet containing at least two paired observations: one set of X values and one set of Y values. More points usually make a trend easier to assess.
- A chart that supports trendlines. An XY (Scatter) chart is usually best when the horizontal values are numbers, dates, measurements, or other quantities.
- Excel for Microsoft 365 on Windows for the exact ribbon path below. In other desktop versions, look for the same Trendline options in the chart formatting controls.
Step-by-Step
- Check the data layout. Put the values you want to use as X and Y in separate columns, with one observation per row. For example, A1 could contain Month and B1 could contain Units; the observations would then sit in A2:B10. Keep the headings outside the values and make sure the paired values line up on the same rows. Remove blank rows within the data and check that values intended to be numeric are stored as numbers rather than text.
- Create or select a suitable chart. Select the headings and paired values, then choose Insert > Insert Scatter (X, Y) or Bubble Chart and pick a scatter style. A scatter chart treats the first selected numeric column as X and the second as Y. If you already have a chart, click it once to show the chart tabs and check that it plots the intended series. A line chart can also accept a trendline in many cases, but its horizontal axis may represent categories rather than numeric X values.
- Add a trendline. With the chart selected, use either of these Windows paths: click the Chart Elements (+) button beside the chart, point to Trendline, and choose More Options; or use Chart Design > Add Chart Element > Trendline > More Trendline Options. If Excel offers multiple series, make sure you are adding the trendline to the series you want to analyze.
- Choose the trendline type. The Format Trendline pane opens. Under Trendline Options, choose a model such as Linear, Exponential, Logarithmic, Polynomial, Power, or Moving Average, depending on what fits your data and your purpose. For a straight-line relationship, choose Linear. A polynomial trendline can bend to follow a curve, but a higher order may fit the visible points closely while being less useful for explaining the broader pattern. Do not choose a model only because it makes the line look attractive.
- Turn on the equation. In the Format Trendline pane, scroll through Trendline Options and select Display Equation on chart. Excel places the equation on the chart as a label. You can also select Display R-squared value on chart if you want a fit statistic alongside it. The equation checkbox and R-squared checkbox are separate, so turn on either or both according to what you need.
- Read the displayed equation. For a linear trendline, Excel presents the relationship in the form y = mx + b. The coefficient m is the slope: the modeled change in Y for a one-unit increase in X. The constant b is the modeled Y value when X is zero. For example, if the chart shows y = 2.4x + 7, the line predicts a Y value of 19 when X is 5, because 2.4 multiplied by 5 plus 7 equals 19. The prediction is an estimate from the fitted line, not a guarantee that an observed value will equal 19.
- Position and format the label. Click the equation label and drag it to an open part of the chart where it does not cover the points or line. To adjust its appearance, right-click the label and choose the available formatting command, or use the chart formatting controls. If the numbers appear too rounded, check the label’s number-format settings and increase the displayed decimal places where available. Formatting changes what is shown; it does not improve the precision of the model itself.
- Review the chart before using the result. Confirm that the trendline follows the intended series and that the equation uses the expected variables and units. Add axis titles if the variable names are not obvious, and state units where relevant. If the chart has several series, make clear which series the equation describes. Save the workbook after checking that the label remains readable at the chart’s final size.
On Excel for Mac, select the chart and use the Chart Design tab to add a trendline, then open the trendline’s formatting options and enable the equation display. The wording or location of controls can differ between Mac releases. In Excel for the web, select the chart and inspect its chart formatting options; if the trendline pane does not offer the equation checkbox, choose Open in Desktop App and complete the steps there.
Common Mistakes
- Using a chart that misrepresents the X values. In a line chart, Excel may treat the horizontal positions as category order, even if the labels look like dates or numbers. If the actual spacing between X values matters, use an XY (Scatter) chart so Excel plots numeric X coordinates.
- Adding the trendline to the wrong series. When a chart contains multiple series, the trendline belongs to the selected series. Verify which points it follows before treating its equation as the model for your data.
- Assuming the equation is an exact description. A trendline is a fitted model, and its displayed coefficients may be rounded. The equation label is useful for interpretation, but it may not reproduce every plotted point exactly.
- Choosing a complex trendline without a reason. A high-order polynomial can swing sharply between observations or behave poorly outside the plotted range. Prefer the simplest model that makes sense for the data and the question you are asking.
- Reading the intercept as a meaningful real-world value automatically. The intercept is the modeled Y value at X = 0. If zero is outside the observed range or has no practical meaning, the intercept may not be useful to interpret.
- Confusing the equation with a forecast guarantee. Extending a trendline beyond the data is extrapolation. A line can continue mathematically while the real-world relationship changes, so do not assume the equation remains reliable far outside the observed range.
Tips
- Use descriptive axis titles and include measurement units. An equation is much easier to interpret when a reader can tell what X and Y represent.
- For a basic rate-of-change relationship, start with a linear trendline. Compare other types only when the plotted pattern and subject matter support them.
- Turn on the R-squared display only when it helps the reader assess the fit. R-squared describes how well a model accounts for variation in the plotted data under that model; it does not prove that the model is appropriate or that one variable causes another.
- Keep the equation label inside the chart area and away from crowded points. If the chart is small, enlarge it or move the label rather than shrinking it until it is difficult to read.
- Check units before interpreting the slope. If X is measured in days and Y in dollars, the linear slope represents modeled dollars per day, not simply an unexplained number.
- Keep a copy of the source data and note which trendline type you selected if the chart will be used in a report. That makes the result easier to review when the workbook is updated later.
Troubleshooting
The Trendline option is missing. Click the chart first, then click a data series or use the Chart Design tab. Some chart types do not support trendlines, and some chart layouts or series types can limit the choices. Try an XY (Scatter) chart with a standard data series. If you are using Excel for the web, open the workbook in desktop Excel to check whether the required trendline controls are available there.
The equation checkbox is not visible. Make sure the Format Trendline pane is open, not the Format Data Series pane. Select the trendline itself, open its formatting options, and scroll within the pane to the Trendline Options section. The equation option appears after a trendline has been added; it is not an option in the initial chart-insertion menu.
The equation uses unexpected X values. Check the chart type and the series ranges. A line chart may use position numbers for its category axis, while a scatter chart uses the numeric X values in the source range. Right-click the chart and choose Select Data to review the series, then confirm that the X values and Y values point to the correct rows and columns.
The displayed result looks inaccurate. Confirm that the selected trendline type is appropriate, that text or blank cells have not disrupted the source data, and that each X value is paired with the correct Y value. Also distinguish label rounding from model quality: a short equation may display fewer decimals than Excel used internally. Increasing displayed decimals can clarify the coefficients, but it will not fix incorrect data or an unsuitable model.
The equation overlaps the chart or disappears after resizing. Select the label and drag it to a less crowded position. If the chart is too small, enlarge the chart area or reduce other visual elements. Check the chart again after resizing or moving it, because the label’s position may no longer be clear.
Frequently Asked Questions
Can I display the equation without displaying R-squared?
Yes. In the Format Trendline pane, select Display Equation on chart and leave Display R-squared value on chart unchecked. They are independent settings.
Can I show an equation for every data series?
Yes, if the chart and series support trendlines. Add a separate trendline to each series and enable the equation display for each one. Move the labels apart and identify which series each equation belongs to, especially when their colors are similar.
Why does Excel show a different equation after I change the chart type?
The chart type can change how Excel interprets the horizontal values, and changing the selected series or its source range also changes the fit. Use a scatter chart when the actual numeric X values should determine the model, and verify the series ranges after changing the chart.
Can I use the displayed equation to predict a new value?
You can substitute an X value into the displayed equation to estimate the corresponding Y value, as in the worked example above. Treat it as an estimate, and be especially cautious when the new X value is outside the range of the observed data.
How do I get more decimal places in the equation?
Select or right-click the equation label and look for its number-format controls. If available, increase the decimal places. This changes the label’s presentation only; it does not change the source data or make a poor-fitting trendline more reliable.
Does this work in Excel for the web?
Some chart editing options are available in Excel for the web, but controls can vary. If you cannot find the trendline equation option, open the workbook in desktop Excel for Microsoft 365 and use the Format Trendline pane.
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 Histogram Analysis: Create and Interpret DataTo analyze a distribution in Excel for Microsoft 365 on Windows, select a column of numeric observations, choose…
- Change Bin Size in an Excel HistogramTo change a histogram’s bin size in Excel for Microsoft 365 on Windows, select the chart’s horizontal axis,…
- How to Update Pivot Table Data in Google SheetsGoogle Sheets pivot tables update automatically when cells in their source range change. If you add rows or…
- Excel Dashboard Course: What to Learn and How to ChooseAn Excel dashboard course teaches you to turn spreadsheet data into a clear, interactive summary that helps people…
- How to Create a Horizontal Bullet Chart in ExcelCreate a horizontal bullet chart in Excel by charting performance bands as a stacked bar, then placing a…
- Dot Progress Chart in Excel: Create One Step by StepExcel does not include a built-in chart type called a dot progress chart, but you can create the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.