Hi, this is Wayne again with a topic “Advanced Excel: Using Charts and Functions to See Trends”.
In this Excel tutorial, I’m gon na show you some techniques and tools that we can use in Excel to identify trends to look at how things are trending, both in the recent past, as well as looking toward the future. So, let’s get started as you can see: I’m in a spreadsheet called games sales and this spreadsheet tracks the total number of items sold each month for a small business that sells board games and card games. It also tracks the earnings that came from those sales, and you can see each month is listed separately here. So the first thing I would like to do is add a chart here at the right and I’d like this chart to show the trend.
How are the earnings trending for this company? In a previous video, I showed some quick and simple techniques for adding charts to excel and if you haven’t watched that video already please do so, but in this tutorial I want one specific kind of chart this kind. Here. That’S a line chart or area chart and the reason why is this kind of chart is particularly good for showing changes over time or trends. But before I add that it’s helpful to first select your data that you would like charted. So I’m going to click and drag on earnings all the way down to the bottom of the data that I would like charted. Next, I’m gon na hold the ctrl key on the keyboard, and now I’m going to move my mouse over here to column a and still holding the ctrl key, I’m going to click and drag from the word month. All the way down to the bottom of the data. Now I can release the ctrl key now. I can simply go up here to the charts group and select line chart, there’s a bunch of different ones I could choose, but I’m gon na stick with this particular kind here line chart.
I click it and it shows me a nice chart of the earnings for this small business, I’m just going to move that chart over to the right a little bit and let’s take a look at it. So this gives me some good information about the trends. At least the trends as of 2018, you can see that the earnings are building somewhat over time, but there are these dips as well, so to help us get a sense of where things are headed. Something we can do is click on the line.
When you do, you can see that it highlights the line and it also highlights the different data points. Each of these points is an earnings amount from the data here at the left. Now I can carefully right-click on that line and choose add trendline. Look what happens when you do that.
First of all, you get a panel that opens on the right and if I scroll horizontally a little bit you’ll be able to see this better. You can see that a trendline has been added to my chart, so this helps me get a sense of how things are trending, even though there are ups and downs, it looks like the trend is gradually up now. Let’S look at the panel that opened on the right side. You can see that there are different options here, for example, there’s exponential instead of linear trendline, you could do an exponential trend line and, to be honest, I don’t totally understand the difference between the two other than exponential is more curved and linear is straight.
There’S also logarithmic polynomial, and I think these are all basically statistical terms. So if you’ve taken statistics, you probably understand this part much better than I do and I think I’m gon na stick with linear with linear selected. If I browse down the panel a little ways, look at what I can do, there’s a forecast option and I can forecast forward. Let’S say: 5 periods.
I tap enter on the keyboard and you can see what that did. It adjusted my chart to include a forecast of 5 more months. I could change that to, let’s say 20 periods. Let’S look at that. So now you can see that trendline and how it’s dramatically going up if we change it to exponential.
It looks similar, but there are some differences now as you’re doing this, as you are looking at trends and future forecasts notice that there are some other options down here, there’s a set intercept if you understand what that means go for it, there’s a display equation on Chart and there’s a display, r-squared value on chart, and my understanding is that this, our squared number, has to do with the accuracy of the predictions notice that there are some trendline options. For example, you can add some effects if you want to put in a shadow. I just put in kind of a peach colored shadow you can put in a glow. I mean we can really make this pretty fancy. If we want to I’m actually going to take that off – and there are also some color options, I can change it from blue to red to black whatever color. I want that line to be at this point, I’m gon na undo.
All of this work that I’ve just done and go back to before I put the trendline in I’m just holding ctrl and tapping Z to undo next I’d like to show you how you can add that same trend line, but do it directly in the spreadsheet using Excel functions now, why would you ever want to do it this way? Well, for one thing: it enables you to actually track more than one type of trendline and you’ll see what I mean in just a minute, and there are other reasons as well why you might want to do it this way, so I’m going to scoot this chart To the right, just a little bit and I’m gon na come over here next to earnings and I’m gon na type here trend forecast here at the top and next to that growth forecast. So I just tap enter and it looks like the text did not fit. So I’ll just click and drag to highlight both of those columns and then just double click between them and it should expand them out so that the text fits perfectly.
The next step is kind of tricky. Now, before I do this next step, we need to think about this. What I’m about to do is create a formula that looks at the trend of this data and, if you think about it, a trend cannot rely just on one cell, one piece of data that doesn’t make sense. How can you have a trend based on one thing, and so for that reason I can’t just select cell d3 in this case and type in my formula. Instead, I have to click and drag to highlight the entire range to the right of my data. Next, I’m going to type equals, so this range B basically equals I’ll type, the word trend and then left parentheses, and then I’m going to select the data that I want the trend to be based upon.
So I click and drag to select this entire range. Next, I should put in the right parentheses, but I don’t have to so I’m not going to now think about it. Typically, when you put a formula into a cell when you’re done you just tap enter, but in this case you can’t the reason. Why is because this data is dealing with an array it’s dealing with more than just one cell or one column, it’s a whole array, so, instead of tapping enter, what you need to do is hold ctrl, hold shift and then tap enter and look what it did.
It created some trend data based on these numbers here at the left. This is the trend now, for some reason it put this particular cell in bold. I don’t know why I’m going to take that off.
So now, if we go back and look at our chart, nothing’s changed so far. So now I’m gon na redo this chart, I’m just gon na delete the chart out and once again, I’m going to click and drag, and this time I’m going to click and drag to highlight both earnings and trend forecast columns. Then I’ll hold the ctrl key and select month now I go up and select that same line, chart right here and take a look at that. The chart now incorporates both my data of the earnings and also the trend line that was calculated using the trend function. So that’s kind of nice and I really like the different colors that come in.
Of course you can change those colors just by selecting a line you can go in and change the color that it has now. The last thing I’d like to show you here is: you can do a similar thing if you want to show an exponential forecast and again, I don’t totally understand the differences between the two. So, in this case, just like before you would click and drag to highlight the whole range type, equals growth, left, parentheses and then select the data that it’s gon na be based on. I want to tap enter on the keyboard, but that’s not right. I have to hold ctrl shift and then enter there’s my data and now, if I create that same chart again this time clicking and dragging to highlight all the data I want included and then hold control, because I don’t necessarily need the items sold I’ll just go Here to month and I’ll click and drag to get month go to that same chart and click.
So now you can see three separate lines: each one marked differently earnings in blue trend forecast in orange and the growth forecast in gray. Now, if you remember, when I right clicked to add a trendline, it gave me the option of looking ahead looking forward five months ten months twenty months. So what if I wanted to do that with this other method that I just showed you of using an excel function, to do that we’re gon na have to first change this data, so this data here, if you look carefully, it’s actually based on formulas, we need To change it to values only and one way to do that is to click and drag, to highlight the whole range and then put your mouse at the very bottom edge of that range and right click and just drag down a little bit or left or right. Doesn’T matter and then right back on top of itself and the way Excel interprets? That is that we want to copy this data back on top of itself. However, this time we’re gon na copy as values only so I select that so now, if I click here, you can see, this is not based on a formula. It’S just numbers: let’s do that also with the growth forecast, so I’ll click and drag highlight the whole thing put the mouse at the very bottom edge right click drag down or right wherever I want to go and then right back on top of itself copy here. As values only so now, I can click and drag to get that whole range, and I can go to the lower right corner of the range you can see.
The autofill handle is there if you’ve watched my previous tutorials, you know all about the autofill handle, but you put your mouse right on that and click and drag and I’ll extend this out. Maybe five or six more months, maybe seven – I let go, and it’s now making some forecasts based on the trend. Let’S do the same here with the growth or exponential data, lower right, corner click and drag down, and there we go, and now I can also do a similar thing with the months to extend that out and now, if I click on my chart, you can see What I need to do here, I need to extend this range down to include all of this new data in my chart and there you have it now we have a chart that is based purely on the data in this spreadsheet. Yes, I did use a couple of functions, trend and growth, but this chart is dependent on the numbers here at the left. So we’ve looked at two different ways to create a chart that tracks recent trends and forecasts for the future. We looked at the much easier method of simply right-clicking on the chart and adding a trendline, and then we also looked at how to create that yourself using the trend function and the growth function thanks for watching.
I hope you found this tutorial to be helpful. If you did, please click the like button below and consider connecting with me on my social media accounts like Facebook, Pinterest and Twitter, and definitely do subscribe to my youtube channel for more videos about technology for teachers and students and when you do subscribe. Please click the bell next to the subscribe button. If you’ll do that, you’ll be notified whenever I post another video and watch for another video from me at least every Monday, if you’d like to support my youtube channel, consider becoming a patron of mine through my patreon account and you’ll find links to that in the Description below .