Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Excel has quite a few different analytical tools, and one that's associated specifically with charts is what we call a trendline. Now you don't have to be a statistical expert to understand regression analysis, or anything like that, although we will get into that territory a bit. If you're trying to analyze the existing data and possibly even trying to make predictions about where the sales might head in the future, right-click on the line chart entry--and you can also do this with column charts as well--and choose Add Trendline, and immediately you'll see a line there. It's called a linear regression line.
Now you probably at this point do need to know a little bit about regression analysis, and we are not going to be talking about that in any depth here. But if you're familiar with the idea of regression, you might choose Exponential. Usually that's a curved line. Associated with this is also an equation, and if you're familiar with why you have an equation, you might want to display that on the char. We see this option down here, Display Equation on Chart. Let me move the dialog box over. There is the equation. Sometimes the R-squared, which is a rough measure of how accurate this might be, and again long explanations as to how that really works, but for those who are familiar with these concepts, the ability to create these quickly and easily has some real merit to it.
I'm just going to choose Linear here. And let me just suggest--and without displaying the other characteristics here at the bottom I'll uncheck those-- you can also, in addition to creating this line, extend it into the future. So I'm going to forward this, using the forward box here, six periods forward, and close. And again if I have the right statistical background and am familiar enough to suggest what has happened here, I can also make a prediction as to where these sales figures might be headed in future months.
Now, one feature here that doesn't require a lot of statistical analysis is a different kind of trendline. So I'll again right-click here. I could keep the current trendline or possibly replace it with another. So maybe we'll right-click on the line here, format the trendline, and along the way here change it to Moving Average. Immediately we see a different line here, and you'll see the number 2, so what actually has happened here in the example? A new line is being created. It starts at the second point. And each point here that we see on this new line is the average of the current entry and the previous one.
So, for example, the one right here for Jun-09, this point right here is the average of the point above and the point for May-09 just to the left here. And the idea behind these is that there are some erratic occurrences in the data here. It bounces up and down a lot. So this line here is more of an average, not exactly an average of everything, but an average of what's happening month to month. You see these sometimes with stock quotes. So I am going to change this to be a three, and each time we do this, as we change this box here, we get a smoother line, and a smoother line, and so on.
So that's going to make some sense, rather than creating a function or a long formula to do the calculation, you don't have to do this. Now those of you who are adept with Excel formulas know that's not exactly difficult. You could easily create the data for this. But the trendline does it much faster, and so creating a Moving Average could be a more popular use of this feature. So this is a feature that has some sophistication to it. On the other hand, a Moving Average a lot more common for a lot of people, easy to get to, different ways to create a trendline in working in Excel. And I did say earlier, it doesn't have to be a line chart. We could easily change this to a column chart here, and it might be appropriate as well, and I will do that right now.
On the Design tab, Change Chart Type, I'll make it a clustered column here to see the data that way. In fact, in this case maybe it sets off the line more readily because we are not looking at two lines, we are looking at a line and a series of columns. So, different uses of the trendline capability with Excel charts.
Get unlimited access to all courses for just $25/month.Become a member
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.