How to make a trendline in google sheets

Q: Can you show trendline equation in Google Sheets?

By default, Google Sheets doesnt display the equation of trendlines, but that doesnt mean youll have to estimate the slope or calculate it yourself. Theres an option hidden in the Chart Editor settings that makes it easy to add a trendline equation to a graph in Google Sheets.

As a maths teacher, I always liked collecting data and finding the strength of the relationship between the data. Making a scatterplot and finding the line of best fit or trendline was a piece of cake in Excel, but I could never do it in Google Sheets until I found a way to build a meaningful scatterplot, put in a trendline and find the correlation coefficient within a couple of clicks.

Table of Contents Show

How do you make a trendline for multiple series in Google Sheets?
How do you add a trendline?
Can you add a line of best fit in Google Sheets?
Can you show trendline equation in Google Sheets?

If scatterplots are a foreign concept or how to find the strength of the relationship between data, read on.

Here’s a helpful metaphor to illustrate how a scatterplot works. If we put a pot of water on some heat and measure the temperature at certain times, we would get two pieces of data that are related to each other. If we heat the pot for a longer period, the hotter the water will become. This is called a direct correlation.

If we collected the data and put it in a table we would have two variables and be able to draw a scatterplot of time versus temperature. The stronger the relationship between the two variables the stronger the correlation is. A perfect correlation is 1 and this can be represented by r2.

When we put data like this into a table and draw a scatterplot in Sheets, we would get a scatterplot that looks like this.

This is not correct as it is treating the data on the x-axis as text and not numbers. Follow these steps to change this:

Create a scatterplot by highlighting the data and clicking on Insert > Chart
Change the chart style from column to scatter chart
Click on Customise
Go to Horizontal axis
Un-check the box that says Treat labels as text

Click on Series
Check the Trendline box and the Show R2 box.

This now displays a scatterplot of your data, shows you the formula of the trendline or line of best fit and shows you the strength of the correlation.

How do you make a trendline for multiple series in Google Sheets?

Right click the series, choose Add Trendline, format the trendline. Again, right click the series, choose Add Trendline, format the trendline. Rinse and repeat.

How do you add a trendline?

Add a trendline.

Select a chart..

Select the + to the top right of the chart..

Select Trendline. Note: Excel displays the Trendline option only if you select a chart that has more than one data series without selecting a data series..

In the Add Trendline dialog box, select any data series options you want, and click OK..

Can you add a line of best fit in Google Sheets?

You can do this in Sheets through an option in the chart editor. After making a scatter plot, you can add a line of best fit by opening the chart editor by clicking the three dots in the top right corner.

Can you show trendline equation in Google Sheets?

By default, Google Sheets doesn't display the equation of trendlines, but that doesn't mean you'll have to estimate the slope or calculate it yourself. There's an option hidden in the Chart Editor settings that makes it easy to add a trendline equation to a graph in Google Sheets.