Spreadsheet users often need to calculate the slope of a line related to their spreadsheet data. If you are a new user or are used to using Microsoft Excel, it might be a bit difficult to figure out how to do it yourself.
In this article, you will learn how to calculate slope values in Google Sheets with and without graphs.
What is the slope?
First, what is slope in Google Sheets?
Slope is a concept in geometry that describes the direction and slope of a line on a Cartesian plane. (A Cartesian plane is the standard xy grid you might remember from math class with an X axis and a Y axis.)
A line that rises from left to right on the plane has a positive slope; a line that descends from left to right has a negative slope.
In the diagram below, the blue line has a positive slope, while the red line has a negative slope:
The slope is expressed as a number, and this number indicates how much the line rises or falls over a given distance. If the line goes from X=1, Y=0 to X=2, Y=1 (i.e. the line goes up by +1 on the Y axis while going up by +1 on the X axis), the slope is 1. If he went from X=1, Y=0 to X=2, Y=2, the slope would be 2, and so on.
Larger numbers mean a steeper slope; a slope of +10 means a line that goes up 10 on the Y axis for every unit it moves on the X axis, while a slope of -10 means a line that goes down 10 on the Y axis for each unit on the X axis.
On a spreadsheet, slope values are usually related to linear regression, which is a way to analyze the relationship between two or more variables.
Variables consist of dependent Y and independent X values, which on spreadsheets would be stored as two separate table columns.
Dependent value is the value that automatically changes by a number, while independent value is the value that can change freely. A typical example would be one column (the dependent X variable) that contains a series of dates, with another column (the independent Y variable) that contains numeric data, for example, sales figures for that month.
Where are the lines? Where is the graph? Slope is about how the line moves, right?
Think of spreadsheet data as the plot points of a graph. The data represented in this table can be easily visualized using a line graph.
How to Find Slope in Google Sheets
Google Sheets provides a simple yet powerful set of tools for creating line charts from table data. In this example, all you need to do is select the entire data table (from A1 to B16) and click on the “Insert Chart” button. After that, Sheets will instantly produce the following chart:
- Click on the Chart icon. It goes down in some places and up in others! How are you supposed to calculate the slope of a crazy line like that? The answer is what is called a trendline. A trend line is the smoothed version of your line that shows the overall trend of the numbers.
- Click on Edit chart. It's also easy to get a trendline in Sheets.
- Click on the trendline. In the chart editor that appears, click the Configuration tab, then change the chart type to Scatter plot.
- Click it Personalize their campaigns tab, open the Series drop-down section and toggle Trendline.
Now your graph should look like this:
The light blue line following the chain of dots on the chart is the trendline.
So how do you find the slope of this line?
Well, if it was a math class, you should be doing math. Fortunately, we are in the 21st century and math lessons are well behind us. Instead, we can just tell the computer to do it for us. Thanks Google.
How to Find the Slope of a Graph on Google Sheets
In the graph editor, we can use Google Sheets to determine the slope. Just follow these instructions to find the slope of any line graph in Google Sheets.
- Select Label > Use equation. This will add the equation that Google Sheets used to calculate the trend line, and the slope of our line is the part to the left of the *X term.
- In this case, the slope is +1251. This means that for each passing month, the revenue will increase by a total of $1.
3. Interestingly, you don't need to have a graph to determine the slope. Google Sheets has a SLOPE function that will calculate the slope of any table of data without bothering to draw it as an image first. (However, drawing the pictures is very useful for learning how to do all of this, so we did it this way to start with.)
4. Instead of creating the graph, you can simply add the SLOPE function to a cell in your worksheet. Syntax for Google Sheets' SLOPE the function is PENT(data_y, data_x). This function will return the same slope value as in the graph equation.
Note that the order of entry is a little backward from how you're probably displaying the information in your table. This is because Sheets wants you to put the independent data (the revenue) first and the dependent variable (the month) second.
You should also note that the SLOPE the function is not as smart as the chart creator. It needs pure numeric data for the dependent variable, so I changed those cells to be 1 to 15.
Select any blank cell in the worksheet and enter '=SLOPE(b2:b16, a2:a16)' and press Come back.
And there is our slope, with a little more precision than that provided by the graph.
Latest Thoughts
This is how you can find the slope in Google Sheets. Hopefully, if you were having trouble figuring this out on your own, these instructions were able to help.
If you prefer to use Excel instead of Sheets, there's also a TechJunkie Guide to Finding Slope Values in Excel.
Do you have any cool apps for finding slope in Google Sheets? Share them with us below!
