ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Adding trend lines to Excel 2007 charts

Updated on September 14, 2012

Introduction

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:

http://robbiecwilson.hubpages.com/hub/Creating-charts-and-graphs-in-Excel-2007

http://robbiecwilson.hubpages.com/hub/Creating-Configuring-and-Using-Pivot-Charts-in-Excel-2007

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).

Adding a trend line to a graph in Excel 2007.
Adding a trend line to a graph in Excel 2007. | Source

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.

Data used to create a graph with a trend line in Excel 2007.
Data used to create a graph with a trend line in Excel 2007. | Source

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.

Graph with an added trend line in Excel 2007.
Graph with an added trend line in Excel 2007. | Source

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).

Configuring a trend line to forecast the future in Excel 2007.
Configuring a trend line to forecast the future in Excel 2007. | Source
Example of a graph with a trend line forecasting the future in Excel 2007.
Example of a graph with a trend line forecasting the future in Excel 2007. | Source

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.

Showing two graphs in Excel 2007 with trend lines showing the importance of accurate data.
Showing two graphs in Excel 2007 with trend lines showing the importance of accurate data. | Source

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

Conclusion

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

http://robbiecwilson.hubpages.com/hub/How-to-adjust-to-Excel-2007-from-previous-versions-as-well-as-step-by-step-guides-to-many-functions-in-Excel-2007

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    Click to Rate This Article