Visualizing data using charts and graphs can be a great way to understand your data and relationships between the variables in the data.
Moreover, adding a trend line (also called the line of best fit) to the chart can help you gain a deeper understanding of the chart.
In this tutorial I will show you how to add a line of best fit in Google Sheets, to help you gain better insights into your data.
Table of Contents
What is a Line of Best Fit, and Why Add a Trendline?
A trend line, or a ‘line of best fit’ is one that is superimposed on a chart to understand trends within the data.
These lines can help you understand the direction of the data, make forecasts and understand relationships between data elements.
For example, if you look at the scatter chart below, you can somewhat tell how the data points are distributed but it’s hard to tell if there’s an upward or downward trend. It is also a little tough to make proper sense of the distribution.
What happens if I add a trendline to this chart?
Adding a trend line, as shown above, helps bring a lot of things into perspective.
We can now see that the data is moving in an upward direction.
We can also extrapolate the line to make predictions about future data points, and I can tell which data points are outliers, or completely far away from the general trend.
Trend lines can be added to bar, line, column, or scatter charts. In the next section I will show you how to add a trendline in Google Sheets to fit with a scatter chart.
How to Add a Line of Best Fit in Google Sheets
Before I show you how to add a line of best fit, let us first quickly go over how you can create a chart for the data shown below:
In the above image, I have data on the number of times ads were posted and the number of sales made.
To visualize this type of data, a scatter chart would be a great option.
Here’s how you can quickly use this data to create a scatter chart in Google Sheets.
Creating a Scatter Chart in Google Sheets
To create a scatter chart based on our given data, follow the steps below:
- Select the data range, including the column headers. In our case, select the range A1:B22.
- Click on the Insert menu from the menu bar.
- Select the Chart option from this menu.
- You will see a chart displayed on the worksheet and a Chart Editor sidebar on the right side of the window.
- Google usually attempts to predict and recommend a chart depending on your selected data. If the chart displayed is not a scatter chart, then go to step 6. Otherwise, stop here.
- To convert your displayed chart to a scatter chart, select the Setup tab from the Chart Editor and click on the drop-down menu under ‘Chart Type’.
- From the chart options, select “Scatter Chart”, which you should be able to find under the Suggested or Other category.
You should now see a Scatter chart on your worksheet. Next, it’s time to add a line of best fit so you can understand the trends in your data.
How to Find the Line of Best Fit on Google Sheets
There is no Google Sheets line of best fit equation you have to use. You just have to make some customizations to the chart.
The Chart editor sidebar consists of two tabs – Setup and Customize. The Customize tab has different options to help you customize different chart settings.
To add a line of best fit to the scatter chart that I created, you need to access this Customize tab. Follow the steps below:
- Click on the Customize tab of the Chart Editor.
- Select the Series drop down menu.
- If you scroll down the drop down menu, you will see three checkboxes.
- Select the ‘Trend line’ checkbox.
This will display a trend line (or line of best fit) across your scatter chart.
In our example, you will notice the trend chart shows an upward trend. That means as the number of ads are increased, sales start going up.
Making Changes to the Trend Line
Now that you know how to find the lineIf you want to further customize the trend line, here’s what you need to do. Under the trend line checkbox, there are a number of options to customize the trend line. For example, you can:
- Change the type of trend line. You have the option to select linear, exponential, polynomial, logarithmic, power series, and moving average trend lines.
- Change the line color.
- Change the opacity and thickness of the trend line.
- Change the label for the trend line.
- Display the R2 value. This value helps you see how closely the trend line fits the data. The closer this value is to 1, the closer the fit.
- If you select Polynomial as your trend line type, you will also get the option to select the polynomial degrees.
- If you select Moving Average as your trend line type, you will get the option to select the type of average, i.e. Trailing or Centered. You can also select the number of periods for the Moving Average trend line.
Play around with the different options until you finally obtain a trend line that provides you with enough insight on your data.
Adding Multiple Trendlines in Google Sheets
If your scatter plat has more than one series, you can add a trendline for each set of data. Let’s take a look at an example:
Say we wanted a trendline for both Product A and Product B. Here’s how you’d do it:
- Navigate to Chart editor > Customize > Series
- Make sure the drop-down list has Apply to all series selected
- Check the Trendline box
You can also add trendlines to each individual data set instead of all at once by selecting the series from the dropdown menu instead of Apply to all series.
Using the TREND Function to Make a Trendline
The TREND function is used to show forecasting potential future values, but you can also use it as a trendline Google Sheets to make lines of best fit in existing data. It operates under the following syntax:
=TREND(known_data_y, [known_data_x], [new_data_x])
To use it in a data set, you input the know y and x values in the respective places, you’d also provide new x values and it would predict possible new y values based on the existing data. The trick to getting it to work with current values is to use the knows x values as the new_data_x value too. Here’s an example of how to add a best fit line in Google Sheets with the TREND function, let’s say we have the following values in our sheet:
We could substitute the x and y values into the trend function like so:
=TREND(B2:B8,A2:A8,A2:A8)
Note that the know_data_x and new_data_x are the same. It would give the following results:
You may notice that the trendline figures are increasing at a steady rate instead of sporadically like the initial data. If we were to chart the data it would show a clear trend.
Of course, you could then ad a physical line to the data in the same way we did for the other examples.
This is a convoluted way to build a line of best fit in Google Sheets, and we’d recommend only using the TREND function for forecasting.
How to Insert a Trendline in Google Sheets FAQ
Does Google Sheets Have Line of Best Fit?
Yes, but it’s called a trendline instead,
How Do You Add a Line of Best Fit on Google Sheets? / Can You Add a Trendline in Google Sheets?
Here’s how to make a trendline in Google Sheets.
- Open the Chart editor and head to Customize
- In the Series tab, check the box that says Trendline.
Is Trendline the Same as Line of Best Fit?
Yes, although some may argue that a line of best fit is only for linear equations, in Google Sheets, they mean the same thing.
Where Is R2 in Google Sheets? / How Do You Find the Slope of a Line of Best Fit In Google Sheets?
Finding the slope in Google Sheets only takes a few clicks.
- Navigate to Chart editor > Customize > Series
- Make sure Trendline is checked
- To show the slope equation, change the Label to Use equation
- To show the R2 slope, Check the R2 box
Which Equations Should You Use From the Types Menu?
The equation you select from the Types dropdown menu will affect the shape of the best fit line in Google Sheets. Here’s a brief explanation of each:
- Linear: Creates a straight line of best fit from the data points
- Exponential: An increase or decrease from the initial data point at an increasing rate.
- Logarithmic: A rapidly increasing or decreasing set of data that flattens out over time
- Polynomial: For varied data
- Power series: For a steady increase or decrease from initial value
- Moving average: Smooths out volatile data
There is no bell curve option.
Wrapping Up the Line of Best Fit Tutorial
In this tutorial, I showed you how to add a line of best fit in Google Sheets to analyze and make effective inferences about your data.
The line of best fit (or trend line) can help you observe trends and patterns in your data, can help you understand how closely your data points relate to one another and it can help you identify outliers in the data, which would have been otherwise difficult to find out.
I hope this tutorial was helpful for you.
Other Google Sheets tutorials you may like:
