How to Create a Line of Best Fit in Google Sheets
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.
X | Y = f(X) |
---|---|
3 | 27.247 |
5 | 127.141 |
8 | 509.875 |
9 | 728.709 |
13 | 2196.798 |
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 Customise from the horizontal options, and under the Customise 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 ploynomial 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 Insert Line of Best Fit in Google Spreadsheets
How to Find the Line of Best Fit on Google Sheets
- Select the Customize tab from the Chart Editor
- Select the Series drop-down menu
- Scroll down to the three checkboxes
- 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.
Contact Us