Add a Linear Regression Trendline to an Excel Scatter Plot

You’re either reading this because you searched for how to add a linear regression trendline to anExcel scatter plotor you saw the title and thought, “Are these words even English?!” We’ll help you with both.

What Is Linear Regression?

If you know what a linear regression trendline is, skip ahead. Ok, now that the nerds are gone we’ll explain linear regression.Linearmeans in a line. You knew that.Regression, in math, means figuring out how much one thing depends on another thing. We’ll call these two thingsXandY.

Let’s use the example of tracking the value of a single share in the stock market over the years. X will be time in years and Y will be the value in dollars.

We know that the value of a stock is changed by time passing, among other things. We can’t control those other things, but we can control when we sell the stock, so we control the time variable. But how dependent is the value of a stock on time passed?

If we bought a stock for $1 and in one year its value went up to $100, does that mean every year the value will go up another $100? Does that mean in 25 years it will be valued at $2500? We don’t know.

We figure it out by looking at how much the stock earned over several years. That’s fairlysimplebecause we’re only measuring how much we change one thing or one variable. Then we put those measurements on a graph orplot. The dots could be all over the place orscattered.

Could we draw a line through the dots that would show a trend? Let’s call that atrendline. Yes, we can certainly try. That line is asimple linear regression trendline through a scatter plot. Now we know those words are actually English and what they mean. Let’s create one in Excel.

How To Create An Excel Scatter Plot With Linear Regression Trendline

Let’s assume you haven’tlearned all about Excelyet. The first step is to create a scatter plot. Then we can create the trendline. Then we can do some neat things with the trendline and see what it means.

What Does The Equation and R-squared Value Mean?

These are handy to have. The R-squared value tells you just how good the trendline fit is. Although an R-squared value above 0.8 is ideal, 0.69 isn’t bad though. Think of it as you being 69% confident that this line will give you a good insight into how this stock tends to perform.

The equation makes it easier for you to do quick calculations to figure out what the stock value on the trendline is at any point in time. Even before the line begins and after it ends. What might the stock be worth in 2030? Let’s plug it into the equation.

Y = 80.468 x X – 160136– whereXis2030

Y = 80.468 x 2030 – 160136

Y = 163,350.04 – 160136

Y = 3,214.04

Yes, if the trend holds, the value of the stock has a decent chance of being worth $3214.04 in 2030.

What Will You Do With A Linear Regression Trendline?

We’ve shown you an example of how a linear regression trendline in Excel might help you make a financial decision. But what other ways could you use it? Are you coming up with ideas? Let us know.

Guy has been published online and in print newspapers, nominated for writing awards, and cited in scholarly papers due to his ability to speak tech to anyone, but still prefers analog watches.Read Guy’s Full Bio

Welcome to Help Desk Geek- a blog full of tech tips from trusted tech experts. We have thousands of articles and guides to help you troubleshoot any issue. Our articles have been read over 150 million times since we launched in 2008.

HomeAbout UsEditorial StandardsContact UsTerms of Use

Copyright © 2008-2024 Help Desk Geek.com, LLC All Rights Reserved