We have learned how to create column charts, bar charts, scatter charts, and line charts in our previous tutorials. Google Sheets can automatically add a trendline into these four types of Google Charts. A trendline, also known as a line of best fit, is used to determine the overall pattern of your data in a specific chart.
In this tutorial, we will learn how to add and customize a trendline. Keep in mind that you must already have a chart created before you proceed to these steps to add a trendline.
Here, we will use the data set and Scatter chart discussed in our previous tutorial.
How to add a trendline?
Step 1: Double click on the chart.
Step 2: Go to the Customize tab under Chart Editor located on the right side of your Google Sheet. Click the Series menu to display other options.
Step 3: Tick on the Trendline. Google Sheet will automatically add a trendline of linear type in your chart.
How to customize the trendline?
Changing the type of the trendline
There are six types of trend lines available in Google Sheets. Each of these types has its own use. This Microsoft Support article discusses when to use these trendlines and we have summarized it below:
- Linear - for linear data sets. If your data shows an increasing or decreasing pattern, you can use the linear trendline.
- Exponential - for positive data values which rise or fall at increasingly higher rates.
- Polynomial - when there are fluctuations within your data set.
- Logarithmic - for nonzero data values that rise or fall quickly at the beginning then slow down or level off over time.
- Power Series - for data sets that increase at a certain rate.
- Moving Average - for smoothing the extreme fluctuations in your data set.
In case you want to change the type of your trendline, go to Type below the trendline option. Click on the down arrow menu. Then, choose one of the six types.
If you choose the polynomial trendline, you can add a polynomial degree. Just choose among the options on the drop-down menu under the polynomial degree.
If you choose the moving average trendline, you can choose either trailing or centered on the drop-down menu under Average Type. You may also change the corresponding Period located right beside the average type.
Formatting the aesthetics of the trendline
The color, opacity, and thickness of the trendline can be modified by clicking on each respective drop-down menu under the Trendline option.
Adding labels to your trendline
You can also add labels to your trendline. Labeling the trendline as an equation can help determine the mathematical model that will best fit the data. To do that, click on the drop-down menu under Label, then choose Use Equation.
Showing the correlation coefficient, R2
The correlation coefficient R-squared, R2, is a statistical measure and is used to determine the reliability of the trendline. The closer that R2 is to 1 or -1, the more accurate the trendline is. Just tick on “Show R2”, and Google Sheets will add it to the legend of your chart.
How to Add Trendline in Google Sheets
It's easy to add trendlines to see patterns in your google sheets charts. Trendlines can be added to the line, bar, column, or scatter charts.
- Double-click on a chart to open the Chart editor
- Click the Customize tab and choose Series
- In the "Apply to" dropdown choose the data series you want to add the trendline to.
- Scroll down and tick the Trendline box to add your tendline. if you don’t see this box it means trendlines don’t work with your data.
Adding a trendline is very simple. You just need to tick the trendline option, make some tweaks in formatting, and that’s it! Remember again that you need to have a prepared chart before adding a trendline. Without a chart, no trendline can be added!
Different types of trendlines are not always applicable to every data set. It’s a tough decision for some, but you need to study your data carefully and know the specific trendline that you need.
Sample Sheet for Adding a Trendline
You can use this sample sheet for your practice.
-Add Title and Label Legend Google Sheets Chart
-Line of Best Fit Google Sheets
-Finding the Slope in Google Sheets
-Create a chart with Multiple Ranges of Data