How to Add a Line of Best Fit in Google Sheets
A line of best fit — also called a trendline — is a straight (or curved) line drawn through a scatter plot that represents the general direction of your data. It's a core tool in data analysis, helping you visualize patterns, spot correlations, and make rough predictions based on existing values. Google Sheets supports this natively through its chart editor, and the process is straightforward once you know where to look.
What a Line of Best Fit Actually Does
Before adding one, it's worth understanding what you're drawing. A line of best fit is calculated to minimize the distance between itself and all the plotted data points — specifically, it minimizes the sum of squared differences between each point and the line. This is known as least squares regression.
The result is a line (or curve) that represents the central tendency of your data's relationship. It doesn't connect the dots; it cuts through them in the most statistically representative way possible.
Google Sheets can generate several types of trendlines:
| Trendline Type | Best Used When |
|---|---|
| Linear | Data follows a roughly straight-line relationship |
| Exponential | Values grow or decay at an increasing rate |
| Polynomial | Data curves with one or more directional changes |
| Logarithmic | Growth is rapid at first, then levels off |
| Power series | Data follows a power-law relationship |
| Moving average | You want to smooth out short-term fluctuations |
Step-by-Step: Adding a Trendline in Google Sheets
1. Set Up Your Data
Your data needs to be in two columns — typically an independent variable (X) in one column and a dependent variable (Y) in another. For example, column A might hold dates or quantities, and column B might hold sales figures or measurements.
2. Create a Chart
- Highlight your data range
- Go to Insert → Chart
- In the Chart Editor, open the Chart type dropdown
- Select Scatter chart — trendlines work best and most intuitively on scatter plots, though they can also be added to line charts and bar charts
3. Open the Customize Tab
Once your chart is created, click on it to select it, then click the three-dot menu in the top-right corner of the chart and choose Edit chart. In the Chart Editor panel that appears on the right, click the Customize tab.
4. Navigate to Series
Under the Customize tab, click on Series to expand that section. Scroll down and you'll find a Trendline checkbox. Check it. 📈
A default linear trendline will appear on your chart immediately.
5. Configure the Trendline Options
Once the trendline is enabled, additional options appear:
- Type — Choose from Linear, Exponential, Polynomial, Logarithmic, Power, or Moving Average
- Line color and opacity — Adjust to make the trendline visually distinct from your data series
- Line thickness — Thicker lines are easier to read in presentations
- Label — You can display the trendline equation or the R² value directly on the chart
The R² value (coefficient of determination) is particularly useful. It ranges from 0 to 1 and tells you how well the trendline fits your data — the closer to 1, the stronger the fit.
Displaying the Equation and R² Value
To show the equation on your chart:
- In the Series → Trendline section of the Customize tab, find the Label dropdown
- Select Use Equation to display the formula, or enable the Show R² toggle to display the goodness-of-fit metric
This is useful when you want to use the trendline for forecasting — you can plug X values into the displayed equation to estimate Y values beyond your existing data range.
Using the TREND and FORECAST Functions as Alternatives 🔢
If you need the actual numerical values of a best-fit line — not just a visual — Google Sheets offers built-in functions:
=TREND(known_y's, known_x's, new_x's)— Returns predicted Y values based on a linear regression of your existing data=FORECAST(x, known_y's, known_x's)— Returns a single predicted value for a given X=SLOPE(y_range, x_range)and=INTERCEPT(y_range, x_range)— Return the individual components of the linear equation (m and b in y = mx + b)
These functions are valuable when the visual trendline isn't enough and you need numbers to feed into further calculations or separate reports.
Variables That Affect Which Trendline Type Works for You
Not every dataset behaves the same way, and choosing the wrong trendline type can produce a misleading visualization. The variables that matter most include:
- The nature of your data relationship — Is growth steady, accelerating, or plateauing?
- The number of data points — Polynomial trendlines with high degree values can overfit small datasets
- Your purpose — Visualizing a trend for a presentation has different requirements than using the equation for actual prediction
- Outliers in your data — A single extreme value can significantly skew a linear trendline
- Whether your data is time-series or categorical — Time-series data often benefits from moving averages rather than regression lines
A linear trendline applied to exponential data will technically appear on the chart but will represent the relationship poorly — and the R² value will confirm that. Conversely, a high-degree polynomial can appear to fit perfectly while being statistically meaningless on small datasets.
Different users working with the same feature will arrive at quite different results depending on their data structure, their analytical goals, and how carefully they match the trendline type to the underlying pattern in their numbers.