Google Sheets is frequently used to archive, access, and organize information. But it can also be used for some impressive calculations. Understanding more about Google Sheets algorithms and calculations can give you insights into how to use Google for advanced analytics and reporting.
Let’s take a look at how to find slope on Google Sheets.
Why Would You Need to Know How to Find Slope on Google Sheets?
“Slope” is a calculation of the steepness of a line. A line that is ascending (lower to the left and higher to the right) will have a positive slope. A line that is descending (higher to the left and lower to the right) will have a negative slope.
Being able to assess and compare slopes can be critical for everything from scientific reporting to financial documents. Those who are completing mathematical problems may also need to know how to find the slope of a line in Google Sheets.
Starting With Your Google Sheets Data
Before we can find the slope of a line, we need to form the data that will create the line.
Let’s take a personal finance sheet as an example. Ethan has placed his last year’s expenses in a sheet — but he wants to transform it into a graph.
As you can see, Ethan’s data is really all over the place. He has regular, large payments for school — so, it’s difficult for him to see any real trends. For him to actually start to see trends, he’ll need to know how to chart.
How to Find Slope on Google Sheets with a Chart
Creating a chart in Google Sheets is easy. First, you select the data that you want to make into a chart, and then you click on the “Chart” icon in the toolbar. A chart will pop up on your screen.
A chart is a little unusual to manipulate in Google Sheets because it isn’t part of the cell structure. It isn’t in a row or column. Rather, it’s a floating entity that you can move around just by clicking and dragging.
But this line is extremely erratic. Before we get any further, we need to make it a clear line.
Setup -> Chart Type -> Scatter chart
This creates a scatter plot rather than a series of lines.
Customize -> Series -> Trendline
This creates a single trendline made out of the scatter chart. Now the data is starting to make more sense. While Ethan’s spending initially looks erratic, it fairly events out — it’s only going up a little.
We now have the trendline — the line that we need to use to figure out how to find the slope.
At this point, it’s possible to simply break out a graphing calculator and determine the slope of the line. But Google Sheets has similar functionality.
First, we need to determine the equation that Google Sheets used to create that trendline. We do so like this:
Label -> Use Equation
We can see that the equation that is being used is 18.2*x + 1870. Importantly, we know that the slope is 18.2.
How to Find Slope on Google Sheets Without a Chart
We can also find a slope in Google Sheets without a chart, if we don’t want all those analytics.
SLOPE ([numerical data], [time data])
As you can see, this produces just about the exact same data as our chart — but it’s in a harder-to-digest format. When we don’t have the chart, it’s more difficult to understand a slope, especially when the data is as disparate as Ethan’s. In fact, here, without the chart, it would be difficult to even see that Ethan’s chart trends upwards over time.
What Can You Do With Slope?
In this slope, we know that roughly, the line is going up by about $18.20 a month. We can see that things are trending upward in a way that averages don’t indicate alone — because the slope takes into account how the scatter plot is moving over time. Based on current trends, it’s likely that, over the course of the next year, expenses will continue to go up this amount.
It’s easier to see if Ethan’s data was “simpler,” though simple data is rare:
Here you can see that there’s a very easy progression and that the slope increase is always 100.
A slope doesn’t give you discrete information that you can use to determine the next period. Rather, it consolidates all the points of data that you’ve put into it to determine the trends over time. This is something that can be important in many reports.
Perhaps more importantly, take a look at the following example:
In this expense sheet, we made one modification: We lowered the expenditures for a month. Now, we see on the chart that the slope is going downwards and that the slope is -3.42. We are immediately aware from the slope itself that Ethan’s expenses are going down rather than up.
A major reason to calculate slope isn’t just to know how fast a line is moving up or down but whether it’s moving up or down at all.
By calculating slope absent a chart, you can quickly see where the data is trending — without having to do any in-depth analysis.
It should be noted, though, that the slope of Ethan’s expenditures might not always be valid. When calculating information for reports in Google Sheets, the data that is presented is only as good as the data provided. If Ethan’s expenditures are truly volatile, then past behavior isn’t going to reflect future behavior. Slopes are best used when the data being compiled is expected to remain similar.
Google Sheets has a number of robust tools that can be used for analyzing and charting data. In this tutorial, you learned how to find slope on Google Sheets effectively.
With the use of advanced techniques such as slope, you can quickly analyze data for specific factors — such as determining whether a line is trending upward or downward over time. When making these calculations, you can also choose between a visual representation (such as charting) or just raw numbers (using algorithms).
Leveraging these tools provides more in-depth data that can be used for actionable insights.
You may also like the following Google Sheets tutorials: