How to Calculate Slope and Intercept in Excel

A Slope value describes the relationship between two values, typically the x and y values.

“Intercept” refers to the point where a line crosses the axis of a graph (x or y).

This is the sample dataset.

sample data to Calculate Slope and Intercept in Excel

Steps:

=SLOPE(C5:C14,B5:B14)

You will get the following slope value: 293.93.

use slope function to Calculate Slope in Excel

=INTERCEPT(C5:C14,B5:B14)

You will get the following intercept value: -146.666.

Note:

In the SLOPE function, arguments must be numerical. Empty cells or text strings will be disregarded.
If a cell contains ‘0,’ it will be used in the calculation.
The SLOPE function’ must contain equal ranges for the x and y values. Otherwise, you get a #N/A error.
Utilize names, arrays, or references containing integers as parameters.
If the known ys and xs have different or no data points, the INTERCEPT returns the #N/A error.

Steps:

apply scatter chart to Calculate Slope and Intercept in Excel

The chart is displayed.

The equation y=293.94x -146.67 is displayed in the chart.

use the equation to Calculate Slope and Intercept in Excel

The slope is m= 293.94, and the intercept -146.67.

Steps:

=SUM((B5:B14-AVERAGE(B5:B14))*(C5:C14-AVERAGE(C5:C14)))/SUM((B5:B14-AVERAGE(B5:B14))^2)

The slope is displayed in C16.

use simple formula to Calculate Slope and Intercept in Excel

Formula Breakdown

SUM((B5:B14-AVERAGE(B5:B14))) adds the average of the x-values in (B5:B14).
*(C5:C14-AVERAGE(C5:C14)) returns the average of the y-values in (C5:C14), multiplied by the previous value.
/SUM((B5:B14-AVERAGE(B5:B14))2) returns the average of the x-values subtracted from (B5:B14), which is the square number. The multiplied number is divided by the result.

Note: