Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
After you've collected a set of data, such as the number of web site visitors per month, you can make projections about how that number will change if the current trend were to continue. The math to calculate those future values isn't that hard to do, but it's really tedious. Rather than do it yourself, you can have Excel do it for you and draw a trendline in your chart to illustrate its result. So here I have a chart with some sales data, which shows values for the years 2005 through 2010. If I wanted to see, based on this data, what 2011 and beyond might look like, then I can create a trendline.
To do that, I click the chart, and then on the Chart Layout contextual tab, I can click the Trendline button, and then I select the trendline that I want to use. In this case, I want to create a linear trendline, and that will show the line that best fits the data that I have in the past. So I will click Linear Trendline, and I'll see that if everything had progressed smoothly, this is the line, the trend that my sales would have taken.
So that's how you create a trendline. But how do you push it into the future? Here is how you do that. First, I'm going to get rid of the trendline by clicking the Trendline button and then clicking No Trendline, and now I will click the Trendline button again, and click Trendline Options. The first thing I need to do is select my trend or regression type. Pay no attention to the big words. All you need to remember is that unless you know definitely that you need to use something other than linear, you should always use Linear. Logarithmic, Polynomial, Power, Exponential, and Moving average are all for other business or scientific applications.
If all you want to do is look a little ways into the future, you use Linear Regression. Next thing I can do is change my Options. This is where I tell Excel how far I want to look ahead. When you look back at the chart - and I won't exit the Format Trendline dialog - you see that we have years: 2005, 2006, 2007, 2008, and so on. A year in this context is called a period. That's because the data I collected is based on years. If I want to forecast ahead, say one or two years, then I would go ahead that many periods.
In this case, I want to look ahead by two years, so I'll use the spin control to half-year, one-year, 1.5, and 2. And you can see the line has already been added to the chart, but I'll click OK to get the Format Trendline dialog out of the way so you can see it more clearly, and I will also make the chart a little bit larger so the line is more spread out. So what we have here is a trendline that, if current sales were to continue, then in 2011, which is two periods into the future, sales would be about here, which is roughly $525,000 per year.
And taking a look at the data that we have here, and the way it's grown, although it fell back a bit in 2009, that doesn't seem to be an unreasonable estimate. So this is how you create a trendline for a line chart. You can also create a trendline for an XY graph, and let me show you that on the XY worksheet. So I'll click the XY worksheet tab here, and you see that I have my rainfall versus customer data that I used in a previous movie. It seems intuitively obvious, just looking at the data, that the more rain there is, the fewer customers I will have.
But if I put in a trendline, I should be able to see how close the correlation really is. To create the Trendline, I click the chart, and then on the Chart Layout contextual tab, I click Trendline and then click Linear Trendline. And I will expand the chart so the line isn't quite as cramped. And you can see that there is a very definite downward slope, high at the left, down at the right, which indicates that the more rainfall there is, the fewer customers will come into my store. Predicting trends is never easy and is only occasionally accurate.
Even so, adding a trendline to a chart can provide a basis for evaluating what your company's future might hold if present trends did continue.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 70074 Viewers
80 Video lessons · 127448 Viewers
52 Video lessons · 62386 Viewers
59 Video lessons · 48090 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.