**How to do** **Linear Regression**** in Excel**

**How to do**

**Linear Regression**

**in Excel**

*Written by co-founder Kasper Langmann, Microsoft Office Specialist.*

One of the best approaches in **analyzing quantitive** data in Excel is by running a simple linear regression.

With the latest versions of Excel, it doesn’t take *more than a minute* to make one.

However, this wasn’t always the case. Previously, you have to use Excel’s Analysis ToolPak or a combination of different functions.

In this article, let’s talk about the **basics **of simple linear regression in Excel and then we’ll show you how to make one fast and easy.

**The Simple Linear Regression**

Linear regression analysis, in general, is a **statistical method **that *shows or predicts the relationship between two variables or factors*.

There are 2 types of factors in regression analysis:

**Dependent variable***(y)*: It’s also called the*‘criterion variable’*,*‘response’*, or*‘outcome’*and is the factor being solved.**Independent variable***(x)*: This is otherwise known as*‘explanatory variables’*or*‘predictors’*. They are factors used in solving the dependent variable due to their influence or effect on the said variable.

Usually, this type of analysis is used when one is trying to

find or establish the correlation between variables.

Here’s the **linear regression formula**:

**y = bx + a + ε**

As you can see, the equation shows *how y is related to x*.

On an Excel chart, there’s a **trendline **you can see which *illustrates the regression line *— the **rate of change**.

Here’s a more detailed definition of the formula’s parameters:

**y***(dependent variable)***b***(the slope of the regression line)***x***(independent variable)***a***(y-intercept of the regression line)***ε***(the error term which accounts the variability in y that can’t be explained by the analysis)*

The analysis accounts for an error since they can’t be completely eliminated especially in a predictive analysis such as this.

But don’t be surprised if you can’t find the error term in Excel. The program does it in the background.

Now, let’s proceed into making one in Excel!

**Simple linear regression in Excel**

The first part of making a simple linear regression graph in Excel is making a scatter plot.

For convenience, let’s use the same data set with the scatter plot exercise. Let’s assume you’re visualizing your e-commerce site’s pageviews and sales the previous year.

In summary, here’s what you need to do to *insert a scatter plot *in Excel:

**Format your data**in such a way that the*independent variable is on the left column*and the*dependent variable on the right*.**Highlight your data**.**Find and click the ‘Scatter’ icon**under the**‘Scatter’**group on the**‘Charts’**category on the ribbon.

There’s your scatter plot!

For convenience, **edit the chart (bounds and titles) **so the data points look centered on the chart.

If you’re not sure what to do, feel free to refer to the

‘scatter plot’article.

Aim for this look:

Once you got it, let’s now head over to the main part.

To draw the **regression line**, let’s *add a trendline *on the chart.

**Click on any of the data points** and **right-click**. **Select ‘Add Trendline’**.

After that, a window will open at the right-hand side.

**‘Linear’ **is the default **‘Trendline Options’**. If it’s not selected, **click on it**.

Also, if you like to *show the equation on the chart*, **tick the ‘Display Equation on chart’ box**.

Once you do so, the equation will show on the chart. Feel free to *move it around *so that it doesn’t *block the data points or the line*.

If you like to *edit the look of the trendline*, just **right-click on it **and **select ‘Format Trendline’**.

This will open the same window as earlier. Where you need to go, however, is the **‘Fill & Line’ **tab.

In here, you can *edit the line’s color, transparency, width, dash type, and others*.

All in less than a minute. Fast and easy! 👍

**How to interpret the results**

Primarily, what you’re looking in a simple linear regression is the **correlation between the variables**. Fortunately, in Excel, the trendline does it all for you.

The trendline will tell you if the relationship of your variables is positive or negative.

**Positive:**If the line shows an*upward trend*. This indicates that*as the independent variable increases, the dependent variable also increases*. The same with our example, as the pageviews increase, we can expect to see a rise in sales as well.**Negative:**If the line shows a*downward trend*. This suggests that*as the independent variable increases, the dependent variable decreases*.**None at all:**This is easy to spot. There is*no correlation*between the variables (therefore, no way to predict the next values) when*the points in the scatter plot don’t resemble a line*as they are scattered. You can still see a line if you add a trendline no matter how random the points are, but the line is usually close to a horizontal line.

**Wrapping things up…**

The simple linear regression is a good tool to determine the correlation between two or more variables. Before, you have to mathematically solve it and manually draw a line closest to the data.

It’s a good thing that Excel added this functionality with scatter plots in the 2016 version along with 5 new different charts. Now, you can visually see the correlation between the variables without breaking a sweat.