Google Sheets is undoubtedly a powerful tool that can be used for creating spreadsheets, charts, and analyzing data.
One very useful feature is the ability to find the slope of a trendline for data in your spreadsheet.
In this tutorial, I will show you how to find the slope in Google Sheets.
Table of Contents
What is Slope?
Before I go over how to calculate the slope of a line, let me briefly explain what the slope is.
Essentially, the slope is a measure of the steepness of a line. The slope will describe the direction of the line (positive or negative) as well as how steep it is.
Here is an example of a positive and negative trendline slope plotted and a Y-axis and X-axis graph.
The slope will tell you how the line rises or falls across a given distance in the graph.
The slope is expressed as a number and the general formula is:
Slope = Rise/Run
When dealing with slopes, larger numbers mean steeper slopes. For example, a slope of +50 would mean that the Y-axis goes up 50 for every single unit we move on the X-axis.
We really don’t even need a graph to find the slope, we can do it in a spreadsheet with just two columns that represent the Y and X variables.
Generally, when we are looking at the slope in a spreadsheet, we are looking at linear regression, which is a way of modeling and analyzing a relationship between two or more variables.
Finding the Slope in Google Sheets
Now that we have covered the basics of what a slope is, let’s go over how to find it in our spreadsheet.
Here are the steps to create a chart and show the trendline in Google Sheets:
- First, you will need to highlight the data that you want to show the slope of. Highlight the range of cells that contains your data
2. In the top menu select Insert and then select Chart. You can also select the Chart icon in the toolbar
3. Google Sheets will automatically choose the chart that they feel will best fit your data, but in my example, I want to create a scatter chart. The chart editor should open on the right-hand size of your screen (if not double click on your chart). In the setup tab, select the down arrow in the Chart type section
4. Scroll down until you find Scatter chart and select that
5. Select the Customize tab at the top of the Chart editor
6. Select Series
7. Select the box for Trendline
8. You will now have a completed chart in your spreadsheet that shows the blue trendline (slope). It will look something like the below example:
Being able to visualize the trendline of your graph in Google Sheets can really help you understand your data better. With a little knowledge, this can be set up by following the above steps in a matter of minutes.
But sometimes visualizing the slope is not enough, you actually want to find the value of the slope. We will cover that below.
How to Figure out the Slope of a Graph
Previously we showed you how to add a trendline to your chart in Google Sheets so you can see the slope, but if you want to know the exact slope equation, there are a few other steps we have to take:
1. In the Chart editor select the Customize tab
2. Select Series
3. Select the drop-down option under Label
4. Select Use Equation
5. The equation used to calculate the trendline will now appear at the top of your chart. Everything to the left of the *x is the slope, so in this dataset, my slope is 1155.
Calculating Slope without a Graph
You don’t even really need to create a chart if you want to calculate the slope for your dataset, you can just use the SLOPE function.
The SLOPE function will take your Y data and X data and calculate the slope of the line resulting from linear regression.
The syntax of the SLOPE function is:
- data_y – this is the range that represents the dependent data
- data_x – this is the range that represents the independent data
Here is how this function would be used with our previous data:
The exact formula I use in this example is:
Remember that the Y-data has to be the first argument in the function, followed by the X-data. You can see that my slope for this dataset is 1155, which is the same slope as when we used the chart to show the equation.
You can now see there are many different ways to find the slope of a trendline in Google Sheets. You can use the SLOPE function or have sheets automatically add the slope to your chart.
An important thing to pay attention to when dealing with slopes is to make sure your dates are reading correctly. Sometimes Google Sheets will not be able to read your date format correctly, and this can cause issues when dealing with slopes. The safest way to deal with this is just to number your dates similar to how I did in the previous example.
More Google Sheets Tutorials:
How to Add Error Bars
How to Make a Histogram
How to Create a Candlestick Chart
How to Use the LINEST Function