# How to do Linear Regression in Excel: Full Guide (2022)

Linear regression is an easy way to present a statistical analysis between two or more variables in Excel.

Previously, running a linear regression in excel involved a lot of functions (and other complexities). But now, only a few steps can lead you to the results.

Apart from running linear regression in Excel, this guide also discusses its types. So, without further ado, let’s dive right in. 😃

Also, if you want to practice along the guide, download our free sample workbook here.

## Linear Regression Equation

What is linear regression?

If we want to evaluate the relationship between two variables, we use linear regression analysis. These two variables are the Dependent and the Independent variables.

The dependent variable, also known as the explained variable, is the main variable here.

The independent variable is also known as the explanatory variable. The change in this variable is used to explain the change in the dependent variable.

So, how do we relate the dependent variable with the independent variable? We do it using the simple linear regression model.

Mathematically, the linear relationship between dependent and independent variables is explained by the following equation:

**Y= a + bx**

Where,

Y = dependent variable or explained variable

a = regression intercept term

b = regression slope coefficient

x = independent or explanatory variable

“a” and “b” are also called regression coefficients. And Excel returns the predicted values of these regression coefficients.

## How to Do Linear Regression Through A Graph

Okay, so we know what linear regression is. Now, let’s see how to depict it through a graph. 😀

First, you need to define your variable; X – we can think of it as an explanatory variable.

Then, the Y variable which is a response to that explanatory variable.

Let’s suppose, Avg. Temperature is our X variable. Whereas, in this example, the sale of sweaters is our Y variable.

To explain the relationship between these variables, we will be using the scatter plot.

The sheet below contains the data for both variables in the last six months of a year.

So, to plot data on a scatter plot:

- Go to Insert > Charts Group

- Select the data of columns whose relationship you want to examine.
- Click on the scatterplot part under the chart. It will show you a list of options.
- Select the first option.

**Pro Tip!**

You can also enter x and y values by using the regression dialog box in Excel.

Excel displays the scatterplot for our variables like:

Now to draw a trend line of this graph:

- Click on the “+” icon on the top left of the graph.

- Click the trendline option. A drop-down menu appears.
- Select More Options.

- Select the linear trendline option, to check the linear trend between variables.

Excel displays the trendline on the scatterplot.

The above image shows a downward regression line which represents a negative trend. But why is that?

We will explain it later in the article, but till then try to guess the reason. Who knows you might get it right. 😀

To show the equation and R-squared on the graph:

- Scroll down the Task pane.
- Check mark the last two options.

One is for the equation of this linear relationship while the other is an R- squared value of the equation. These are the outputs we will be discussing further in the article.

- Excel displays regression statistics as follows:

Equation:** y= -19.622x + 612.77**

**R-squared= 0.7456**

**Pro Tip!**

How to interpret this trend? It’s simple – a sign with a slope in the equation is all you need to check.

A positive sign means a positive relationship. A negative sign means a negative relationship between variables. Since we have “-19.622x”, our trend is negative here.

Okay, so what if you want to format your trendline? Like always, Excel comes to our rescue. 😃

To format the trendline:

- Go to Format
- Select “Fill & Line”.
- To change the color, select the Color tab.

We select the color red for our trendline. You can see how the color changed to red from blue in our images.

Let us explore one more option for trendline formatting – the style of the trendline.

Say, we want to change our dotted trendline to a solid one. To do so:

- Select “Dash type” from the fill & line menu.
- Select solid line.

This will change the presentation of the trendline from a dotted line to a perfectly solid line.

All these formatting options only change the presentation of the trendline. It has nothing to do with the relationship of the trend. It will remain the same whether it’s positive, negative, or has no trend.

Now don’t get curious already, explanation of these trends is not very far away. 😁

You can also format the graph and axis titles on the graph for better visuals.

Here is how you can do that:

- Click on Chart Elements > Chart Title > Above chart.

- Add your desired Chart title.
- Press Enter.

The chart title represents the core story and meaning of the graph. Since our chart represents a comparison of relations, hence, we chose Scatterplot of Sale of sweaters v/s Avg. Temperature is our title.

Okay, so we’re done with the Chart title. Let’s now add in the Axis titles too.

Let’s start with the left vertical title:

- Click Chart elements > Axis Titles >
**Primary Vertical**.

- Now add in the Axis Title.

In our case, the Axis Title for our graph is Sale of Sweaters.

You can also add the horizontal Axis Title. The process is similar to the one above.

- Click Chart elements > Axis Titles >
**Primary Horizontal**.

- Add in the Horizontal Axis Title or the Y variable.

Our title is Avg. Temperature, hence:

And that’s it. We’ve successfully plotted linear regression on the chart. 🥳

## How to Analyze the Linear Regression Graph

Okay, so we know how to plot a graph on the scatterplot.

Now, let’s analyze it.

### Shapes of the Linear Trendline

The linear trendline of excel helps us in checking the trend of the variable and their movement. These are listed down for you as promised earlier. 😄

The linear trendline can take the following three shapes:

**Positive Trendline**

The trendline becomes positive when the scatterplot in Excel shows an “upward trend”. Here an increase in the independent variable causes the dependent variable to increase.

The following image is a good depiction of this type of trend.

**Pro Tip!**

What if both the variables increase together? It’s simple – that’s also a positive trend.

**Negative Trendline**

If the scatterplot in Excel shows a downward trendline, what does it mean? It means an increase in the independent variable causes a decrease in the independent variable.

**No Trend**

The two variables can also be independent of each other. In this case, movement in both variables is random with no relation to each other. Then their trendline can take this shape below.

The shape of this trendline graph is not necessarily horizontal, but very close to that.

### The Slope of The Graph

What if we want to know the percentage of change in Y caused by a change in X? Slope of the graph is an answer to this.

Let’s recall the linear regression equation again, i.e., **Y = a + bx**

Here the term “b” is the slope. In our example, Excel presents this regression analysis output for us as:

**Y= -19.622x + 612.77.**

Here -19.622 is our slope. It means a 1% change in the x variable causes a **-19.622%** change in the y variable.

**Pro Tip!**

Easy way to remember the slope is to remember **Rise over Run**. Rise means vertical axis. Run means horizontal axis. So the slope is just a change in y for a change in x.

### R-Squared

Another important output of our scatterplot is R-squared. It tells us how much variation in the independent variable is explained by the dependent variables in our linear regression model.

The R-squared of this trendline is 0.7456. It tells you that 74.56% of y variable variation is explained by the x variable.

There’s a simple rule for this, the higher the better. Another statistic relevant to this is the p value. It is totally opposite to R-squared. The smaller the better.

## That’s it – Now what?

This is a complete guide on how to make a trendline in Excel. It also explains how to interpret the trendline.

These steps are very useful if you want to estimate a change in any variable caused by the other variable.

The guide above also explains how to draw a trendline using a scatterplot in Excel. It further tells how to format the trendline in simple steps – along with formatting shapes and outputs of the trendline.

Linear regression analysis in Excel is a smart tool. But so are the other functions in Excel. Like the VLOOKUP, SUMIF, and IF functions of Excel.

Want to learn them already? Enroll in my 30-minute free email course that teaches you these and many more functions of Excel.

## Other resources

Linear regression can be challenging to understand, but once you get a hold of it, you can master it in no time.

In addition to linear regression, Excel offers other forecasting functions too – like data analysis tools in Excel and How to Use the Excel FORECAST function.