How to Insert Line of Best Fit in Google Spreadsheets

How to Find the Line of Best Fit on Google Sheets

1. Select the Customize tab from the Chart Editor
2. Select the Series drop-down menu
3. Scroll down to the three checkboxes
4. Click on the Trend Line checkbox

Creating plots is a crucial aspect of working with spreadsheet software like Google Sheets and Microsoft Excel. Frequently, we encounter scenarios where we need to analyze data obtained from experiments or external sources, and manually identifying trends can be challenging. In such cases, we employ the technique of polynomial fitting from Statistics to determine a mathematical function that optimally aligns with the provided data. The utilization of graphs and charts for data visualization remains a valuable method for extracting insights and understanding relationships among different data points.

What is a Line of Best Fit

The line of best fit represents the relationship between two variables in a scatter plot, allowing for predictions. It minimizes the distance between the actual data points and the predicted ones. The line of best fit can be straight or curved, depending on the data's spread. Tools like Google Sheets make finding the line of best fit easy.

In this article, we will learn how to find the line of best fit (Polynomial Fitting) for a given data in Google Sheets. Google Sheets provides many functions that help us to find the best-fit line and add it to the plot of given data.

How to Create Scatter Chart in Google Sheets

Before Adding a line of best fit in google sheets you need to create a scatter chart. Follow the below steps to learn how to create a Scatter chart in Google sheets

Step 1: Select Your Data

Highlight the data you want to plot, ensuring you include both the X (independent variable) and Y (dependent variable) values.

Step 2: Insert a Chart

Click on Insert in the top menu and Select Chart from the dropdown menu. Google Sheets will automatically create a chart based on your selection.

Step 3: Choose Scatter Chart

In the Chart Editor on the right, go to the Setup tab and From the Chart type dropdown menu, select Scatter chart.

Step 4: Customize Your Chart

Switch to the Customize tab in the Chart Editor to modify chart elements like titles, axis labels, and point styles.

Step 5: Add a Trendline (Optional)

If desired, you can add a trendline by selecting Series in the Customize tab and checking the Trend line option.

Step 6: Finalize and Position Your Chart

Once you're satisfied with the chart, click outside the Chart Editor to close it. You can resize or move the chart as needed within your spreadsheet.

How to Find the Line of Best Fit on Google Sheets

To determine the line of best fit in Google Sheets, you don’t need to rely on an equation. Instead, you can achieve this by making a few straightforward customizations to your scatter plot.

Step 1: Create a Scatter Plot of Some Data

Before adding a line of best fit, we need a scatter plot of the data for which we require the best-fit line. Now, for demonstration purposes, we shall use the following data.
If you want to learn How to Create a Scatter Plot of Some Data Click here

Step 2. Open New Spreadsheet

Now open a new spreadsheet in Google Sheets and add the above data to the spreadsheet. Then, click on Insert in the toolbar and then choose Charts

Step 3. Open Chart Editor

Now, this will create a default line chart and open the chart editor. Here, the first option will be to select the chart type from a drop-down menu. From this menu, select the scatter chart option.

Now, our plot will look as follows:

Step 4. Adding Line of Best Fit Trendline

Now, that we have our plot, we can add the line of best fit to our data. To do the same, we need to open the chart editor, which can be accessed by clicking on the three dots icon on the top right corner of the chart and then, choosing Edit the chart.

Step 5: Customize and Select Series

Now, in the chart editor, select Customize from the horizontal options, and under the Customize section, select Series as shown in the figure below.

Step 6. Check the Trend Line

Now, under the Series section, check the trend line option to add a trend line (line of best fit) to your chart. The default nature of the trend line is linear; which is not always the best option with the majority of real-life data. Thus, for the best fit in most cases, we should keep the trend line as a polynomial. This can be done by changing the type of trend line to Polynomial, as shown in the figure below.

Step 7. Preview Added Line of Best Fit

Once this is done, a line of best fit will be added to your chart. A thing to be noted is that Google sheet smartly detects the degree of polynomial of best fit however, this can be changed by the user according to their will. Now, our chart will look something like this.

Note: The blue curve represents the line of best fit for the data we used in this example.

How To Find the Slope of a Line of Best Fit In Google Sheets

Now, for those who need extra insights into their data, they can get the equation of the best-fit line used and also the R^2 value of the line. The R^2 value represents how well the best-fit line fits the given data. The closer the value is to 1, the better the fit. Absolute 1 means the line fits the data. To add these values, we only need to choose the options in the chart editor.

How to Find R2 in Google Sheets [R-Squared]

Under the Trendline Checkbox, there are options for Label and a checkbox for \bold{r^2}. Set the label to Use Equation from the dropdown menu and tick the checkbox for R-squared value.

Once, the above steps are done, the graph should look like this:

As we can see, the graph now displays the equation of the best-fit curve and the R-squared value.

Conclusion

In this article, we have learned the process of adding a line of best fit to a scatter plot in Google Sheets, using randomly generated data. Initially, we created a basic scatter plot using dummy data. Next, we outlined the steps to access the chart editor in Google Sheets, enabling us to incorporate the line of best fit into our scatter plot. Finally, we elucidated how to display the equation representing the line of best fit, as well as the R-squared value, directly on the chart.