Excel Trendline Intercept: Meaning and How to Set It

Charts & pivot tables · Updated November 21, 2026

In Excel, a trendline intercept is the estimated y-value where the trendline crosses the vertical axis, which is the value of y when x equals zero. To set it for a linear trendline, select the trendline, open the Format Trendline pane, check Set Intercept, and enter the value you want, such as 0.

What a trendline intercept means

A linear trendline describes a straight-line relationship between x-values and y-values. Its equation is commonly written as y = mx + b: m is the slope, and b is the intercept. When x is zero, the mx part is zero, leaving b as the predicted y-value. The intercept is therefore a feature of the fitted line, not necessarily a data point in your chart.

For example, a line with an intercept of 12 predicts y = 12 when x = 0. If the observed x-values range from 40 to 90, that prediction is an extrapolation well outside the data. It may be mathematically defined but not useful or credible in the real-world context. An intercept is most interpretable when zero is a meaningful value on the x scale and the data provide a reasonable basis for estimating behavior near zero.

The word intercept can also mean the point where a line crosses an axis. In a standard XY scatter chart, the y-intercept is the crossing at x = 0. Do not confuse it with the x-intercept, where y = 0. Excel’s Set Intercept option specifies the y-intercept for a linear trendline.

How to set the intercept on an Excel chart

These steps apply to Excel for Microsoft 365 on Windows. Start with an XY scatter chart if the horizontal values are numeric measurements and their spacing matters; a line chart commonly treats the horizontal axis as categories instead.

  1. Select the data, including the x-values and corresponding y-values, and choose Insert > Insert Scatter (X, Y) or Bubble Chart. Pick a scatter chart style.
  2. Select the chart, then choose Chart Design > Add Chart Element > Trendline > More Trendline Options. You can also select the chart’s trendline and open its formatting pane.
  3. In the Format Trendline pane, choose Linear under Trendline Options if the relationship is intended to be a straight line.
  4. Check Set Intercept and enter the desired y-value. Enter 0 to require the line to pass through the origin.
  5. To show the fitted equation on the chart, select Display Equation on chart in the pane. If useful, also select Display R-squared value on chart.

Changing the intercept constrains the fitted line; it does not move the underlying observations or alter their values. Excel adjusts the line’s slope to fit the data under the constraint. The exact chart controls can look slightly different in Excel for Mac, but the trendline options are available from the chart’s formatting controls. Excel for the web may not expose every chart-formatting option found in the desktop app, so use desktop Excel if you cannot find Set Intercept.

What changes when you fix the intercept

When Excel fits an unconstrained linear trendline, it chooses both the slope and intercept to minimize the sum of squared vertical differences between the observed y-values and the line’s predicted values. When you specify an intercept, that value is no longer estimated from the data. Excel chooses the slope while keeping the line anchored at the specified y-value.

A forced zero intercept is appropriate only when there is a sound reason the relationship must pass through (0, 0). For instance, a measurement might be defined so that a true input of zero necessarily produces an output of zero. Even then, measurement offsets, background readings, or instrument errors can make the observed relationship depart from the origin. A line that looks plausible over the measured range is not, by itself, evidence that the true intercept is zero.

Do not set the intercept to zero merely to make a chart look tidy or because the plotted values are positive. A fixed intercept can noticeably change the slope and the predictions. If you want to examine the effect, compare the unconstrained and constrained lines against the data and consider whether each line makes sense for the process being measured.

Worked example: a zero-intercept line

Suppose A2:A6 contains x-values 1, 2, 3, 4, and 5, while B2:B6 contains matching y-values 3, 5, 7, 9, and 11. The unconstrained relationship is y = 2x + 1: the values rise by 2 each time x increases by 1, and the fitted line crosses the y-axis at 1. That intercept is estimated even though the table has no observation at x = 0.

With x-values in A2:A6 and y-values in B2:B6, enter this formula to calculate the unconstrained intercept:

=INTERCEPT(B2:B6,A2:A6)

It returns 1 for these data. With the same layout, this formula calculates the unconstrained slope:

=SLOPE(B2:B6,A2:A6)

It returns 2. If you instead require the intercept to be zero, the best-fitting slope becomes 125/55, or about 2.2727. The constrained line therefore predicts about 2.27 at x = 1, rather than the observed value 3, and about 11.36 at x = 5, rather than 11. The constrained slope differs because Excel must balance the errors while keeping the line pinned to the origin.

This example shows why a forced intercept is a modeling decision, not just a display preference. The zero-intercept line may be suitable if the underlying relationship truly requires zero output at zero input. If not, the unconstrained model may represent the data more faithfully, especially near the low end of the measured range.

How to decide whether to set it

Consider the meaning of the variables before changing a chart setting. Ask whether x = 0 is a possible and meaningful condition, whether y must equal the proposed intercept at that condition, and whether the observations cover values near zero. If zero is far outside the observed range, the fitted intercept can be sensitive to small changes in the data and may be a poor basis for a real-world conclusion.

It also helps to distinguish a theoretical constraint from a convenient assumption. A known physical or operational rule may justify fixing the intercept. A desire for a simpler-looking graph does not. If you are comparing models, compare predictions and residual patterns on the same data range, and explain whether the intercept was estimated or imposed. A displayed R-squared value alone does not establish that a model or its intercept is appropriate.

  • Use an estimated intercept when the data should determine where the line crosses the y-axis.
  • Fix the intercept when a defensible constraint requires a particular value.
  • Use caution when x = 0 is outside the measured range or has no meaningful interpretation.
  • Check whether the chart’s x-axis represents actual numeric values rather than category positions.

Chart trendlines compared with worksheet calculations

A chart trendline is useful for visualizing a fitted relationship and showing its equation. Worksheet functions are useful when you need the coefficient in another calculation, want to report a value in a cell, or need to check the chart’s result. Excel’s INTERCEPT function estimates the intercept of a least-squares linear fit; it does not let you supply a fixed intercept. SLOPE likewise returns the slope of the ordinary fitted line, not the slope of a line constrained to pass through a chosen point.

With x-values in A2:A6 and y-values in B2:B6, the formula =INTERCEPT(B2:B6,A2:A6) returns the estimated y-intercept, while =SLOPE(B2:B6,A2:A6) returns the estimated slope. Keep the ranges aligned: each x-value must correspond to the y-value on the same row. Blank cells, text, mismatched ranges, or accidentally including a header can produce misleading results or errors.

For a chart’s constrained fit, use the trendline’s Set Intercept control. Do not expect the ordinary INTERCEPT and SLOPE worksheet functions to reproduce that constrained equation. Excel’s LINEST function can return regression statistics, but a standard call does not automatically apply the chart’s fixed-intercept setting; consult its function options and verify the model being calculated before using it as a match for a chart.

Common problems and how to check them

Set Intercept is missing. Confirm that you selected a linear trendline and are looking in the Format Trendline pane rather than formatting the data series. Some trendline types have different parameters, and the fixed-intercept control is associated with the linear fit. If the option is still absent, check that you are using the desktop app and that the chart type supports a trendline.

The equation on the chart seems slightly different from the worksheet result. Excel rounds the equation displayed on a chart. To see whether rounding explains the difference, increase the displayed number of decimal places in the equation label’s formatting options. Also confirm that the chart and worksheet use the same x-values, y-values, and data points.

The line does not cross the origin visually. Check that Set Intercept is checked and its value is 0, then examine the chart’s axis bounds and scale. If the displayed axes do not include zero, the origin may be outside the visible plot area. A line ending at the chart boundary is not necessarily showing where it would cross the axis beyond that boundary.

The intercept is unexpectedly large or nonsensical. Verify the x-axis values and chart type. A category-based chart may not use the intended numeric x-values as a true measurement scale. Also check for unit mismatches, dates stored as serial numbers, or data that cover only a narrow range far from x = 0. The fitted intercept is an extrapolation in such cases, not an observed measurement.

Frequently Asked Questions

How do I force a trendline through zero in Excel?

Select the linear trendline, open the Format Trendline pane, check Set Intercept, and enter 0. Excel then fits the slope while keeping the y-intercept fixed at zero. Confirm that this constraint is justified for the relationship rather than applying it only for appearance.

What is the Excel formula for a trendline intercept?

With y-values in B2:B6 and their matching x-values in A2:A6, use =INTERCEPT(B2:B6,A2:A6). It returns the estimated intercept for an ordinary linear regression. It does not set a chart trendline’s intercept to a chosen value.

Why does my trendline equation show a different intercept?

The chart label may round its coefficients, or the chart may be using different data than the worksheet calculation. Check the ranges and increase the equation label’s displayed decimal places. Also verify whether Set Intercept is enabled, since a constrained fit is not the same calculation as an unconstrained INTERCEPT result.

Does setting the intercept to zero change the data?

No. It changes the fitted line, not the source cells. Excel recalculates the line’s slope under the constraint, so predicted values and the line’s fit to the observations can change even though the plotted data remain the same.

Can I set an intercept for every type of trendline?

The Set Intercept control is used with a linear trendline. Other trendline types, such as polynomial or exponential, use different equation forms and options. If the control is unavailable, check the selected trendline type and whether the chart supports that option.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.