Statistical

INTERCEPT Function in Excel

Returns the y-intercept of the linear regression line.

Syntax

  • =INTERCEPT(known_y's, known_x's)

Arguments

  • known_y's (required): Dependent data range
  • known_x's (required): Independent data range

Examples

  • =INTERCEPT(B2:B10, A2:A10) - Y-intercept of trend line - Result: Intercept value

Excel INTERCEPT function documentation

  • INTERCEPT returns the y-intercept (b) of the least-squares line through known_y's and known_x's — the predicted Y when X is 0.
  • Syntax is =INTERCEPT(known_y's, known_x's). Both ranges must be numeric and the same length. Available in Excel 2010 and later.
  • Pair with [SLOPE](/functions/slope/) for y = mx + b, or use [FORECAST.LINEAR](/functions/forecast-linear/) when you only need one prediction.
  • Related: [LINEST](/functions/linest/) returns slope, intercept, and fit statistics in one array; [RSQ](/functions/rsq/) reports how linear the relationship is.

Step-by-step: find the intercept of a trend

  • Step 1 — Put X values (months, ad spend, units) in one column and matching Y values (revenue, cost, KPI) in the next column.
  • Step 2 — Confirm both columns are numbers. Text dates or numbers stored as text return #VALUE!.
  • Step 3 — Enter =INTERCEPT(Y_range, X_range), for example =INTERCEPT(C2:C13, B2:B13).
  • Step 4 — Read the result as the baseline: intercept 12000 on monthly revenue vs month number means the line starts at $12,000 when month is 0.
  • Step 5 — Check fit with =RSQ(Y_range, X_range). An intercept is only useful when the line actually fits the points.

INTERCEPT vs SLOPE vs FORECAST.LINEAR

  • INTERCEPT is only b. Use it when a dashboard needs the starting level of a trend.
  • SLOPE is only m, the change in Y per unit of X. See [SLOPE](/functions/slope/) for rate-of-change examples.
  • Predicted Y for a new X is =SLOPE(Y,X)*new_x + INTERCEPT(Y,X), which matches FORECAST.LINEAR(new_x, Y, X).
  • A negative intercept means the line crosses below zero at X = 0. That can be a real baseline (fixed cost) or a sign that X = 0 is outside your data.

Worked examples to copy

  • Sales baseline: X = advertising spend, Y = sales. INTERCEPT estimates sales with zero ad spend, assuming a straight line.
  • Cost model: X = units, Y = total cost. INTERCEPT approximates fixed cost; SLOPE approximates variable cost per unit.
  • Do not force X to include 0 if your history starts at year 2019. The intercept is a mathematical extension, not an observed point.
  • Mismatched range sizes return #N/A. One unique X value, or all identical X values, can return #DIV/0!.

Common errors

  • Arrays must be same size

Use cases

  • Baseline value
  • Regression analysis
  • Trend starting point

Frequently asked questions

  • What is INTERCEPT in Excel? INTERCEPT calculates the y-intercept of a linear regression line - where the trend line crosses the y-axis (when x=0). Combined with SLOPE, you can build the equation y = mx + b where INTERCEPT gives you 'b'.
  • How do I create a trend line equation with INTERCEPT and SLOPE? Use =SLOPE(y_values, x_values) for 'm' and =INTERCEPT(y_values, x_values) for 'b'. The equation is y = mx + b. To predict y for any x: =SLOPE()*x + INTERCEPT(). Or use FORECAST.LINEAR directly.
  • What does a negative INTERCEPT mean? A negative intercept means the trend line crosses the y-axis below zero. For example, if analyzing sales vs. advertising spend, a negative intercept might indicate baseline costs before any sales occur. The intercept's meaning depends on your data context.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.