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.

    Click to Rate This Article