To get the equation of a line in Google Sheets, use two formulas: =SLOPE(B2:B11, A2:A11) for the slope (m) and =INTERCEPT(B2:B11, A2:A11) for the y-intercept (b), where column B holds your y-values and column A your x-values. Your line is then y = mx + b. To show the equation on a chart instead, insert a scatter chart, add a Trendline in the chart editor, and set its label to Use Equation.
Below are both methods step by step, how to build the full equation in one cell, how to check how well the line fits, and how to use it to predict new values.
Set up your data
Put your x-values in one column and the matching y-values in the next, with a header row. For example, A1 = “Hours studied” and B1 = “Score”, with data in rows 2 to 11. Make sure each row has both values and there are no text entries or blanks in the ranges you use.
Method 1: Use the SLOPE and INTERCEPT functions
- Click an empty cell, such as D2, and enter
=SLOPE(B2:B11, A2:A11). This returns m. - Click D3 and enter
=INTERCEPT(B2:B11, A2:A11). This returns b. - Write your equation as y = (value in D2)x + (value in D3).
Note the order: both functions take the y-values first and the x-values second. Swapping them gives a different (wrong) line.
Show the whole equation in one cell
To display the equation as text, enter:
="y = "&ROUND(SLOPE(B2:B11,A2:A11),3)&"x + "&ROUND(INTERCEPT(B2:B11,A2:A11),3)
Change the 3 to show more or fewer decimal places. If the intercept is negative, the result will read “+ -2.5”; that’s mathematically fine, or you can tidy it with an IF formula.
Method 2: Show the equation on a chart
- Select your data, including headers (for example, A1:B11).
- Click Insert > Chart.
- In the Chart editor, under Setup, set Chart type to Scatter chart.
- Click the Customize tab and open Series.
- Check Trendline. Make sure Type is Linear.
- Set Label to Use Equation. The equation appears in the chart legend.
- Optionally check Show R² to display how well the line fits.
Check how well the line fits
The R² value (coefficient of determination) runs from 0 to 1. Values near 1 mean the line describes the data well; values near 0 mean it doesn’t. Calculate it with:
=RSQ(B2:B11, A2:A11)
You can also use =CORREL(A2:A11, B2:B11) to see the correlation, which is positive when y rises with x and negative when it falls.
Get slope, intercept, and more with LINEST
=LINEST(B2:B11, A2:A11) returns the slope and intercept in two adjacent cells. Add extra arguments for more statistics: =LINEST(B2:B11, A2:A11, TRUE, TRUE) returns standard errors, R², and other values in a block. Leave enough empty cells to the right and below for the results.
Use the equation to predict values
- Manual:
=$D$2*E2+$D$3, where E2 is the new x-value. - FORECAST:
=FORECAST(E2, B2:B11, A2:A11)predicts y for x in E2 directly. - TREND:
=TREND(B2:B11, A2:A11, E2:E5)predicts several values at once.
Make the chart easier to read
- Axis titles: In Customize > Chart & axis titles, label the horizontal and vertical axes with your column names.
- Trendline style: Under Series, change the trendline color, opacity, and line thickness so it stands out from the points.
- Axis ranges: Under Vertical axis and Horizontal axis, set minimum and maximum values if the default scale hides the pattern.
When a straight line isn’t the right fit
A linear equation assumes y changes by the same amount for every step in x. If your points curve upward, level off, or swing up and down, a straight line will give misleading predictions. Plot the data first and look at the shape. A low R² or a clear curve is a sign to try a different trendline type, or to fit the line only to the range where the relationship looks straight.
Quick reference
| Function | Returns |
|---|---|
| SLOPE(y, x) | Slope (m) |
| INTERCEPT(y, x) | Y-intercept (b) |
| RSQ(y, x) | R², the fit of the line |
| LINEST(y, x) | Slope and intercept (plus statistics if requested) |
| FORECAST(x, y, x) | Predicted y for a new x |
| TREND(y, x, new_x) | Predicted y-values for several new x-values |
Troubleshooting
- #DIV/0!: All x-values are the same, so the slope can’t be calculated.
- #N/A or wrong results: The y and x ranges are different sizes, or contain text or blanks.
- No Trendline option: The chart isn’t a scatter, line, column, or bar chart. Change the chart type.
- Equation doesn’t appear on the chart: Make sure the legend is turned on under Customize > Legend.
Related: How to show equations in Google Sheets.
Frequently asked questions
What is the formula for a line of best fit in Google Sheets?
Use SLOPE and INTERCEPT, or add a linear trendline to a scatter chart with the equation label turned on.
Can I fit a curve instead of a straight line?
Yes. In the trendline settings, change Type to Polynomial, Exponential, or Logarithmic.
Why doesn’t my chart equation match SLOPE and INTERCEPT?
The chart label is rounded. The formulas give the full-precision values.
Kermit Matthews runs Live2Tech from just outside Philadelphia. He is an IT specialist with over 20 years of experience, from a university help desk to small-business IT, IT management and his own consulting practice, and has written tech guides full time since 2020. Steps are checked against the current software on a real device or the maker’s official documentation. More about Kermit.