How to Find a Line of Best Fit in Google Sheets

Finding a line of best fit in Google Sheets is useful when you want to visualize trends in your dataset.

The line of best fit is also known as a trendline. We can use the Google Sheets Chart feature to visualize our dataset and understand the relationship between multiple variables.

A line of best fit is usually paired with a scatter plot of data points.  This line is computed using a regression analysis. This statistical method involves creating a model of the relationship between variables.

Let’s take a look at a quick example where we can add a line of best fit.

In this scenario, I am in charge of deciding the effect of our marketing budget on sales. We have historical data of monthly marketing expenses and monthly revenue. How do I easily see a positive trend in the relationship between both variables?

With the Trendline feature in Google Sheets charts, it’s easy to find the best fit line for any particular data set.

This use case is just one way to use the line of best fit in Google Sheets. Any type of analysis that requires understanding how two variables affect each other might benefit from a line of best fit visualization. 

Besides helping you understand relationships between variables, trendlines can also help you make forecasts based on data. You can estimate the value of y given a certain value of x.

Now that we know when we need to find a line of best fit in Google Sheets, let’s look into how to use it and work on an actual sample spreadsheet with a dataset.

 

 

A Real Example of Finding a Line of Best Fit

Let’s look at a real example of a scatter plot chart with a line of best fit being used in a Google Sheets spreadsheet.

In the example below, we have a dataset that shows the value of y for each value of x. Plotting these values on a scatter chart, we can begin to see a positive trend in the data. We’ve added a line of best fit or trendline to our data to see how close each data point is to the modeled relationship.

sample Line of Best Fit in Google Sheets

 

Besides the trendline, we can also get the R-squared value of the dataset. The R-squared value measures how well a certain linear regression model fits the dataset. 

In the example below, we added a label for our R-squared value. A value of 0.949 indicates that the trendline fits our data pretty well.

r-squared value in scatter chart

 

You can make your copy of the spreadsheet above using the link attached below. 

If you’re ready to try out creating your line of best fit in Google Sheets, let’s begin writing it ourselves!

 

 

How to Find a Line of Best Fit in Google Sheets

This section will go through each step needed to start setting up a line of best fit visualization in Google Sheets. This guide will show you how to convert the dataset seen earlier into a scatter chart with a trendline.

Follow these steps to find the line of best fit in Google Sheets:

  1. First, let’s add a new chart to our spreadsheet. We can find this option under the Insert menu.
    add a chart in your spreadsheet
  2. Next, make sure that the Chart type is set to Scatter.
    Line of Best Fit in Google Sheets uses a scatterplot
  3. Make sure that the data range of our chart corresponds to the range of our dataset. For example, our x and y values are in the cell range A1:B12.
    select the data range
  4. Our chart should now look something like this.
    scatterplot of variables
  5. In the Customize tab in the Chart editor, check the Trendline option.
    enable trendline to show Line of Best Fit in Google Sheets
  6. We also have the option to set additional formatting to our trendline. We can change the line color, opacity, and thickness.
    formatting Line of Best Fit in Google Sheets

  7. Our line of best fit shows the trend of our data through a Google Sheets scatter plot.
  8. We can also find out the R-squared value by checking the following options below.

 

Frequently Asked Questions (FAQ)

  1. How is the line of best fit computed?
    Google Sheets uses the Least Squares method to compute the line when using a linear trendline. This method finds the line that minimizes each data point’s squared distance from the line of best fit.
  2. Is the line of best fit always indicative of a trend?
    The line of best fit makes the best of the given data, but it does not necessarily affirm a trend. Completely random data points can still provide a positive or negative trend. 

 

That’s all you need to remember how to find a line of best fit in Google Sheets. Hopefully, this step-by-step tutorial shows how easy setting up a trendline for your data can be. 

The line of best fit is just one example of a statistical function we can use in Google Sheets. With so many other Google Sheets functions out there, you can surely find a function that best suits your requirements.

Are you interested in learning more about what Google Sheets can do? Make sure to subscribe to our newsletter to be the first to know about the latest Google Sheets guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

2 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like