Calculate Slope in Excel

It’s easy to calculate a slope in Excel using the SLOPE function, but it’s also possible to use chart trendlines and simple arithmetic as well. This article will show you how to do all three, so keep reading!

Calculate Slope in Excel with the SLOPE Function

Excel SLOPE Function Formula: How to Calculate Slope in Excel without a Graph

The Excel SLOPE function uses linear regression to calculate the slope of data in Excel without creating a graph, adding a trendline, or performing complex linear regression analysis.

The syntax of the SLOPE function is shown below:

=SLOPE(known_ys, known_xs)

where:

known_ys: the known y-values (the values on the vertical axis or dependent variables)

known_xs: the known x-values (the values on the horizontal axis or independent variables)

SLOPE Function Examples

The SLOPE function can calculate the slope when there are 2 or more known x-values and 2 or more known y-values.

Example 1

In this example, there are only two x-values and two y-values. The formula for calculating the slope between the two points defined by the x- and y-values is

=SLOPE(C3:C4,B3:B4)

calculate slope in excel

The result is 1.2.

Because there are only two values, the slope could have been calculated as the rise over the run, or the difference in the y-values divided by the differences in the x-values.

=(C4-C3)/(B4-B3)

calculate rise over run in excel

Of course, the result of this calculation is also 1.2.

Example 2

If there are more than two pairs of x- and y-values, the SLOPE function returns the slope of the line of best fit for the dataset. Let’s see how it works by adding another pair of x- and y-values to our data from before. Now the formula is:

=SLOPE(C3:C5,B3:B5)

calculate slope of best fit line in excel

Here the result is 0.95.

This is not the slope between any two sets of points. Instead, it is the slope of a straight line that “best fits” the data. The slope of the best fit line is calculated using linear regression.

SLOPE Function Errors

#DIV/0! Error: Only One Pair of Values Selected

If only one pair of x- and y-values is selected as the SLOPE function arguments, this equivalent to trying to calculate the slope of a single point. This is undefined, and so the result is a #DIV/0! error

In the example below, one x-value and one y-value are entered as arguments.

slope formula mistake in excel 1

Because the “run” is zero, a #DIV/0! error is returned:

DIV0 error with slope function in excel

#DIV/0! Error: Data Points Represent a Vertical Line

When all x-values in the dataset are the same, the line represented by the data is perfectly vertical. The slope is infinite or undefined in this situation because the “run” in the denominator is zero. This also causes a #DIV/0! error, as demonstrated below:

slope function in excel mistake 2

DIV0 error in excel vertical line

#N/A Error: Different Number of X- and Y-values

An #N/A error can occur with the SLOPE function in Excel when the number of y-values differs from the number of x-values. In the example below, 3 x-values are chosen as arguments for the SLOPE function, but only 2 y-values are chosen.

slope function in excel unequal points

This returns an #N/A error:

NA error slope function in excel

How to Find the Slope of a Trendline in Excel: Find Slope on an Excel Scatterplot

Charts are a great way to visualize a data set, and they also provide an opportunity to add a trendline that can display the slope of a set of x- and y-values.

Step 1: Graph the Data

The method below works if the data is on a spreadsheet with the x-values in one column and the y-values in another column immediately to the right of the x-values as shown in the images below.

Select ALL the data…

select dataset in excel

…then insert a scatter chart by selecting Insert > Scatter Chart. (The scatter chart with only data markers works best for this.)

create scatter chart from dataset in excel

The data will be shown on a chart like this:
excel chart with data markers

Step 2: Add a Trendline

Next add a trendline by right-clicking on the data series and selecting “Add Trendline…”

add trendline to calculate slope in excel

This adds a linear trendline to the chart by default:

trendline in excel

Step 3: Display the Trendline Equation on the Chart

To determine the slope, display the equation on the chart by selecting “Display Equation on the chart” in the “Format Trendline” pane that appears on the right side of the screen:

display equation on chart trendline to find slope in excel

The trendline equation is displayed on the chart:

calculate slope in excel with chart trendline equation

The slope is the coefficient in front of x, or 0.95.

How to Calculate Rise over Run in Excel

As discussed above, if there are only two x- and y-values, it possible to calculate the slope, or rise over run, of the data points without using the SLOPE function.

Rise over run is the difference between the y-values (rise) divided by the difference in the x- values (run).

When using this method, it’s important to be consistent in the order of the x- and y-values in the evaluation. If the rise is calculated as the second y-value minus the first y-value, the run must be calculated as the second x-value minus the first y-value.

In the example below, the rise over run is calculated by

=(C4-C3)/(B4-B3)

how to calculate rise over run in excel

The rise over run is 1.2.

Calculate Slope in Excel with the LINEST Function

The LINEST function is an array function that can be used to perform linear regression and non-linear curve fitting. However, it can calculate both the slope and the intercept of a data set with a single formula.

The LINEST function has similar arguments to the slope function, with a couple of additions:

=LINEST(known_ys,known_xs,const,stats)

where

known_ys: the known y-values

known_xs: the known x-values

const: (optional) if TRUE, the y-intercept is calculated normally, if FALSE the y-intercept is set to 0

stats: (optional) if TRUE, additional regression statistics will be returned, if FALSE no additional statistics are returned

In the example below, the same x- and y-values are chosen. CONST is TRUE so that the y-intercept will be calculated normally, and STATS is FALSE so that no additional statistics are returned.

calculate slope in excel with LINEST

In Excel 365 LINEST is a dynamic array and the results are returned to two cells. The first cell contains the slope and the second cell contains the y-intercept. (If you are using a version of Excel other than Excel 365, only the slope will be returned.)

linest results slope and intercept

The values of the slope and intercept returned by LINEST match the values in the chart trendline. The slope is 0.95 and the y-intercept is 0.133

Scroll to Top