How To Find Slope in Google Sheets
Spreadsheet users often need to calculate the slope of a line related to the data in their spreadsheet. In this article, I will explain how you can calculate slope values in Google Sheets with and without graphs.
What is Slope?
Slope is a concept in geometry that describes the direction and steepness of a line on a Cartesian plane. (A Cartesian plane is the standard x-y grid that you may remember from math class, with an X axis and a Y axis.)
A line that goes up as it goes from left to right on the plane has positive slope; a line which goes down from left to right has negative slope. In the diagram below, the blue line has positive slope, while the red line has negative slope.
Slope is expressed as a number, and that 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 (that is, the line goes up +1 on the Y axis while also going up +1 on the X axis), the slope is 1. If it went up 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 every unit on the X axis.
On a spreadsheet, slope values are generally related to linear regression, which is a way of analyzing the relationship between two or more variables. The variables consist of dependent Y and independent X values, which on spreadsheets would be stored as two separate table columns. The dependent value is the value that changes automatically, by a count, while the 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 numerical data, for example, sales figures for that month.
But wait, you may be saying – that’s a bunch of data, but where are the lines? Slope is about the way the line moves, right? Indeed, and the data represented in this table can easily be visualized using a line graph. Google Sheets provides a simple but powerful set of tools for creating line graphs from table data. In this example, all you have to do is select the entire data table (from A1 to B16) and click on the “Insert Chart” button, and Sheets will instantly produce the following chart:
But wait, you may be saying – that line is all jaggedy! It goes down in some places and up in others! How am I supposed to figure the slope of a crazy line like that?
The answer is something called a trendline. A trendline is the smoothed-out version of your line that shows the overall trend in the numbers. Getting a trendline in Sheets is also easy. Right-click your chart, and select “Edit Chart”. In the Chart Editor that pops up, click the Setup tab and then change the chart type to “Scatter Chart”. Then click the Customize tab, open the Series drop-down section, and toggle “Trendline”. Now your chart should look like this:
The light blue line that follows the string of dots across the chart is the trendline.
So how do we find the slope of that line? Well, if this was math class, we would have to do some math. Fortunately this is the 21st century and math class is well behind us, and we can just tell the computer to do it for us.
Finding the Slope
Within the Chart Editor, we can tell Google Sheets to figure the slope for us. Select the Label drop-down and select Use Equation. That will add the equation that Google Sheets used to calculate the trendline, and the slope of our line is the part to the left of the “*x” term. In this case, the slope is +1251; for every month that passes, the trend is for the sales revenue to increase by $1251.
Interestingly, we don’t actually have to have a chart in order to figure out the slope. Google Sheets has a SLOPE function which will calculate the slope of any data table without bothering to draw it as a picture first. (Drawing the pictures is very helpful in learning about how to do all this, though, which is why we did it that way to start with.)
Instead of creating the chart, you can just add the SLOPE function to a cell in your spreadsheet. The syntax for Google Sheets’ SLOPE function is SLOPE(data_y, data_x). That function will return the same slope value as in the graph’s equation. Note that the order of entry is a little bit backwards from the way you probably display the information in your table; Sheets wants you to put the independent data (the sales revenue) first, and the dependent variable (the month) second. Note also that the SLOPE function is not as smart as the chart creator; it needs pure numeric data for the dependent variable, so I’ve changed those cells to be 1 through 15.) Select any empty cell in the spreadsheet and enter ‘=SLOPE(b2:b16, a2:a16)’ and hit return.
And there’s our slope, with a little bit more precision than that provided by the chart.
So that’s how you can find slope in Google Sheets. If you prefer to use Excel, there’s also a TechJunkie guide to finding slope values in Excel.
Do you have interesting applications for finding the slope in Google Sheets? Share them with us below!