How to calculate a regression line

Office Calculate linear regression in Excel - this is how it works


Linear regression is a data plot that graphically depicts the linear relationship between an independent and a dependent variable. It is typically used to visually represent the strength of the relationship and the dispersion of the results - all for the purpose of explaining the behavior of the dependent variable. Sounds complicated, but with Microsoft Excel you can easily calculate and display linear regression.

How to do linear regression on a graph

Follow our step-by-step instructions or take a look at the quick start guide.

How to perform linear regression using a formula

Follow our step-by-step instructions or take a look at the quick start guide.

Quick guide: diagram

  1. Select the desired value columns in Excel and then use the tab "Insert" a Dot (XY) diagram a.
  2. If necessary, adjust the axes by using a Double click do it and then under "Limits" the wished minimum and maximum specify.
  3. Then click on the top right of the diagram green plus and then check "Trend line". Open the menu via the arrow next to "Trendline" and select the option "Linear". Then click in the same menu on"More options...".
  4. Now check the box next to "Show formula in diagram" and "Show the coefficient of determination in the diagram".
  5. Now move the formula and the coefficient of determination to a desired position on your regression diagram.

Quick guide: Formula

  1. First mark two cells side by side.
  2. Enter the following formula above: "= LINEST (values_y's; values_x: constants)"
  3. Replace_y_values ​​and_x_values ​​with your value ranges.
  4. Enter as a constant "TRUE" a.
  5. Confirm the matrix formula with [Ctrl] + [Shift] + [Enter].
  6. Excel gives you the values ​​for m and b in the adjacent cells.