Excel LINEST(): The Linear Regression Function in Excel

Excel has a great tool for linear regression called LINEST(). LINEST() is great because it calculates the statistics for a straight line that best fits your data using the least squares method. I also like LINEST() because it gives you additional model info, like the F-statistic and standard errors.

Keep reading and I'll show you all about LINEST() so you can use it for your trend analysis, forecasting, and more.

What Excel LINEST() Is Doing

Before we try examples, let me give a higher-level view of what LINEST() is doing. 

LINEST() performs linear regression using the least squares method, which finds the line that minimizes the sum of squared residuals, which are the squared differences between the observed y-values and those predicted by the line. Under the hood, Excel's LINEST() uses matrix algebra to solve what is known as the normal equation that arises in ordinary least squares (OLS) regression.

Regression is one of the absolute most important things an analyst or data scientist needs to be skilled with. Regression is used for everything from comparing models and forecasting, to hypothesis testing and causal inference. 

Syntax for Excel LINEST()

The syntax for LINEST() is as follows:

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

• known_y's: The dependent data values (required)

• known_x's: The independent data values (optional)

• const: Logical value; TRUE to calculate the intercept, FALSE to force it to zero (optional)

• stats: Logical value; TRUE to return additional regression statistics, FALSE for only slope/intercept (optional)

Using Excel LINEST() for Regression

Let’s now look at both the simple linear regression case (one independent variable) and the multiple linear regression case (more than one independent variable). 

LINEST() and simple linear regression

To perform a simple linear regression with one independent variable, you can use LINEST() with just your y-values and x-values. This best-fit line for simple linear regression is defined by exactly two parameters: its slope and intercept.

=LINEST(B2:B10, A2:A10, TRUE, TRUE)

By setting the optional arguments to TRUE, LINEST() returns several statistics, as you can see in the image below:

• Slope(s) for each independent variable
• Intercept
• Standard error values
• R-squared value
• F-statistic and degrees of freedom

By default, because LINEST() is an array formula, it will output a 2x5 grid (when doing simple linear regression with the stats argument as TRUE).

Personally, I like to wrap LINEST() inside of TOCOL() so I get the output as one column. 

Just make sure that you keep the columns in the right order!

We have a great Introduction to Statistics course if you want additional help interpreting output like this. 

LINEST() and multiple linear regression

LINEST() can also handle multiple independent variables for multiple regression analysis. Simply provide a range for known_x's that includes all your variables.

=LINEST(C2:C10, A2:B10, TRUE, TRUE)

This example calculates regression statistics for y-values in C2:C10 with two independent variables in columns A and B. A helpful tip is to keep a second grid so you can match up the statistics accurately.

For a video walk-through of how to use LINEST() and other functions, tune in to our Regression in Excel Made Easy YouTube video.