ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Using the Analysis ToolPak in Excel 2007 and Excel 2010

Updated on May 2, 2013

Introduction

Welcome to my latest hub on Excel 2007 and Excel 2010. Today, I am going to discuss the Analysis ToolPak which is available in both of these versions of Excel. The ToolPak is used for statistical and engineering analysis of the data contained in your Excel spreadsheet.

In this hub, I will introduce the Analysis ToolPak and discuss how to add the ToolPak to both Excel 2007 and 2010 as well as fixing the common error where the ToolPak has been added but is not visible. Which tool you use depends very much on what you are looking to discover from analysing your data. I have hubs covering all of the commonly used tools in the ToolPak:

Example of a Correlation created using the Analysis ToolPak in Excel 2007 and Excel 2010.
Example of a Correlation created using the Analysis ToolPak in Excel 2007 and Excel 2010. | Source

Correlation

A correlation is a relationship between two variables and can be a positive relationship (when one goes up so does the other), or negative (when one goes up, the other goes down) or there could be no correlation between your two variables. Suppose you have some data on daily temperatures and pie sales, you may choose to test whether there is a Correlation between the daily temperature you recorded and the sales of pies on that day.

My hub on correlation can be found here:

http://robbiecwilson.hubpages.com/hub/Using-the-Correlation-Tool-from-the-Excel-2007-and-Excel-2010-Analysis-ToolPak

Example of a Regression created using the Analysis ToolPak in Excel 2007 and Excel 2010.
Example of a Regression created using the Analysis ToolPak in Excel 2007 and Excel 2010. | Source

Regression

Using regression, you can visually summarise the relationship between a number of variables. You can then use this to predict variables when you know the value of other variables. As with correlations, there can be a positive relationship and a negative relationship, or no relationship whatever. In my hub, I look at the relationship between concentrations of Phosphate and Nitrogen on fish mortality. Click here for my hub on regression:

http://robbiecwilson.hubpages.com/hub/Using-the-Regression-Tool-from-the-Analysis-ToolPak-in-Excel-2007-and-Excel-2010

Using the Sampling Tool to illustrate data variation with the Analysis ToolPak in Excel 2007 and Excel 2010
Using the Sampling Tool to illustrate data variation with the Analysis ToolPak in Excel 2007 and Excel 2010 | Source

Sampling

Is the creation of a small sample in a larger population. You may use it to look at a part of the population that is different from the larger population. I have data on the number of hits my websites each day for the last several months. I believe that there is a definite reduction of hits over the weekend and would like to test for this. To achieve this, I can take a sample of the data during the week and during the weekend using Sampling and test to see if there is a statistical difference between the week and the weekend. To learn more about Sampling, my hub can be located here:

http://robbiecwilson.hubpages.com/hub/Use-the-Sampling-Tool-in-the-Excel-2007-and-Excel-2010-Analysis-ToolPak-for-sampling-and-to-analyse-periodic-variations

Example of a chart showing a Moving Average Trend line, created using the Moving Average Tool from the Analysis ToolPak in Excel 2007 and Excel 2010.
Example of a chart showing a Moving Average Trend line, created using the Moving Average Tool from the Analysis ToolPak in Excel 2007 and Excel 2010. | Source

Moving Average

Is used to test for a trend in your data.For example, I have data showing the number of hits my websites receive over time. I would like to look for a trend in that data to determine if my daily hits are trending up or not. The Moving Average Tool will create a trend line as part of the results which will illustrate any trend. My hub on the Moving Average Tool can be found here:

http://robbiecwilson.hubpages.com/hub/Using-the-Moving-Average-Tool-from-the-Excel-2007-and-Excel-2010-Analysis-ToolPak

Student results ranked and added to a percentile using the Rank and Percentile tool in the Analysis ToolPak in Excel 2007 and Excel 2010.
Student results ranked and added to a percentile using the Rank and Percentile tool in the Analysis ToolPak in Excel 2007 and Excel 2010. | Source

Rank and Percentile

Allows you to rank your data and also assign it a percentile. This is the perfect tool for taking data such as exam results and ranking the data top to bottom and then assigning the results a percentile which can be used for grades to create a ranking table. My hub on the Rank and Percentile Tool can be accessed here:

http://robbiecwilson.hubpages.com/hub/Use-the-Rank-and-Percentile-Tool-from-the-Analysis-ToolPak-in-Excel-2007-and-Excel-2010-to-create-ranking-tables

Example of a Histogram created using the Analysis ToolPak in Excel 2007 and Excel 2010.
Example of a Histogram created using the Analysis ToolPak in Excel 2007 and Excel 2010. | Source

Histogram

Shows the distribution of your data graphically across boundaries that you can define. You can also rank the data from most popular to least popular again using the defined boundaries. You can also utilise cumulative percentages should your data require it. My hub on histograms can be found here:

http://robbiecwilson.hubpages.com/hub/Using-the-Histogram-Tool-from-the-Analysis-ToolPak-in-Excel-2007-and-Excel-2010

Adding the Analysis ToolPak to Excel 2007

The Analysis ToolPak can be found on the Data tab and is the only button in the Analysis group. The button itself is called Data Analysis.

The Analysis ToolPak button shown in Excel 2007.
The Analysis ToolPak button shown in Excel 2007. | Source

Should you find that the Data Analysis button is not available, you may need to add the ToolPak to Excel 2007. To do this:

  • Click on the Office button
  • Select Excel Options
  • Next, click on Add-ins
  • If Analysis ToolPak is not listed in the section Active Application Add-ins then click on Go under Manage

How to add the Analysis ToolPak to Excel 2007.
How to add the Analysis ToolPak to Excel 2007. | Source

This will show you which add-ins are installed

  • If you do not see Analysis ToolPak listed, browse to the add-in and select OK twice to add it

Adding the Analysis ToolPak to Excel 2010

The process for adding the ToolPak is different if you are using Excel 2010. If you have the Developer tab enabled, then click the Add-Ins button which is part of the Add-Ins group on the Developer tab.

How to access the Add-Ins button in Excel 2010.
How to access the Add-Ins button in Excel 2010. | Source
  • Select the Analysis ToolPak from the list of Add-ins as below

How to add the Analysis ToolPak to Excel 2010.
How to add the Analysis ToolPak to Excel 2010. | Source

If you do not have the Developer tab enabled, to add the ToolPak:

  • Navigate to the File menu
  • Select Options
  • Select the Add-ins tab
  • Click Go (illustrated by the red arrow below)

Adding the Analysis ToolPak to the enabled add-ins in Excel 2010.
Adding the Analysis ToolPak to the enabled add-ins in Excel 2010. | Source

The Developer tab can be added by:

  • Navigating to the File menu
  • Select Options
  • Choose the Customise Ribbon tab
  • Click Developer (again shown by the red arrow below)

Adding the Developer tab to Excel 2010.
Adding the Developer tab to Excel 2010. | Source

What to do if the Analysis ToolPak is added but not Visible in Excel 2007 and Excel 2010

As happened to me when I first tried to use it, often it is added but does not appear as a button on the Data tab. To resolve this for Excel 2007:

  1. Click on the Office button
  2. Select Excel Options
  3. Next, click on Add-ins
  4. Click on Go under Manage
  5. De-select the Analysis ToolPak on the list of Add-Ins available
  6. Click OK and repeat the above steps one through four
  7. This time, select Analysis ToolPak and click OK for the final time

For Excel 2010:

  1. Navigate to the File menu
  2. Select Options
  3. Select the Add-ins tab
  4. Click Go
  5. Uncheck the Analysis ToolPak from the list of Add-Ins available
  6. Click OK and repeat the above steps one through four
  7. This time, select Analysis ToolPak and click OK for the final time

This should add the ToolPak into Excel 2007 and Excel 2010 and allow you to use it to perform statistical analysis on your data.

Using the Analysis ToolPak in Excel 2007 and Excel 2010 for data Analysis

Once you have your data and you have determined which test you will apply on the data, click on the Data Analysis button

Excel will then give you a list of Analysis Tools you can choose from. Choose your tool from the list and click OK.

The tools available within the Analysis ToolPak in Excel 2007 and Excel 2010.
The tools available within the Analysis ToolPak in Excel 2007 and Excel 2010. | Source

Above is an example of the dialogue box from Moving Average. The options I selected will give me a moving average with a chart created to display the results (you will notice below that Excel 2007 and Excel 2010 also creates a forecast line in the chart it creates which is significantly smoother than the actual moving average). To create the resultant moving average, Excel 2007 and Excel 2010 averages the number I choose in the Interval (in this case three). A moving average is a very useful tool for predicting trends in data.

Examples of the output from the Moving Average tool in the Analysis ToolPak in Excel 2007 and Excel 2010.
Examples of the output from the Moving Average tool in the Analysis ToolPak in Excel 2007 and Excel 2010. | Source

Conclusion

Excel has always been a very powerful application and both Excel 2007 and Excel 2010 are no different. Through the Analysis ToolPak, Excel 2007 and Excel 2010 are capable of performing some very powerful statistical analysis on your data. This hub has introduced some real world examples of uses of the ToolPak as well as how to add the ToolPak to Excel 2007 and Excel 2010. It also covers troubleshooting situations where the ToolPak is loaded into Excel but does not appear.

Many thanks for reading, I do hope that you found this hub useful in explaining what the Analysis ToolPak is and how its suite of tools can benefit you. Please feel free to leave any comments you may have below.

And Finally...

Which Tool from the Analysis ToolPak in Excel 2007 and Excel 2010 do you intend to (or already regularly) use?

See results

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)