How to Find the Slope of a Line in Excel (5 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel is a powerful software. We use Excel functions and features for our educational, business, and other daily life purposes. Furthermore, Excel provides some special functions to calculate the line slope in our workbooks. This article will show you 5 easy ways to find the slope of a line in Excel.


What Is the Slope of a Line?

A slope value describes the relationship between two values, for instance, x and y values. It is usually denoted by m in equations. In essence, a line’s slope determines two parameters:

  • The line’s steepness
  • The direction of the line

The mathematical equation of the slope is:

y = mx + c

Here,

  • m denotes the slope of a line.
  • C denotes intercept.

How to Find the Slope of a Line in Excel: 5 Easy Ways

Determining the slopes of a line in Excel can be really cumbersome if we do not know the right functions to implement. In this article, I will show you 5 easy ways to find the slope of a line in Excel. For instance, we take a dataset that contains two variables time x and distance y.

how to find the slope of a line in excel


1. Create Simple Formula in Excel to Calculate the Slope

The slope of a line typically does not lie exactly on the declared known time (x) and distance (y) points. However, we can create an Excel formula that will assist us in calculating the slope of a line in our graph. Furthermore, we need to find the mean of the declared known x-values and y-values. Now, let’s create a formula joining SUM and AVERAGE functions to get the slope easily. The SUM function adds up the array values and returns a numeric value. On the contrary, the AVERAGE function returns the mean value of an array. Follow the procedures.

Steps:

  • Firstly, in C12, type the following formula.
=SUM((B5:B10-AVERAGE(B5:B10))*(C5:C10-AVERAGE(C5:C10)))/SUM((B5:B10-AVERAGE(B5:B10))^2)
  • Afterward, press Enter or Tab.

Create Simple Formula in Excel to Calculate the Slope of a Line

  • As a result, the slope value appears in cell C12.

  • Since, we took increasing x-values and decreasing y-values, therefore, the value of the slope returns as negative. A negative slope elopes in an anti-clockwise, left-to-right downhill direction and makes an obtuse angle with the x-axis.
  • See the chart given below to understand better.

🔎 How does the formula work?

  • SUM((B5:B10-AVERAGE(B5:B10)) sums up the value obtained by the average of x-values after subtracting from the array (B5:B10).
  • *(C5:C10-AVERAGE(C5:C10)) returns the average of y-values subtracted from the range (C5:C10) and multiplied with the previous number.
  • /SUM((B5:B10-AVERAGE(B5:B10))^2) returns the square number of the average of x-values subtracted from range (B5:B10). Later, we divide the multiplied number by the obtained value.

Read More: How to Find Instantaneous Slope on Excel


2. Insert Scatter Chart to Get the Slope of a Line

You can plot your data in a Scatter chart and use it to determine the slope. Furthermore, you can intercept the trendline if you like to visualize your data and the regression line. With this in mind, in this method, we will make a Scatter chart to find out the slope of a line. Naming also as a Scatter plot or Scattergram, this chart visualizes and returns the relationship between 2 or more variables. Let’s follow some steps to implement this.

Steps:

  • First, select the range B4:C10.
  • After that, go to Insert and then locate the Charts group.
  • Next, click Insert Line and Area Chart dropdown icon.
  • See the below picture to understand better.

Insert Scatter Chart to Get the Slope of a Line in Excel

  • Eventually, the Scatter dropdown pops up.
  • Here, select the chart that you wish to get in your dataset.

  • As a result, the desired chart appears.
  • Now, right-click on any dot on the line.

  • Consequently, a context menu opens up.
  • Tap on Add Trendline option.

  • Subsequently, another dropdown box pops up.
  • Check the Display Equation on chart box.

  • Thus, the equation y=-3.4286x + 18.571 appears in the chart.
  • As we know, the line equation:
  • Therefore, from the obtained equation we get the slope, m= 3.4286.

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


3. Use SLOPE Function to Find Slope in Excel

The SLOPE Function in Excel, generally used in statistics functions and financial analysis refers to the ratio of the change in the y-axis to the change in the x-axis, which returns a numeric value. The function relates to the method for calculating a line’s steepness and inclination. The function uses x and y coordinates points along the line to determine the slope of the lines. Let’s use the function to determine the slope in our chart.

Steps:

  • To begin with, type the formula in C12.
=SLOPE(C5:C10,B5:B10)
  • Afterward, hit Enter or Tab.

Use SLOPE Function to Find Slope of a line in Excel

  • Hence, we get our desired slope of the line.

  • See the chart given below to understand better.

Output of SLOPE Function to Find Slope in Excel

Read More: How to Calculate Slope of Exponential Graph in Excel


4. Find Out the Slope of a Line Through Excel VBA

In this method, we will calculate the slope of a line using Excel VBA. If we have a large dataset, Excel VBA assists us to find out the slope when the set the range. Let’s follow the steps carefully.

Steps:

  • First, go to the Developer tab and then Visual Basic.
  • Subsequently, the Visual Basic window appears.

Find Out the Slope of a Line Through Excel VBA

  • Afterward, click Insert and then create a module by tapping on Module.

  • After creating the Module, we write the following VBA code there.
Sub Find_SLOPE()
    Dim Known_X As Range
    Dim Known_Y As Range
    Set Known_X = Range("B5:B10")
    Set Known_Y = Range("C5:C10")
    MsgBox Application.WorksheetFunction.Slope(Known_Y, 
    Known_X)
End Sub
  • Next, hit Run.

  • Finally, we find the required slope.

Excel VBA Output to Find the Slope of a Line


5. Format Trendline to Determine Slope of a Line in Excel

Excel provides the Format Trendline option to find slopes in our charts and graphs. This tool displays equations on charts. Therefore, let’s build a chart and go through some easy steps to format the trendline in our chart.

Steps:

  • Firstly, select the range B4:C10.
  • After that, go to Insert and then locate the Charts group.
  • Next, click Insert Line and Area Chart dropdown icon.
  • Check out the picture given below to understand better.

Format Trendline to Determine Slope of a Line in Excel

  • As a result, we get a chart.
  • Now, tap the Chart Elements icon shown below.

  • Subsequently, the Chart Elements options menu opens.
  • Later, check the Trendline option.

  • That gives out another option box.
  • Locate Format Trendline and click on it.

  • Eventually, the Format Trendline box appears on the display.
  • Afterward, check the Display Equation on chart box.

  • As a result, we get the desired equation in the chart.
  • Since we already discussed the line equation, we can say that the slope is -3.4286 in this case.

Output of Format Trend line to Determine Slope of a Line in Excel


Download Practice Workbook


Conclusion

In conclusion, we have discussed some easy ways to find the slope of a line in Excel. Not to mention, our ExcelDemy website shows various simple problem-solving methods like this. Please leave any further queries or recommendations in the comment box below.


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.
Yousuf Khan
Yousuf Khan

Yousuf Khan has been working with the ExcelDemy project for over one year. He has written 47+ articles for the ExcelDemy site. He also solved a lot of user problems with ExcelDemy Forum. He has interests in Excel & VBA, Desktop and mobile applications, and projects & templates. He completed his graduation and post-graduation in Information Technology from Jahangirnagar University. Currently, he works as a VBA & Excel Content Developer in ExcelDemy projects, writing unique and informative content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo