EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Statistical Functions in Excel Linear Regression in Excel
Secondary Sidebar
Excel Functions
  • Statistical Functions in Excel
    • COUNTIF Formula in Excel
    • COUNTIF Multiple Conditions
    • COUNTIFS with Multiple Criteria
    • Statistics in Excel
    • Confidence Interval in Excel
    • Interpolate in Excel
    • Linear Programming in Excel
    • COUNTIFS in Excel
    • Excel Variance
    • Normal Distribution Formula in Excel
    • Count Cells with Text in Excel
    • Excel Formula For Rank
    • Linear Regression in Excel
    • COUNTA Function in Excel
    • MAX Formula in Excel
    • Excel NORMSINV
    • Count Names in Excel
    • FORECAST Formula in Excel
    • NPER in Excel
    • Excel Median Formula
    • Lognormal Distribution in Excel
    • Excel Chi Square Test
    • Count Formula in Excel
    • COUNTIF Examples in Excel
    • Excel P-Value
    • COUNTIF Not Blank in Excel
    • Excel Standard Deviation Formula
    • Excel GROWTH Formula
    • Excel Percentile Formula
    • Excel Frequency Formula
    • Excel Average Formula
    • Excel Correlation Matrix
    • Excel Z Score
    • Excel MAX IF Function
    • Z TEST in Excel
    • Excel Trendline
    • Excel F-Test
    • Excel STDEV Function
    • Excel Frequency Distribution
    • DCOUNT Function in Excel
    • Excel MIN Function
    • Excel Forecast Function
    • FREQUENCY Excel Function
    • COUNTIF with Multiple Criteria
    • Standard Deviation in Excel
    • MAX Excel Function
    • Excel QUARTILE Function
    • Excel T.Test Function
    • Excel PERCENTILE Function
    • MODE Excel Function
    • SLOPE Excel Function
    • Excel Median Function
    • Excel TREND Function
    • Excel Count Function
    • Excel LARGE Function
    • SMALL Excel Function
    • COUNTIF Excel Function
    • Excel AVERAGE Function
    • Excel CORREL Function
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training Certification
  • Online Excel for Marketing Course

Linear Regression in Excel

By Twinkle SethiTwinkle Sethi

Linear Regression in Excel

Linear Regression in Excel (Table of Contents)

  • Introduction to Linear Regression in Excel
  • Methods for Using Linear Regression in Excel

Introduction to Linear Regression in Excel

Linear regression is a statistical technique/method used to study the relationship between two continuous quantitative variables. In this technique, independent variables are used to predict the value of a dependent variable. If there is only one independent variable, then it is a simple linear regression, and if a number of independent variables are more than one, then it is multiple linear regression. Linear Regression models have a relationship between dependent and independent variables by fitting a linear equation to the observed data. Linear refers to the fact that we use a line to fit our data. The dependent variables used in regression analysis are also called the response or predicted variables, and independent variables are also called explanatory variables or predictors.

A linear regression line has an equation of the kind: Y= a + bX;

Where:

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,639 ratings)
  • X is the explanatory variable,
  • Y is the dependent variable,
  • b is the slope of the line,
  • a is the y-intercept (i.e. the value of y when x=0).

The least-squares method is generally used in linear regression that calculates the best fit line for observed data by minimizing the sum of squares of deviation of data points from the line.

Methods for Using Linear Regression in Excel

This example teaches you the methods to perform Linear Regression Analysis in Excel. Let’s look at a few methods.

You can download this Linear Regression Excel Template here – Linear Regression Excel Template

Method #1 – Scatter Chart with a Trendline

Let us say we have a dataset of some individuals with their age, bio-mass index (BMI), and the amount spent by them on medical expenses in a month. Now with an insight into the individuals’ characteristics like age and BMI, we wish to find how these variables affect the medical expenses, and hence use these to carry out regression and estimate/predict the average medical expenses for some specific individuals. Let us first see how only age affects medical expenses. Let us see the dataset:

linear regression in excel example 1-1

Amount on medical expenses= b*age + a

  • Select the two columns of the dataset (x and y), including headers.

linear regression in excel example 1-2

  • Click on ‘Insert’ and expand the dropdown for ‘Scatter Chart’ and select ‘Scatter’ thumbnail (first one)

linear regression in excel example 1-3

  • Now a scatter plot will appear, and we would draw the regression line on this. To do this, right-click on any data point and select ‘Add Trendline.’

linear regression in excel example 1-4

  • Now in the ‘Format Trendline’ pane on the right, select ‘Linear Trendline’ and ‘Display Equation on Chart’.

Linear Regression in Excel example 1-5

  • Select ‘Display Equation on Chart’.

Linear Regression in Excel example 1-6

We can improvise the chart as per our requirements, like adding axes titles, changing the scale, color and line type.

Linear Regression in Excel example 1-8

After Improvising the chart, this is the output we get.

Linear Regression in Excel example 1-7

Note: In this type of regression graph, the dependent variable should always be on the y-axis and independent on the x-axis. If the graph gets plotted in reverse order, then either switch the axes in a chart or swap the columns in the dataset.

Method #2 – Analysis ToolPak Add-In Method

Analysis ToolPak is sometimes not enabled by default, and we need to do it manually. To do so:

Analysis ToolPak Add-In method 2-1

  • Click on the ‘File’ menu.

Analysis ToolPak Add-In method 2-2

After that, click on ‘Options’.

Analysis ToolPak Add-In method 2-3

  • Select ‘Excel Add-Ins’ in the ‘Manage’ box, and click on ‘Go.’

Analysis ToolPak Add-In method 2-4

  • Select ‘Analysis ToolPak’ -> ‘OK’

Analysis ToolPak Add-In method 2-5

This will add ‘Data Analysis’ tools to the ‘Data’ tab. Now we run the regression analysis:

  • Click on ‘Data Analysis’ in the ‘Data’ tab

Analysis ToolPak Add-In method 2-6

  • Select ‘Regression’ -> ‘OK’

Analysis ToolPak Add-In method 2-7

  • A regression dialog box will appear. Select the Input Y range and Input X range (medical expenses and age, respectively). In the case of multiple linear regression, we can select more columns of independent variables (like if we wish to see the impact of BMI as well on medical expenses).
  • Check the ‘Labels’ box to include headers.
  • Choose the desired ‘output’ option.
  • Select the ‘residuals’ checkbox and click ‘OK.

Analysis ToolPak Add-In method 2-8

Now our regression analysis output will be created in a new worksheet, stating the Regression Statistics, ANOVA, residuals and coefficients.

Output Interpretation:

  • Regression Statistics tells how well the regression equation fits the data:

Summary Output

  • Multiple R is the correlation coefficient that measures the strength of a linear relationship between two variables. It lies between -1 and 1, and its absolute value depicts the relationship strength with a large value indicating a stronger relationship, a low value indicating negative and zero value indicating no relationship.
  • R Square is the Coefficient of Determination used as an indicator of goodness of fit. It lies between 0 and 1, with a value close to 1 indicating that the model is a good fit. In this case, 0.57=57% of y-values are explained by the x-values.
  • Adjusted R Square is R Square adjusted for a number of predictors in the case of multiple linear regression.
  • Standard Error depicts the precision of regression analysis.
  • Observations depict the number of model observations.
  • Anova tells the level of variability within the regression model.

ANOVA

This is generally not used for simple linear regression. However, the ‘Significance F values’ indicate how reliable our results are, with a value greater than 0.05 suggesting to choose another predictor.

  • Coefficients are the most important part used to build regression equation.

Coefficients

So, our regression equation would be: y= 16.891 x – 355.32. This is the same as that done by method 1 (scatter chart with a trendline).

Now, if we wish to predict average medical expenses when age is 72:

So y= 16.891 * 72 -355.32 = 860.832

So this way, we can predict values of y for any other values of x.

  • Residuals indicate the difference between actual and predicted values.

Residuals

The last method for regression is not so commonly used and requires statistical functions like slope (), intercept (), correl (), etc., to carry out regression analysis.

Things to Remember About Linear Regression in Excel

  • Regression analysis is generally used to see if there is a statistically significant relationship between two sets of variables.
  • It is used to predict the value of the dependent variable based on the values of one or more independent variables.
  • Whenever we wish to fit a linear regression model to a group of data, then the range of data should be carefully observed. If we use a regression equation to predict any value outside this range (extrapolation), it may lead to wrong results.

Recommended Articles

This is a guide to Linear Regression in Excel. Here we discuss how to do Linear Regression in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. Excel Regression Analysis
  2. Linear Programming in Excel
  3. Linear Interpolation in Excel
  4. Statistics in Excel
Popular Course in this category
Excel Training (23 Courses, 9+ Projects)
  23 Online Courses |  9 Hands-on Projects |  110+ Hours |  Verifiable Certificate of Completion
4.9
Price

View Course

Related Courses

Excel Advanced Training (16 Courses, 23+ Projects)4.9
Excel for Marketing Training (8 Courses, 13+ Projects)4.8
3 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Linear Regression Excel Template

EDUCBA

Download Linear Regression Excel Template

EDUCBA

डाउनलोड Linear Regression Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more