How to Do Linear Regression in Excel: SLOPE to LINEST

Linear regression in Excel finds the straight line that best fits two columns of numbers, so you can see how much one changes when the other does and predict new values. Excel gives you four ways to do it: quick functions (SLOPE, INTERCEPT, RSQ), the all-in-one LINEST, a chart trendline and the Analysis ToolPak. This guide is for students, analysts and job seekers who need a regression without a statistics package, and it uses one small data set throughout.

What does linear regression tell you?#

A simple linear regression fits the line y = intercept + slope × x through your data. Here, x is the input you know and y is the result you want to explain or predict.

The example is six deliveries, with the distance in km and the delivery time in minutes:

The regression answers three questions:

• Slope: how many extra minutes each extra km adds.
• Intercept: the predicted time at 0 km, which you can think of as fixed handling time.
• R² (R squared): how well the line fits, from 0 (no fit) to 1 (every point on the line).

Quick method: SLOPE, INTERCEPT and RSQ#

Three functions give you the line and its fit. All three take the y values first, then the x values:

=SLOPE(C2:C7,B2:B7)
=INTERCEPT(C2:C7,B2:B7)
=RSQ(C2:C7,B2:B7)

So the fitted line is Minutes = 9.5 + 2.5 × Km. RSQ is the square of the correlation coefficient; the guide to finding the correlation between two columns explains that number. These functions work in every current version of Excel.

How to predict a value with FORECAST.LINEAR and TREND#

To predict y for a new x, use FORECAST.LINEAR. With 12 km in E2:

=FORECAST.LINEAR(E2,C2:C7,B2:B7)

The result is 39.5 minutes (9.5 + 2.5 × 12). The new x comes first, then the known y values, then the known x values. FORECAST.LINEAR was added in Excel 2016; older files use FORECAST, which gives the same answer.

TREND does the same for many new x values at once. In Microsoft 365 and Excel 2021, =TREND(C2:C7,B2:B7,E2:E4) spills one prediction per value in E2:E4. You can also write the line yourself, =$H$2+$H$1*E2 with the slope in H1 and the intercept in H2, and fill it down. The $ signs keep the slope and intercept fixed while E2 moves.

How to use LINEST for the full regression#

LINEST returns the whole regression in one array. Its syntax is:

=LINEST(known_y's, [known_x's], [const], [stats])

=LINEST(C2:C7,B2:B7) returns two cells side by side: 2.5 and 9.5, the slope then the intercept. Set the 4th argument to TRUE to add statistics:

=LINEST(C2:C7,B2:B7,TRUE,TRUE)

This returns 5 rows by 2 columns. Row 1 is the slope and intercept, row 2 their standard errors, and row 3 holds R² (0.993) and the standard error of the estimate (about 0.71 minutes). Rows 4 and 5 hold the F statistic, degrees of freedom and sums of squares.

In Microsoft 365 and Excel 2021, LINEST spills into the cells beside and below it, so leave them empty to avoid a #SPILL! error. In older versions, select a 5 × 2 range first, type the formula and press Ctrl+Shift+Enter (Mac: ⌘+Shift+Return). For more than one input, such as distance and number of stops, give LINEST an x range of two or more side-by-side columns. The slopes then come back in reverse column order.

Add a trendline to a scatter chart#

A chart shows whether a straight line makes sense at all. Select B1:C7, choose Insert > Scatter, then:

1. Click a data point and choose Add Trendline (right-click on Windows; Control-click on a Mac).
2. Pick Linear.
3. Tick Display Equation on chart and Display R-squared value on chart.

The chart shows y = 2.5x + 9.5 and an R² of about 0.993, the same as the functions. Use a scatter chart, not a line chart, because a line chart treats x as evenly spaced labels. The guide to line charts in Excel explains when each fits, and scatter plots in Excel covers trendline options.

Run a regression with the Analysis ToolPak#

The Analysis ToolPak writes a full regression report with one dialog. Turn it on under File > Options > Add-ins, choose Excel Add-ins and click Go (on a Mac: Tools > Excel Add-ins), then tick Analysis ToolPak.

Then choose Data > Data Analysis > Regression, set Input Y Range to C1:C7 and Input X Range to B1:B7, tick Labels and pick an output cell. The report includes R², the coefficients, their standard errors, t statistics and p-values, and optional residuals. The output is fixed values, not formulas, so run it again if the data changes.

How Excel calculates the slope#

The slope is the sum of (x gap × y gap) divided by the sum of (x gap squared), where each gap is the distance from that column's average. With the gaps in D2:D7 and E2:E7 (=B2-AVERAGE($B$2:$B$7) and =C2-AVERAGE($C$2:$C$7)):

=SUMPRODUCT(D2:D7,E2:E7)/SUMPRODUCT(D2:D7,D2:D7)

The top is about 113.33 and the bottom about 45.33, so the slope is 2.5. The intercept is then the average time minus the slope times the average distance: 23.67 − 2.5 × 5.67, which is 9.5 before rounding. Building it by hand like this is a good check when a result looks odd. The guides to SUMPRODUCT in Excel and the AVERAGE function cover the two building blocks, and standard deviation in Excel uses the same gaps from the average.

Common linear regression mistakes#

Check these before you trust a regression:

1. x and y swapped. SLOPE, INTERCEPT, RSQ and FORECAST.LINEAR all want y before x. Swapping them gives a different line.
2. Predicting far outside the data. The data runs from 2 to 10 km. A prediction for 50 km assumes the pattern holds that far, which it may not.
3. Fitting a line to a curve. A high R² on a curved pattern can still mislead. Look at the scatter chart first.
4. Reading cause into a fit. Two columns can move together because of a third factor. Regression measures the pattern, not the reason.
5. Ranges of different sizes or with text. Keep x and y the same length and remove text and blanks from the rows you use.

For trends over time rather than between two measures, a moving average is often the simpler tool.

Practise it#

Regression rests on averages and on references that stay fixed when you fill a formula down. Practise AVERAGE, MIN and MAX in a live spreadsheet: you type the formula and it checks it instantly. Then practise relative and absolute references, the $ skill behind the gap columns in the exercise on this page.