Google Sheets can add a Line of Best Fit, also known as a Trend Line or Trendline, to certain charts and graphs. However, you can put in or type up a “Line of Best Fit” or “Trendline” equation. You have to make the trendline by customizing an existing chart.
You can do so using the two tabs of the Chart editor—”Setup” and “Customize”. The “Customize” tab allows you to change the design, aesthetics, or structure of the chart while “Setup” lets you pick different chart types and settings
What is a Line of Best Fit?
A line of best fit or trendline is used to chart predictions on a graph. It’s a line that passes to two or more points in a given series to show a trend from them. It also helps out in making predictions or forecasts based on the data given.
It shows things like revenue trending upwards or how long the average person is willing to wait or stay on the line to get to technical support on the phone.
A line of best fit:
- Is a line that best fits the data points, hence the name.
- Offers investors a forecast on the trajectory of a given investment’s value.
- It makes the data points more predictive when it comes to its descent or ascent.
- Statistically speaking, enables data forecasting based on what information you have on hand.
- Shows the presence of trends wherein if you have a period of time in mind for a growing or receding trend, the line confirms its impact on the series being followed.
How to Add Line of Best Fit in Google Sheets
Before you can add a trendline or line of best fit to any chart, you need to open a spreadsheet first and add data that could use such a trend line in the first place. Not all data sets or tables can work with the trendline option of Google Sheets.
Step 1: Open a new or existing spreadsheet.
Step 2: Enter relevant data. In our case, we’ve entered data regarding the percentage of calls that lead to hang ups or call abandons relative to how long the call is.
We can spruce the data up a bit to be more presentable too.
Step 3: Insert a chart. Do so by going to the “Insert” menu and selecting “Chart” or looking for the chart icon on the horizontal toolbar.
Edit it later to help fit with the “Line of Best Fit” theme we’re going for later.
Step 4: Here, we’ve changed it to a line chart. Incidentally, the point where 50% of callers hang up relative to the minutes they’ve waited—about 1 minute and 45 seconds—is the average patience of the average caller.
That means almost half of the callers to technical support tend to hang up after barely 2 minutes of staying on the line.
The remaining half who do stay on the line tend to take longer to hang up after breaching that 2 minute mark compared to most who hang up after merely 10 seconds or 30 seconds.
Step 5: On the Chart Editor that’s serving as the right-hand sidebar, click on the “Customize” tab then select the “Series” submenu to access additional options.
Scroll down further until you see the “Trend line” checkbox, right below the “Error bars” and “Data labels” checkboxes. That’s your “Line of Best Fit” checkbox too, but it’s just called by its other name.
You now have your Line of Best Fit cutting through your line graph right there. You can customize your trendline further in terms of:
- Labels
- Trendline Types
- Line, Color, Opacity, or Thickness
Step 6: If you select R² or R-squared, it will reveal to you how closely your line of best fit fits the data being presented. The closer it is to the value of 1, the better the fit. To acquire this option, activate the “Legend” option on the chart.
An R² of 0.952 shows the trend line is pretty close or practically a perfect fit to the data presented with barely a margin of error.
The Final Verdict
A line of best fit allows you to identify specific patterns, behaviors, or “trends” (hence its other name, trendline) of a given data set through a specific period of time.
Also, it’s actually quite easy to add a line of best fit to your chart—just go to “Customize”, “Series” and click the “Trendline” checkbox. The tricky part here is to understand what it signifies relative to the data set you’re putting a trendline to.
References:
- “Add & edit a trendline“, Google.com Support, Retrieved June 28, 2022
- David Johnson III, “Line of Best Fit with Google Sheets“, YouTube, February 4, 2017
- TechJunkie, “How to Add a Trendline to Google Sheets“, YouTube, November 9, 2021
- William Stanton, “How To Add A Trendline To Google Sheets“, Alphr.com, May 17, 2021
- “Trend Lines – How to Add Line of Best Fit in Google Sheets“, SpreadsheetPoint.com, December 9, 2021
Google Sheet: make a copy