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.

Kasper Langmann, Microsoft Office Specialist

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.

Data of X and and Y values

So, to plot data on a scatter plot:

  1. Go to Insert > Charts Group
Selection of the graph from the insert tab
  1. Select the data of columns whose relationship you want to examine.
  2. Click on the scatterplot part under the chart. It will show you a list of options.
  3. Select the first option.
Selection of data and scatterplot

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:

Scatterplot in raw form

Now to draw a trend line of this graph:

  1. Click on the “+” icon on the top left of the graph.
Chart elements for trendline
  1. Click the trendline option. A drop-down menu appears.
  2. Select More Options.
Chart elements for trendline
  1. Select the linear trendline option, to check the linear trend between variables.
Selection for trendline option

Excel displays the trendline on the scatterplot.

Trendline for the data

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:

  1. Scroll down the Task pane.
  2. Check mark the last two options.
Selection of options for the graph

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.

  1. Excel displays regression statistics as follows:

Equation: y= -19.622x + 612.77

R-squared= 0.7456

Regression statistics

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:

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

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

Changing the color of the trendline

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:

  1. Select “Dash type” from the fill & line menu.
  2. Select solid line.
Formatting of the trendline

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. 😁

Kasper Langmann, Microsoft Office Specialist

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

Here is how you can do that:

  1. Click on Chart Elements > Chart Title > Above chart.
Adding chart title
  1. Add your desired Chart title.
  2. Press Enter.
Adding chart title

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.

Kasper Langmann, Microsoft Office Specialist

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:

  1. Click Chart elements > Axis Titles > Primary Vertical.
Adding Axis Title
  1. Now add in the Axis Title.

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

New Vertical Axis Title

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

  1. Click Chart elements > Axis Titles > Primary Horizontal.
Adding Axis Title
  1. Add in the Horizontal Axis Title or the Y variable.

Our title is Avg. Temperature, hence:

New Horizontal Axis Title

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.

Positive trendline

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.

Negative trendline
  • 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.

No trend

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

Kasper Langmann, Microsoft Office Specialist

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.

Slope of the trendline

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.

 R-squared of the trendline

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.

Kasper Langmann, Microsoft Office Specialist

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.