Adding trend lines to Excel 2007 charts
Welcome to my latest Excel 2007 hub which is related to the creation and use of trend lines in Excel 2007 charts or graphs. You can use a trend line to forecast what will happen in the future based on the data that you have in your chart already. The more data you have, the more accurate your trend line will be and the more certain you can be that the future data displayed will be accurately extrapolated by Excel. This hub came about as I have two months of data showing the daily traffic to my hubs from Google Analytics and I was interested in finding out the answers to two questions.
1. Is the daily traffic increasing over time
2. Given the trend, how many hits will my hubs receive daily in two months time
A trend line is added to an existing chart, I cover the creation and configuration of both Pivot Charts and static charts in detail in hubs that can be found here:
I will look at creating a trend line using a linear trend line in this hub. Excel gives you a number of options including
Exponential – uses a curved line. Best for data that is rising and falling at higher and higher rates
Linear – uses a best-fit straight line. Suited for data that goes up or down at a steady rate
Logarithmic – uses a best fit curved line. Ideal for data that goes up or down quickly and then stays the same
Polynomial – uses a curved line. Should be used for fluctuating data
Power – also uses a curved line. Useful for data that increases at a known rate
Moving average – uses a curved trend line. A moving average smooths data out by averaging two or more data points.
Which trend line is best for your data depends on the nature of your data. Choose whichever trend line fits your data best according to the information above. Also bear in mind that exponential and power trend lines do not allow zero or negative values.
Adding a trend line to an existing chart
Once you have your data organised and your chart ready, the next step is to add the trend line. To do so, click on your chart so that the Chart Tools tabs are displayed in the Excel 2007 ribbon. Click on the Layout tab, then the Trendline button. Select the trend line you wish to use and it will automatically be added to your chart. I have changed mine to red and made the line wider (right click on the trend line select Format Trendline and then make any changes you would like to Line Colour and Line Style).
Configuring the data set to ensure an accurate trend line
The creation of a trend line is quite straightforward. Ensuring that your trend line is as accurate as possible is crucial, especially when you begin to project forwards. To illustrate this, let’s examine my data in more detail.
You can see that from my data, I had several days with zero hits before I began recording hits in Google Analytics. Also, my last day (9/12/2012) was in fact a half day. The trend line from the current graph is very steep. I also published a large number of hubs on the 22nd of August so the data before that date is not representative of the numbers I am now getting since their publication. So I trim out the numbers before that date and remove the last one.
You can now see that I have a much gentler upward trend which is what I expected from the data period I am interested in (the 22nd of August until the last full day). What this illustrates is how easy it is to incorrectly use trend lines to display a trend that is not actually there.
Forecasting using trend lines
Now that I have my trend which is going in a nice upward fashion, I am now curious to see how many hits Excel 2007 thinks I will get daily in two months time.
To do this, select the trend line in your chart and again select Format Trendline. Select Trendline Options and enter the number of periods (in my case days as my data is daily) you want to forecast forwards. In my example, I chose 60 periods (or days).
Just out of curiosity, let’s compare the two graphs of my data, showing the full data set including the data that is not representative to the one with just the representative data.
As you can see on the left, the forecast is over 200 and on the right it is just under 70. This illustrates the care that needs to be taken when using trend lines and forecasting.
So given what I have just illustrated with my own figures, there are two things to bear in mind when using trend lines to forecast future results.
- The more data you have the more accurate your forecasts will be. You can see from my graph above that my forecast trend line is much, much longer than my actual data which means it is less likely to be accurate
- Similarly, the further forward you forecast, the less accurate your forecast could become
Adding trend lines to your charts or graphs allows you to visually show a trend, both in your current data and also using forecasting to project future trends. As long as you take care to ensure that you are using a sample that is sufficiently big and also representative of your overall data, you can forecast with a high degree of confidence. I hope that you have enjoyed my latest hub on Excel 2007 and that you have found it useful. Please feel free to leave a comment below.
I also have a number of other hubs on aspects of Excel 2007, covering everything from Conditional Formatting to creating charts and graphs. I have an Index hub which also covers how I successfully transitioned from Excel 2003 to 2007 as well as outlining my other Excel 2007 hubs which can be found here