How to Calculate Slope and Intercept in Excel (3 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

A line’s steepness can be determined by looking at its slope. The slope is calculated mathematically as “rise over run”. If you are looking for special tricks to know how to calculate slope and intercept in Excel, you’ve come to the right place. There are numerous ways to calculate slope and intercept in Excel. This article will discuss the details of all those 3 methods to do this task in Excel. Let’s follow the complete guide to learn all of this.


How to Calculate Slope and Intercept in Excel: 3 Easy Methods

A Slope value describes the relationship between two values, typically referred to as the x and y values. Both positive and negative slope values are possible. The term “Intercept” refers to the location where a line crosses a graph’s axis. The x-intercept is the point at which it crosses the x-axis. The y-intercept is the point at which the y-axis is crossed.

The following dataset, which contains information about the month and the company’s related sales, will be used as an example. You may determine whether there is a positive or negative correlation between these data points by computing the slope for this data. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.

 sample data to Calculate Slope and Intercept in Excel


1. Use SLOPE and INTERCEPT Functions to Calculate Slope and Intercept in Excel

Here, we’ll show you how to compute the slope and intercept in Excel. We’ll demonstrate the SLOPE and INTERCEPT functions in this case. The SLOPE formula in Excel calculates the ratio of the change in the y-axis to the change in the x-axis and produces a numerical result. We use it for statistical functions and financial analysis. The function has to do with how determining the inclination and steepness of a line. To calculate the slope of the lines, the function needs x and y coordinate points along the line. Let’s walk through the following steps to calculate the slope and intercept in Excel.

📌 Steps:

  • First, to calculate the slope, select the cell C16 and write down the following formula:

=SLOPE(C5:C14,B5:B14)

  • Next, press Enter.
  • Therefore, you will get the following slope value 293.93.

use slope function to Calculate Slope in Excel

  • Then, to calculate the intercept, select the cell C17 and write down the following formula:

=INTERCEPT(C5:C14,B5:B14)

  • Next, press Enter.
  • Therefore, you will get the following intercept value -146.666.

💡 Note:

  • The SLOPE function’s arguments must be numerical (value as DATES are allowable). Any cells that are empty or contain text strings will be disregarded.
  • If any cell or cells contain a value of ‘0,’ that value will be used in the calculation.
  • The SLOPE function’s input should contain an equal number of x and y values. If you provide it with ranges of different sizes, you will receive a #N/A error.
  • It is recommended to utilize references or names, arrays, or references containing integers as parameters.
  • In an array or reference argument, text, logical values, and empty cells are disregarded; however, cells with the value zero are taken into account.
  • If known ys and known xs have differing numbers of data points or no data points, INTERCEPT returns the #N/A error value.

Read More: How to Find the Slope of a Line in Excel


2. Apply Excel Scatter Chart for Calculating Slope and Intercept

You can use a scatter chart to visualize your data and calculate the slope and intercept. If you like to see your data and the regression line visually, you may also intercept the trendline. In order to determine the slope of a line using this method, we create a scatter chart. This graph, often known as a scatter plot or scattergram, shows the association between two or more variables. Let’s carry out this in a few phases.

📌 Steps:

  • First of all, select the range B4:C14.
  • After that, go to the Insert tab and then locate the Charts group.
  • After that, click the Dropdown Icon for Inserting Lines and Areas.
  • For a better understanding, please see the picture below.
  • The Scatter dropdown appears eventually.
  • In this section, you can select a chart to include in your dataset.

apply scatter chart to Calculate Slope and Intercept in Excel

  • Thus, we get the desired chart.
  • Next, right-click on any point along the line.

  • As a result, a context menu appears.
  • Then, click the Add Trendline button.

  • Therefore, another dropdown box appears after that.
  • Check the Display Equation on chart box.

  • Thus, the equation y=293.94x -146.67 appears in the chart.

use the equation to Calculate Slope and Intercept in Excel

  • Therefore, from the obtained equation we get the slope, m= 293.94, and intercept -146.67.

Read More: How to Find Instantaneous Slope on Excel


3. Create Simple Formula in Excel to Calculate the Slope

Typically, the slope of a line does not perfectly coincide with the stated known month (x) and sales (y) values. We calculate the slope of a line in our graph with the help of an Excel formula, though. We determine the mean of the declared known x-values and y-values. To quickly get the slope, let’s now combine the SUM and AVERAGE functions in a formula. We add the array contents with the SUM function, which then outputs a numeric number. The AVERAGE function, on the other hand, returns the array’s mean value. Observe the steps.

📌 Steps:

  • First, to calculate the slope, select the cell C16 and write down the following formula:

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

  • Next, press Enter.
  • As a result, the slope value appears in cell C16.

 use simple formula to Calculate Slope and Intercept in Excel

🔎 How does the formula work?

  • SUM((B5:B14-AVERAGE(B5:B14))) adds the average of the x-values after removing them from the array (B5:B14).
  • The formula *(C5:C14-AVERAGE(C5:C14)) returns the average of y-values taken from the range (C5:C14), multiplied by the previous value.
  • The function /SUM((B5:B14-AVERAGE(B5:B14))2) outputs the average of the x-values subtracted from the range (B5:B14), which is the square number. The multiplied number is then divided by the result.

💡 Note:

  • Therefore, the slope’s value would be negative if we used increasing x-values and decreasing y-values. A negative slope forms an obtuse angle with the x-axis and slopes downward in an anticlockwise, left-to-right orientation.

Read More: How to Find Slope of Logarithmic Graph in Excel


Download Practice Workbook


Conclusion

That’s the end of today’s session. I strongly believe that from now on, you may be able to calculate slope and intercept in Excel. If you have any queries or recommendations, please share them in the comments section below. Don’t forget to check our website Exceldemy for various Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles


<< Go Back to Excel SLOPE Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Saquib Ahmad Shuvo
Saquib Ahmad Shuvo

Saquib Ahmad Bhuiyan, holding a BSc degree in Naval Architecture & Marine Engineering from Bangladesh University of Engineering and Technology, skillfully integrates engineering expertise with a passion for Excel. He provides solutions to complex issues as an Excel & VBA Content Developer for ExcelDemy Forum. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing his commitment to excellence. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, Rhinoceros,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo