How to Add Trendline in Google Sheets?


Many spreadsheet software apps that handle huge amounts of data require trendlines or trend lines, and Google Sheets is no exception. It’s one of the best ways to identify specific patterns and behaviors of a given data set during a specific period of time. 

If you wish to know how to add a trendline in Google Sheets, it’s quite easy. The tricky part is parsing the knowledge imparted to you once you see that trendline in your graph.

Trendlines Everywhere! But What Are They For?

A trendline is used on a graph to chart predictions. It’s a line or graph that passes to two or more points in a series, which shows a trend (hence the term). It shows, for example, when revenue is trending upwards or downwards while predicting where it’s going.

 A trendline:

  • Gives investors a better idea of where the value of something is headed.
  • Is a line that best fits the data points, making it predictive of ascent or descent.
  • Allows you to forecast data based on the data you have on hand statistically speaking.
  • Showcases the presence of trends, such that if you have a time period in mind for a growing trend, the trendline can confirm its impact of the value you’re following.

Be careful in knowing which type of trendline is needed for your data set. If you have no idea which equation does or doesn’t apply for your graph, you might get the wrong stats. What’s worse is if it’s just syntax, formatting, or typos that are causing the error.

How to Add Trendline in Google Sheets

Step 1: Open a spreadsheet in Google Sheets. Perhaps get one where you’ve made a chart with the data available for the sake of  having something to put a trendline into.

Add Trendline Step 1 - Open spreadsheet

Step 2: Double-click an existing chart or graph. From there, on the right, click “Customize” then click “Series“.

Add Trendline Step 2 - Double-click table go to Customize and Series

Step 3: Click on the “Apply to” box to choose which data series you want to apply the trendline to. For now, let’s click on “Apply to all series“.

You can go back and click on Microsoft, Apple, Amazon, or TESLA to see their individual trendlines if you wish.

Add Trendline Step 3 - Choose which data series to apply the trendline to

Step 4: Click the “Trendline” checkbox to introduce trendlines to your data. If this option isn’t present, that means trendlines aren’t applicable to your data, graph, or table.

Add Trendline Step 4 - Click the Trendline checkbox

Use it on data that requires line graphs and involves changing data over time, such as the revenue of the biggest companies in the world for roughly a decade.

Step 5: Here we’ve made all the data series use the column chart then isolated the Microsoft trendline. 

Add Trendline Step 5 - Edited the chart for clarity and isolated the Microsoft trendline

The trendline basically covers Microsoft’s stable but not as dynamic growth compared to its peers Apple, Amazon, and TESLA. If you’re an investor, it gives you a good idea of where the data is headed, even in light of outside factors like the pandemic affecting Apple and Amazon revenue better.

How to Edit a Trendline in Google Sheets

Step 1:  Let’s go back to the same chart. Double-click it to get the “Customize” toggle on the “Chart editor” sidebar. Also, again, click “Series“.

Edit Trendline Step 1 - Double-click table go to Customize and Series again

Step 2: Since we’re focusing on Microsoft, choose the Microsoft series on the “Apply to” box.

Edit Trendline Step 2 - Choose Apply to Microsoft

Step 3: Scroll down the Chart editor. You can edit the following under “Trendline“.

  • Labels
  • Trendline Types
  • Line, Color, Opacity, or Thickness
Edit Trendline Step 3 - The things you can edit under Trendline

If you pick R-squared or R2, it will show you how closely the fit of the trendline to your data. The closer to “1” your R2 is the better the fit when push comes to shove. This option is only available when adding a legend to your chart.

Edit Trendline Step 4 - Add R-squared to Trendline options

In the case of the chart we’re using, it’s pretty darn close at 0.896. What does this mean? Simply put,

an R2 value of 1 or near 1 means the model fits the data.

It explains all the variation of the outcome variable. There’s correlation between the trendline and the data. As opposed to an R2 value of 0, which shows the regression model doesn’t explain any variation of the outcome variable.

Or an R2 value of -1, there is negative correlation between the variables. The model (or any forecast you can glean from it) isn’t reflective of the data at all.

Example Spreadsheet: Make a copy of the example spreadsheet

What Else You Need to Know

It’s easy to add trendlines via Google Sheet. Just open a chart, particularly one involving dates or years, then go to “Customize” and “Series” to choose which data series to add the trendline on. Finally, scroll down and just press the check on “Trendlines”.

If you can’t find that checkbox then you have the type of data that you can’t measure trendlines on. If it’s supposed to have trendlines, check the formatting of the data to see if that fixes things. Knowing that trendlines mean and the complex formulas behind them is much tougher to understand though.

References:

  1. Add & edit a trendline“, Google.com Support, Retrieved June 28, 2022
  2. TechJunkie, “How to Add a Trendline to Google Sheets“, YouTube, November 9, 2021
  3. William Stanton, “How To Add A Trendline To Google Sheets“, Alphr.com, May 17, 2021

Andy Avery

I really enjoy helping people with their tech problems to make life easier, ​and that’s what I’ve been doing professionally for the past decade.

Recent Posts