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.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)