Analysis ToolPak in Excel

Analysis ToolPak in Excel (Table of Contents)

• Excel Data Analysis ToolPak
• List of Function Available in Excel Data Analysis Tools
  1. ANOVA: Single Factor
  2. Correlation
  3. Rank and Percentile
  4. Descriptive Statistics

Excel Data Analysis ToolPak

Data Analysis ToolPak is an add-in in Excel, which we can use for financial, statistical and engineering data analysis.

As this is one of the add-ins, we need to load this add-in first.

How to Load the Data Analysis Toolpak in Excel?

Step 1 – Click on ‘File’.

Step 2 – Click on ‘Options’ from the list.

Step 3 – Click on ‘Add-ins’ and then choose ‘Excel Add-ins’ for ‘Manage’. Click on ‘Go’.

Step 4 – The ‘Excel Add-ins‘ dialog box will appear with the list of add-ins. Please check for ‘Analysis ToolPak’ in excel and click on ‘OK’.

Step 5 –The command ‘Data Analysis’ will appear under the ‘Data’ tab in Excel at the extreme right of the ribbon as displayed below.

List of Functions Available in Excel Data Analysis ToolPak

Below is the list of available functions in Analysis Toolpak Excel Add-in:

#1 – ANOVA: Single Factor in Excel

ANOVA stands for Analysis of Variance and is the first set of options available in Analysis Toolpak Excel Add-in. In one way ANOVA, we analyze whether there are any statistical differences between the means of three or more independent groups. The null hypothesis proposes that no statistical significance exists in a set of given observations. We test this hypothesis by checking the p-value.

Let us understand this by an ANOVA excel example.

Example of Anova: Single Factor

Suppose, we have following data from the experiment conducted to check ‘Can self-control be restored during intoxication?’ We categorized 44 males into 4 equal groups comprising 11 males in each group.

• Group A received 0.62mg/kg of alcohol.
• Group AC received alcohol plus caffeine.
• Group AR received alcohol and a monetary reward for performance.
• Group P received a placebo.

Scores on a ward stem completion task involving “controlled (effortful) memory processes” were recorded and the result is as follows:

We need to test the null hypothesis which proposes that all means are equal (there is no significant difference).

To run the ANOVA one-way test, we need to perform the following steps:

• Click on the ‘Data Analysis’ command available in the ‘Data’ tab under ‘Analysis’.

• Select ‘Anova: Single Factor’ from the list and click on ‘OK’.

• We get ‘Anova: Single Factor’ dialog box. We need to select Input Range as our data with column heading.

• As we have taken column headings in our selection, we need to the checkbox for ‘Labels in the first row’.

• For output range, we have selected F1. Please click on ‘OK’.

We have now ANOVA analysis.

The larger the F-statistic value in excel, the more likely it is that the groups have different means which rejects the null hypothesis that all means are equal. An F-statistic greater than the critical value is equivalent to a p-value in excel less than alpha and both mean that we reject the null hypothesis. Hence, it is concluded that there is a significant difference between groups.

Things to Remember About ANOVA in Excel

We need to make sure that the first variance value is smaller than the second variance so that we will get the exact F Value.

#2 – Correlation in Excel

Correlation is a statistical measure available in Analysis Toolpak Excel Add-in and it shows the extent to which two or more variables fluctuate together. A positive correlation in excel indicates the extent to which those variables increase or decrease in parallel and a negative correlation indicates the extent to which one variable increases as the other decreases.

Example of Correlation in Excel

We have the following data related to advertising cost and sales for a company. We want to find out the relationship between both so that we can plan accordingly our budget and expect sales (set target considering other factors also).

To find out the correlation between these two sets of variables, we will follow below mentioned steps:

• Click on ‘Data Analysis’ under ‘Analysis’ group available in ‘Data’.

• Choose ‘Correlation’ from the list and click on ‘OK’.

• Choose range ‘$A$1:$B$16’ as input range and $F$1 as output range. Please tick the checkbox for ‘Labels in the first row’ as we have column headings in our input range and as we have different heads in a different column. We have chosen ‘Columns’ for ‘Grouped By’.

•  Select the Output range then, click on ‘OK’.

• We get the result.

As we can see, the correlation between advertising cost (column head) and Sales (row head) is +0.86274 approx. which indicates that they have a positive correlation and to 86.27% extent. Now we can accordingly take a decision about the advertising budget and expected sales.

#3 – Rank and Percentile in Excel

Percentile in excel refers to a number where a certain percentage of scores fall below that number and is available in the Analysis Toolpak Excel Add-in. For example, if a particular score is in the 90th percentile, that means the student has scored better than 90% of people who took the test. Let us understand this with an example.

Example of Rank & Percentile in Excel

We have the following data for the scores obtained by a student of a class.

We want to find out the rank and percentile for every student.

The steps would be:

• Click on ‘Data Analysis’ under ‘Analysis’ group available in ‘Data’.

• Click on ‘Rank and Percentile’ from the list and then click on ‘OK’.

• Select ‘$B$1:B$B$17’ as input range and ‘$D$1’ as output range.

• As we have data field heads in columns i.e., the data is grouped in columns, we need to select ‘Columns’ for ‘Grouped By’.

• We have selected column heading also in our input range that is why we need to check for ‘Labels in the first row’ then, click on ‘OK’.

• We got the result as the following image.

Using VLOOKUP, we can get the rank in our original data so that we get to know about the student’s name. Apply VLOOKUP Formula.

We got the result as given below.

#4 – Descriptive Statistics in Excel

Descriptive statistics included in the Analysis Toolpak Excel Add-in contains the following information about a sample:

1. Central Tendency
   1. Mean: It is called average.
   2. Median: This is the mid-point of the distribution.
   3. Mode: It is the most frequently occurring number.
2. Measures of Variability
   1. Range: This is the difference between the largest and smallest variables.
   2. Variance: This indicated how far the numbers are spread out.
   3. Standard Deviation: How much variation exists from the average/mean
3. Skewness: This indicates how symmetrical the distribution of a variable is.
4. Kurtosis: This indicates peakedness or flatness of a distribution.

Let us understand the same with a Descriptive Statistics excel example.

Example of Descriptive Statistics

Below we have marks scored by students in Economics subject. We want to find out descriptive statistics.

To do the same, the steps are:

• Click on the ‘Data Analysis’ command available in the ‘Analysis’ group in ‘Data’.

• Choose ‘Descriptive Statistics’ from the list and click on ‘OK’.

• Choose ‘$A$1:$A$15’ as input range, choose ‘Columns’ for ‘Grouped By’, tick for ‘Labels in the first row’,

• Choose ‘$C$1’ as output range and make sure that we have checked the box for ‘Summary Statistics’. Click on ‘OK’.

Now we have our descriptive statistics for the data.

Recommended Articles

This has been a guide to Data Analysis ToolPak Add-in in Excel. Here we discuss the steps to load data Analysis Toolpak in Excel for tools like 1) Anova, 2) Correlation, 3) Rank and Percentile, 4) Descriptive Statistics along with practical examples and downloadable excel template. You may learn more about excel from the following articles –

• What is the Percentile Rank Formula?
• Skewness Formula
• Explanation of LOOKUP Formula in Excel
• What is Linear Regression Formula?
• What is the Percentile Rank Formula in Excel?
• Using Exponential Smoothing in Excel
• Advanced Excel Formulas
• Basic Excel Formulas
• Using Correlation Matrix in Excel