Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

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.


Download Practice Workbook

You can download this workbook to practice yourself.


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.

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

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 Effective Ways)


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 Trendline in Excel (2 Easy Methods)


3. Use SLOPE Function to Find Slope in Excel

The SLOPE formula 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 Find Slope of Tangent Line in Excel (2 Suitable Ways)


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

Read More: How to Find Slope of Polynomial Trendline in Excel (with Detailed Steps)


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

Yousuf Khan

Yousuf Khan

Hello! This is MD Yousuf Khan. I am a graduate & post-graduate in Information Technology from Jahangirnagar University, Bangladesh. Currently, I am writing articles for ExcelDemy. I am an independent, self-motivated person with enthusiasm to learn new things, and always try to do my best in any work assigned to me.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo