Transcripts for the above video clip:

Excel 2007 Graphs Trendlines

One of the points of using graphs, is to try and identify some sort of a trend in the data.  With that in mind Excel has given you the ability to analyse some of the graphs.  So, for example, we have got a graph here  with months of the year and product sales. You can see in this graph there looks like there is some sort of a trend. If you want to check if the trend exists and what it looks like, you can go under Layout – you’ll see there is an Analysis Tool – there is a whole section on Trendlines. My preferred method, is choose the series you want to work with, right click on it,  and you’ll see over here it says Add Trendline. When you click on it you get the following – you’ll see when we get the trendline, you will be able to format the colour, style and shadow, but the interesting one here is the Trendline options.  By default, you will see it is set as Linear, and you’ll see on our graph we have a line which goes through the data  and tries to explain the best fit for all these data items. You can, however, choose a whole range of trendlines  -  there is exponential, the linear we have discussed, the log, polynomial, power and a Moving Average. Depending on what you choose, you have a couple of other options. The most typical, in our experience, is linear and Moving Average.

Looking at some of the other options, now, you’ll notice that once you’ve chosen the Trend or Regression type, you can now decide, for example, on the Trendline name- at the moment it is called Linear – Product  A – but if you want you can customize it – to whatever name you want it to be.

You also have the ability to forecast – so, based on this line, we can say, please forecast it, say, six periods further. What it does, is it adjusts the chart  so that the trendline continues – for the next six months in this case.  You can go backwards as well.

Another thing you can do, is you can Display the Equation that creates this line, on the actual chart.  I click on it, you’ll see the equation appears, that explains that line.  You can also display the R-squared  value, in this case 0.88. And as a rule of thumb, the closer it gets to 1, the better  the fit, and the closer it gets to zero, the worse the fit.

The last item we haven’t looked at yet is the Set Intercept. If I click on that I can say  Set the Intercept and by default it goes to zero. What it means is that line starts at zero and this is the fit that will get us to this line.  Generally speaking, though, you should let Excel decide where the intercept should occur.

Let’s quickly just remove these  and you’ll see we have got the line – I am just going to format it quickly, so that the line colour matches with the colour of the data  - so you’ll see blue bars and the blue line correspond.  I am now going to add Product B in  and you’ll see now we have got the red lines and I am going to do the same thing with the red lines – right click on it, say Add a Trendline, maybe it is linear as well and change the line colour so it is red as well. When I say Close- you have now got a graph with two products on it  and the trendlines for both of these products. You can see that even though the red one seems to be high, you can see a bit of a downward trend or a narrowing off  of the growth. Whereas the blue line, Product A,  seems to be growing at a little bit faster rate.