Adding a trendline in Google Sheets takes four clicks inside the Chart Editor. This guide shows you exactly where the setting lives, how to customize the line type, how to add separate trendlines for multiple data series, and how to use the TREND function as an alternative approach.
What Is a Line of Best Fit?
A trendline, or line of best fit, is a line superimposed on a chart that shows the general direction of your data. It runs through or near the dots on a scatter plot chart in a way that minimizes the distance from each data point to the line itself.
Without a trendline, a spreadsheet chart of scattered data points can be hard to read. Take the scatter plot chart below. The dots show ad spend vs. sales figures, but it’s difficult to tell from the distribution alone whether the relationship is positive, negative, or flat.
Add a trendline and the picture changes immediately.
The line shows a clear upward direction. You can see which dots fall close to the trend and which are outliers. You can also extend the line past your existing data to make rough forecasts. That’s the core value: trendlines turn a cloud of data points into something readable.
Trendlines can be added to scatter, bar, line, and column charts in Google Sheets. The steps below use a scatter plot chart, which is the most common use case.
How to Add a Line of Best Fit in Google Sheets
The data below tracks an independent variable (number of ads posted) against a dependent variable (number of sales). A scatter plot chart is the right choice here because both columns are numeric and we want to visualize the relationship between them.
Step 1: Create a scatter chart
- Select your data range, including the column headers. In this example, that’s A1:B22.
- Click Insert in the toolbar at the top of the screen.
- Select Chart from the menu.
- A chart appears on the worksheet alongside the Chart Editor sidebar on the right. Google Sheets attempts to predict the right chart type based on your data.
- If Google Sheets did not select a scatter chart automatically, go to the Setup tab in the Chart Editor and open the Chart Type dropdown. Select the scatter chart option from the list. You’ll find it under Suggested or Other.
Your scatter plot chart is ready. Now add the trendline.
Step 2: Add the trendline
- Click the Customize tab in the Chart Editor.
- Open the Series dropdown inside the Customize tab.
- Scroll down until you see three checkboxes. Check the trendline checkbox.
The line of best fit appears on your scatter chart immediately.
In this example, the trendline runs upward from left to right. As ad count increases, sales increase. The y-intercept shows where the line would cross the vertical axis if extended back to zero ads, which gives you a rough baseline for expected sales with no advertising.
Customizing the Trendline
Once the trendline checkbox is checked, additional options appear below it. Here’s what each one does.
- Type: Choose from linear, exponential, polynomial, logarithmic, power series, or moving average. See the section below for guidance on which to pick.
- Line color: Change the trendline color to distinguish it from your data series.
- Opacity and thickness: Adjust how prominent the line appears against the dots.
- Label: Add a label to the trendline. Set it to “Use equation” to display the trendline equation directly on the chart.
- R2: Check this box to display the R2 value on the chart. R2 measures how closely the trendline fits the data. A value of 1.0 means a perfect fit. A value of 0 means the line explains none of the variation in the data. For most practical purposes, an R2 above 0.7 indicates a reasonably strong fit.
- If you select Polynomial, a degree selector appears. Degree 2 creates a single curve; higher degrees create more complex curves that fit tighter to the data but risk overfitting.
- If you select Moving Average, you can choose between Trailing and Centered averages and set the number of periods. Moving average trendlines are useful when your data is volatile and you want to smooth out short-term fluctuations.
Which Trendline Type Should You Use?
The type you pick changes the shape of the line and how it interprets your data. Here’s a quick reference.
| Type | Shape | Best used when |
|---|---|---|
| Linear | Straight line | The dependent variable changes at a steady rate as the independent variable increases |
| Exponential | Curved, accelerating | Growth or decline speeds up over time (population growth, compound interest) |
| Logarithmic | Curved, flattening | Rapid early change that levels off (learning curves, diminishing returns) |
| Polynomial | One or more curves | Data rises and falls rather than moving steadily in one direction |
| Power series | Curved, steady | A consistent rate of increase or decrease from an initial value |
| Moving average | Smoothed line | Volatile data where you want to reduce noise and see the underlying direction |
When in doubt, start with linear. If the R2 value is low, try exponential or polynomial and compare. There is no bell curve option in Google Sheets.
For a deeper look at how linear regression works behind the scenes, see the guide to linear regression in Google Sheets.
Adding Multiple Trendlines in Google Sheets
If your scatter plot chart has more than one data series, you can add a separate trendline for each one. Here’s an example with Product A and Product B plotted as two series.
To add a trendline to all series at once:
- Go to Chart Editor > Customize > Series.
- Make sure the dropdown at the top of the Series section reads Apply to all series.
- Check the trendline checkbox.
To add a trendline to one series only, select that series by name from the dropdown instead of using “Apply to all series,” then check the trendline checkbox. This lets you use different trendline types for different series in the same chart.
Using the TREND Function as a Line of Best Fit
The TREND function calculates projected y values based on existing x and y data. It’s primarily a forecasting tool, but you can also use it to generate trendline figures directly in your spreadsheet and then chart them alongside your original data.
The syntax is:
=TREND(known_data_y, [known_data_x], [new_data_x])
To use it as a line of best fit against current data, pass the known x values as both the known_data_x and new_data_x arguments. Here’s a before example with sales data in column B and time periods in column A.
The formula for this data:
=TREND(B2:B8, A2:A8, A2:A8)
The result fills a new column with smoothed values that increase at a steady rate, regardless of how the original data fluctuates.
Charting both columns together shows the trend clearly against the original dots.
You can then add a trendline to that series the same way as any other chart element.
The Chart Editor approach is faster for most use cases. The TREND function is worth knowing when you need the projected values in the spreadsheet itself, not just on the chart, or when you’re building a forecast beyond your existing data range.
Related Google Sheets Chart Guides
- How to Make a Scatter Plot in Google Sheets
- How to Do Linear Regression in Google Sheets
- How to Make a Histogram in Google Sheets
- How to Make a Line Graph in Google Sheets
- How to Make a Bar Graph in Google Sheets
- How to Use Sparklines in Google Sheets
Frequently Asked Questions
What is the difference between a trendline and a line of best fit in Google Sheets?
They are the same thing in Google Sheets. The Chart Editor calls it a trendline. Some textbooks reserve “line of best fit” specifically for linear fits, but in Google Sheets the trendline checkbox covers all six types: linear, exponential, logarithmic, polynomial, power series, and moving average.
How do I display the trendline equation on my chart?
In the Chart Editor, go to Customize, open Series, and check the trendline checkbox. Then find the Label dropdown and select “Use equation.” The trendline equation appears directly on the chart, showing the slope and y-intercept of the line.
What does R2 mean on a Google Sheets trendline?
R2 measures how well the trendline fits your data. A value of 1.0 is a perfect fit. A value of 0 means the trendline explains none of the variation in the data points. As a rough guide, R2 above 0.7 suggests a reasonably strong fit for most business and academic use cases.
How do I add a trendline to a bar or line chart in Google Sheets?
The same steps apply. Open the Chart Editor, go to Customize, open Series, and check the trendline checkbox. The trendline option is available for bar, line, column, and scatter charts.
When should I use a polynomial trendline instead of a linear one?
Use a linear trendline when your data moves steadily in one direction. Switch to polynomial when the data rises and then falls, or falls and then rises, creating a curve rather than a straight line. Start with degree 2 and increase only if the R2 value remains low.
Why is my trendline not showing up in Google Sheets?
The trendline checkbox only appears under the Series section of the Customize tab, not the Setup tab. If you don’t see it, make sure you’re in Customize and have scrolled down past the color and point size options to the three chart element checkboxes at the bottom of the Series section.
Can I use the TREND function instead of the Chart Editor trendline?
Yes. The TREND function calculates smoothed values in your spreadsheet that you can chart alongside your original data. The Chart Editor trendline is faster for visualization. The TREND function is more useful when you need the projected values in the spreadsheet itself or when building a forecast beyond your current data range.
How do I add separate trendlines for multiple data series?
Go to Chart Editor, open Customize, then Series. Select a specific series from the dropdown (instead of “Apply to all series”) and check the trendline checkbox. Repeat for each series. This lets you assign different trendline types to different series in the same scatter plot chart.