excel regression formula

Excel Regression Formula: How to Run Linear Regression in Excel Fast

By Emily Clark
AD

If you have ever searched for an Excel regression formula, you may have expected a single function named REGRESSION. Excel does not work that way. Instead, it gives you a powerful set of built-in functions that can calculate slopes, intercepts, predictions, and full regression statistics without installing an add-in.

This guide breaks down the most useful Excel regression formulas, explains how to use them for simple and multiple linear regression, and shows how to read their output. Whether you are forecasting sales, analyzing test scores, or modeling operational costs, these formulas turn raw columns of data into actionable insight.

Essential Excel Regression Formulas

At the heart of regression in Excel are functions such as LINEST, SLOPE, INTERCEPT, RSQ, STEYX, TREND, and FORECAST.LINEAR. Each one answers a different question, and together they cover everything from a quick trend line to a complete multiple regression model.

AD
The Ultimate Guide to Regression Analysis in Excel
The Ultimate Guide to Regression Analysis in Excel
Regression Analysis Formula
Regression Analysis Formula
How to Model Salary Regression Analysis in Excel (2 Ways) - ExcelDemy
How to Model Salary Regression Analysis in Excel (2 Ways) - ExcelDemy

These functions assume a linear relationship between the dependent variable, usually called Y, and one or more independent variables, called X. They work with ranges or arrays, and in modern versions of Excel they can spill results across multiple cells automatically.

Using LINEST for Simple and Multiple Regression

LINEST is the workhorse of Excel regression formulas. Its syntax is LINEST(known_y's, [known_x's], [const], [stats]). For a simple regression with Y in B2:B11 and X in A2:A11, you can enter =LINEST(B2:B11,A2:A11,TRUE,TRUE) to return both the slope and intercept along with a full statistics array.

Regression Formula
Regression Formula
EXCEL FORMULA FOR DATA ANALYTICS 2026
EXCEL FORMULA FOR DATA ANALYTICS 2026
6 Simple Steps for Conducting Regression in Excel
6 Simple Steps for Conducting Regression in Excel

For multiple regression, place each independent variable in its own column and use a range that includes all of them. For example, =LINEST(D2:D100,A2:C100,TRUE,TRUE) analyzes three predictors. The coefficients appear in reverse order of the X columns, with the intercept last, so always map them back carefully before writing your prediction equation.

SLOPE, INTERCEPT, and RSQ for Quick Analysis

When you only need the equation of a simple linear trend, SLOPE and INTERCEPT are faster than LINEST. SLOPE(known_y's, known_x's) returns the change in Y for a one-unit change in X, while INTERCEPT(known_y's, known_x's) returns the value of Y when X is zero. RSQ(known_y's, known_x's) returns the coefficient of determination, which tells you how much of the variation in Y is explained by X.

AD

For example, =SLOPE(B2:B13,A2:A13) might return 2.5, and =INTERCEPT(B2:B13,A2:A13) might return 1000. Your simple model becomes Y = 1000 + 2.5 times X. RSQ can then confirm whether that line is a strong fit, but remember that RSQ is limited to a single independent variable, so use LINEST when you have multiple predictors.

Regression Analysis in Excel
Regression Analysis in Excel
How to Calculate Standard Error of Regression in Excel (with Easy Steps) - ExcelDemy
How to Calculate Standard Error of Regression in Excel (with Easy Steps) - ExcelDemy
Regression Algorithms Cheat Sheet: 10 Essential ML Models
Regression Algorithms Cheat Sheet: 10 Essential ML Models
a poster with the words properties of regression in blue and white on it
a poster with the words properties of regression in blue and white on it
Top 21 Excel Formulas
Top 21 Excel Formulas
the top 30 excel formulas for data and texting are shown in this poster
the top 30 excel formulas for data and texting are shown in this poster

Step-by-Step Regression in Excel with Formulas

Before you write any formula, prepare your data. Place the dependent variable in one column and each independent variable in adjacent columns, keep the same number of rows, and remove text values or blank cells from the input ranges. Clear labels at the top of each column make formulas easier to audit later.

Consider converting your range into an Excel Table with Ctrl+T. Structured references such as Sales[Revenue] and Sales[AdSpend] make formulas more readable and automatically expand when new rows are added. If error handling matters, wrap regression formulas in IFERROR to display a friendly message instead of a cryptic error code.

AD

Building a Simple Linear Regression Model

Start with one X column and one Y column. Calculate the slope with =SLOPE(Y_range,X_range), the intercept with =INTERCEPT(Y_range,X_range), and the fit with =RSQ(Y_range,X_range). Then create a predicted Y column using a formula like =$B$15+$B$16*A2, where B15 holds the intercept and B16 holds the slope.

Regression Analysis Formula
Regression Analysis Formula
How to use a Linear Regression Analysis in Excel?
How to use a Linear Regression Analysis in Excel?

Suppose you are analyzing monthly sales against advertising spend. If the slope is 2.5 and the intercept is 1000, then each additional dollar of ad spend is associated with 2.5 more dollars in sales, starting from a baseline of 1000. To predict sales for a 5000-dollar campaign, use =INTERCEPT(Y_range,X_range)+SLOPE(Y_range,X_range)*5000, and compare that prediction with the actual result when the month closes.

Running Multiple Regression with LINEST

Multiple regression follows the same logic but with more than one X column. Arrange Y in a single column and place X1, X2, and any additional predictors in adjacent columns. Enter =LINEST(Y_range,X_range,TRUE,TRUE). In Microsoft 365, the result spills into a 5-row by k+1 column array, where k is the number of predictors.

AD

The first row of the LINEST output contains the coefficients. The last value in that row is the intercept, and the values before it are the slopes for the X variables in reverse column order. The second row holds standard errors, the third row contains R-squared and the standard error of the estimate, the fourth row shows the F statistic and degrees of freedom, and the fifth row reports the regression and residual sums of squares.

Advanced Regression Formulas and Tools

Once you are comfortable with the basic formulas, you can extend your analysis with prediction functions and diagnostic calculations. Excel also includes the Data Analysis ToolPak, which produces a regression report with p-values, confidence intervals, and residual output, but you can recreate much of that report with formulas if you prefer a transparent spreadsheet model.

To enable the ToolPak, go to File, then Options, then Add-ins, select Excel Add-ins, and check Analysis ToolPak. After that, the Data Analysis command appears on the Data tab. Use it when you need a standardized output table, and use formulas when you want a live model that recalculates as your data changes.

TREND and FORECAST.LINEAR for Predictions

TREND(known_y's, known_x's, new_x's, [const]) returns predicted Y values along a linear trend and supports multiple X variables. If your historical data occupies A2:C20 and your new inputs are in A21:C21, then =TREND(B2:B20,A2:C20,A21:C21) produces the forecast for that new row. In dynamic Excel, you can feed TREND a block of new X rows and receive an array of predictions.

FORECAST.LINEAR(x, known_y's, known_x's) is simpler and works only for one independent variable. Use it when you have a single X value to predict, such as =FORECAST.LINEAR(5000,B2:B13,A2:A13). For seasonal or time-series forecasting, explore FORECAST.ETS, but for straightforward linear regression, TREND and FORECAST.LINEAR are reliable choices.

Diagnostic Formulas: Standard Error, F-Statistic, and p-Values

LINEST with stats set to TRUE returns standard errors, R-squared, the F statistic, degrees of freedom, and sums of squares. To calculate a t-statistic for each coefficient, divide the coefficient by its standard error. Then convert that t-statistic into a p-value with =T.DIST.2T(ABS(t_stat),df_resid), where df_resid comes from the fourth row of the LINEST output.

For the overall model, the F statistic tests whether at least one predictor is useful. Calculate its p-value with =F.DIST.RT(F_stat,df_reg,df_resid), where df_reg equals the number of predictors and df_resid equals n minus k minus 1. Confidence intervals can be built as coefficient plus or minus T.INV.2T(alpha,df_resid) times the standard error, giving you a clear range for each estimated effect.

Common Mistakes and Best Practices

Regression formulas are only as good as the data and assumptions behind them. A high R-squared does not prove causation, and a statistically significant coefficient does not guarantee practical importance. Always plot your data, inspect residuals, and consider whether a linear model makes sense for the relationship you are studying.

Watch for multicollinearity when predictors are highly correlated, because it can make individual coefficients unstable. Avoid extrapolating far beyond the range of your observed X values, and be cautious about overfitting a model with too many predictors for the number of observations you have.

Handling Blanks, Errors, and Non-Numeric Data

Excel regression functions require numeric input. Blank cells, text labels, or error values inside a range can produce unexpected results or a #VALUE! error. Clean your data before modeling, or use functions such as FILTER and IFERROR to create a tidy input range that contains only valid numbers.

If your relationship is curved, create transformed columns such as X squared, log of X, or log of Y, and include them as additional predictors in LINEST. This keeps you within the linear regression framework while capturing nonlinear patterns. Always keep the original columns so you can document and reverse the transformation when interpreting results.

Interpreting R-Squared and Coefficients Correctly

R-squared measures the proportion of variance in the dependent variable explained by the model. An R-squared of 0.85 means 85 percent of the variation is explained, but it does not tell you whether the model is appropriate or whether the predictors are causally related. For multiple regression, compare adjusted R-squared values when adding or removing variables.

Coefficients describe the expected change in Y for a one-unit change in a predictor, holding other predictors constant. Check the sign, magnitude, and standard error of each coefficient. A small p-value suggests the effect is unlikely to be zero, but you should still ask whether the size of the effect matters in your real-world context.

With a solid grasp of LINEST, SLOPE, INTERCEPT, TREND, and the diagnostic formulas around them, you can build regression models directly in Excel and understand every number they produce. Pick a dataset you already know well, run a simple model first, then add predictors one at a time and watch how the statistics change. That hands-on loop is the fastest way to turn Excel regression formulas into a dependable part of your analytical toolkit.

AD