A linear regression is a data plot that graphs the linear relationship between an independent and a dependent variable. It is typically used to visually show the strength of the relationship, and the dispersion of results – all for the purpose of explaining the behavior of the dependent variable.

Say, as a simple example, we wanted to see test the strength of the relationship between amount of ice cream eaten and obesity. We would take the independent variable, the amount of ice cream, and relate it to the dependent variable, obesity, to see if there was a relationship.  Given a regression is a graphical display of this relationship, the lower the variability in the data, the stronger the relationship, and the tighter the fit to the regression line.   

Important Considerations

There are a few critical assumptions about your data set that must be true in order to proceed with a regression analysis.  

  1. The variables must be truly independent (using a Chi-square test).
  2. The data must not have different error variances (this is called heteroskedasticity).
  3.  The error terms of each variable must be uncorrelated. If not, it means the variables are serially correlated.

If those three things sound complicated, well – they are. But the effect of one of all of those considerations not being true is a biased estimate. Essentially, you'd be misstating the relationship you are trying to measure.  

Outputting A Regression in Excel

The first step in running regression analysis in Excel is to double-check that the free Excel plugin Data Analysis ToolPak is installed. This plugin makes calculating a range of statistics very easy.  It is not required to chart a linear regression line, but it makes creating statistics tables simpler.   

Using the Data Analysis ToolPak, creating a regression output is just a few clicks.  Remember that the independent variable goes in the X range.

Say we want to know, given the S&P 500 returns, if we can estimate the strength and relationship of Visa (V) stock returns.  

Go to the Data Tab --> Data Analysis, and run the result:

[if the table seems small, right-click image and open in new tab for higher resolution]

Interpret the Results

Using that data (which is the same from our R-squared article) we get the following table:


Charting a Regression in Excel

We can chart a regression in Excel by highlighting the data and charting it as a scatter plot.  Apply some formatting, and the visual result sums up the strength of the relationship, albeit at the expense of not providing as much detail as the table above. 

  1. What are some of the more common types of regressions investors can use?

    Learn about the most common types of regressions investors use to model asset prices including linear regressions and multiple ... Read Answer >>
  2. How can I use a regression to see the correlation between prices and interest rates?

    Learn how to use linear regression to calculate the correlation between stock prices and interest rates by taking the square ... Read Answer >>
  3. In what types of economies are regressive taxes common?

    Understand the three main taxation systems, regressive, proportionate and progressive, and learn where regressive tax systems ... Read Answer >>
  4. How is the standard error used in trading?

    Understand how the standard error is used in statistics and what it measures. Learn how the standard error is used in trading ... Read Answer >>
  5. How can you calculate correlation using Excel?

    Find out how to calculate the Pearson correlation coefficient between two data arrays in Microsoft Excel through the CORREL ... Read Answer >>
Related Articles
  1. Investing

    Regression Basics For Business Analysis

    This tool is easy to use and can provide valuable information on financial analysis and forecasting. Find out how.
  2. Trading

    The Linear Regression Of Time and Price

    This investment strategy can help investors be successful by identifying price trends while eliminating human bias.
  3. Investing

    The Restoration of Restoration Hardware Continues

    Restoration Hardware reported strong quarterly results that beat expectations, advancing its turnaround story.
  4. Investing

    What's a Sensitivity Analysis?

    Sensitivity analysis is used in financial modeling to determine how one variable (the target variable) may be affected by changes in another variable (the input variable).
  5. Investing

    The P/E Ratio: A Good Market-Timing Indicator

    Check out the returns this newer technical analysis tool would've yielded over the period from 1920 to 2003.
  6. Investing

    Guide To Excel For Finance

    Formulas, functions and features you need to know when using Excel for financial analysis.
  7. Financial Advisor

    Calculating Beta: Portfolio Math For The Average Investor

    Beta is a useful tool for calculating risk, but the formulas provided online aren't specific to you. Learn how to make your own.
  8. Investing

    Rising Prices: Inflation or Quality Improvements?

    Price indices are used to measure inflation, but qualitative improvements in products complicates attempts to isolate the true cause of rising prices.
  1. Error Term

    A variable in a statistical and/or mathematical model, which ...
  2. Multiple Linear Regression - MLR

    Multiple linear regression (MLR) is a statistical technique that ...
  3. Least Squares Method

    A statistical technique to determine the line of best fit for ...
  4. Line Of Best Fit

    A straight line drawn through the center of a group of data points ...
  5. Residual Sum Of Squares - RSS

    A residual sum of squares is a statistical technique used to ...
  6. Negative Correlation

    In statistics, a perfect negative correlation is a relationship ...
Hot Definitions
  1. Entrepreneur

    An Entrepreneur is an individual who founds and runs a small business and assumes all the risk and reward of the venture. ...
  2. Money Market

    The money market is a segment of the financial market in which financial instruments with high liquidity and very short maturities ...
  3. Perfect Competition

    Pure or perfect competition is a theoretical market structure in which a number of criteria such as perfect information and ...
  4. Compound Interest

    Compound Interest is interest calculated on the initial principal and also on the accumulated interest of previous periods ...
  5. Income Statement

    A financial statement that measures a company's financial performance over a specific accounting period. Financial performance ...
  6. Leverage Ratio

    A leverage ratio is any one of several financial measurements that look at how much capital comes in the form of debt, or ...
Trading Center