How to Run Regression In Excel GA S REGULAR Menu Lifewire Tech for Humans Newsletter! Search Close GO Software & Apps > MS Office 39 39 people found this article helpful

How to Run Regression In Excel

Discover interesting correlations in data with regression analysis

By Ryan Dube Ryan Dube Writer University of Maine Ryan Dube is a freelance contributor to Lifewire and former Managing Editor of MakeUseOf, senior IT Analyst, and an automation engineer. lifewire's editorial guidelines Updated on June 29, 2020 Tweet Share Email Tweet Share Email

In This Article

Expand Jump to a Section Meaning of Regression Performing Linear Regression Enable the Analysis ToolPak Step-by-Step: Perform a Simple Linear Regression Multiple Linear Regression Regression in Excel is a way to automate the statistical process of comparing several sets of information to see how changes in independent variables affect changes in dependent variables. If you've ever wanted to find a correlation between two things, using regression analysis in Excel is one of the best ways to do that. Instructions in this article apply to Excel 2019, Excel 2016, Excel 2013, Excel 2010.

What' s the Meaning of Regression

Regression is a statistical modeling approach that analysts use to determine relationships between multiple variables. Regression analysis starts with a single variable you're trying to analyze and independent variables you're testing to see if they affect that single variable. The analysis looks at changes in the independent variables and attempts to correlate those changes with resulting changes in the single (dependent) variable. This may sound like advanced statistics, but Excel makes this complex analysis available to anyone.

Performing Linear Regression in Excel

The simplest form of regression analysis is linear regression. Simple linear regression looks at the relationship between only two variables. For example, the following spreadsheet shows data containing the number of calories a person ate each day and their weight on that day. Since this spreadsheet contains two columns of data, and one variable could potentially have an affect on the other, you can run a regression analysis on this data using Excel.

Enabling Analysis ToolPak Add-On

Before you can use Excel's regression analysis feature, you need to enable the Analysis ToolPak add-on in the Excel Options screen. In Excel, select the File menu and choose Options. Select Add-ins in the left navigation menu. Then, make sure Excel Add-ins is selected in the Manage field. Finally, select the Go button. In the Add-ins pop-up window. Enable Analysis ToolPack by clicking the box in front of it to add a check mark and select OK. Now that Analysis ToolPak is enabled, you're ready to start doing regression analysis in Excel.

How to Perform Simple Linear Regression in Excel

Using the weight and calories spreadsheet as an example, you can perform a linear regression analysis in Excel as follows. Select the Data menu. Then, in the Analysis group, select Data Analysis. In the Data Analysis window, select Regression from the list and click OK. The Input Y Range is the range of cells that contains the dependent variable. In this example, that's the weight. The Input X Range is the range of cells that contains the independent variable. In this example, that's the calorie column. Select Labels for the header cells, and then select New Worksheet to send the results to a new worksheet. Select OK to have Excel run the analysis and send the results into a new sheet. Examine the new worksheet. The analysis output has a number of values that you need to understand to interpret the results. Each of these numbers has the following meanings: Multiple R: The Correlation Coefficient. 1 indicates a strong correlation between the two variables, while -1 means there's a strong negative relationship. 0 means there's no correlation.R Square: The Coefficient of Determination, which shows how many points between the two variables fall on the regression line. Statistically, this is the sum of the squared deviations from the mean.Adjusted R Square: A statistical value called R square that's adjusted for the number of independent variables you've chosen.Standard Error: How precise the regression analysis results are. If this error is small then your regression results are more accurate.Observations: The number of observations in your regression model. The remaining values in the regression output give you details about smaller components in the regression analysis. df: Statistical value known as degrees of freedom related to the sources of variance. SS: Sum of squares. The ratio of the residual sum of squares versus the total SS should be smaller if most of your data fits the regression line. MS: Mean square of the regression data. F: The F statistic (F-test) for null hypothesis. This provides the significance of the regression model. Significance F: Statistical value known as P-value of F. Unless you understand statistics and calculating regression models, the values at the bottom of the summary won't have a lot of meaning. However the Multiple R and R Square are the two most important. As you can see, in this example, calories have a strong correlation to total weight.

Multiple Linear Regression Analysis in Excel

To perform the same linear regression but with multiple independent variables, select the entire range (multiple columns and rows) for the Input X Range. When selecting multiple independent variables, it's less likely you'll find as strong a correlation because there are so many variables. However a regression analysis in Excel can help you find correlations with one or more of those variables that you may not realize exists just by reviewing the data manually. Was this page helpful? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Subscribe Tell us why! Other Not enough details Hard to understand Submit More from Lifewire How to Create a Histogram in Excel for Windows or Mac How to Combine the ROUND and SUM Functions in Excel Regression Definition and How It's Used in Data Mining How to Create a Report in Excel How to Calculate Variance in Excel How to Find Variance in Excel How to Use the COUNTIF Function in Excel How to Round Numbers Down in Excel With the ROUNDDOWN Function How to Use the Round Function in Excel Round up Numbers in Excel With the ROUNDUP Function How to Calculate Weighted Averages in Excel With SUMPRODUCT How to Use a Dynamic Range in Excel With COUNTIF and INDIRECT How to Count Data in Selected Cells With Excel's COUNTIF Function How to Use the Excel TRUNC Function Excel SUMIFS: Sum Only Values Meeting Multiple Criteria How to Do a T Test in Excel Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. Cookies Settings Accept All Cookies