How to Use Data Analysis Toolpak in Excel (13 Awesome Features)

Get FREE Advanced Excel Exercises with Solutions!

Data analysis toolpak is one of the best features of Excel when we need to do advanced statistical analysis. If you are looking for some special tricks for using data analysis toolpak in Excel, you’ve come to the right place. There are numerous ways to use data analysis toolpak in Excel. This article will discuss thirteen suitable examples of using Excel data analysis toolpak. Let’s follow the complete guide to learn all of this.


Steps to Enable Data Analysis Toolpak in Excel

Before analyzing all the features of the Excel data analysis toolpak, we have to show how to install this toolpak. Here we are going to demonstrate how to enable the Data Analysis Toolpak in Excel. To do this you have to follow the following process.

📌 Steps:

  • First, go to the Options from the File.

Installing  Data Analysis Toolpak

  • Then, go to the Add-ins.
  • Here, select Excel Add-ins in the Manage drop-down menu.
  • Click on Go.

How to Use Data Analysis Toolpak in Excel

  • Then, a new window will appear.
  • Here, mark the option Analysis ToolPak. Click on OK. This is how you will be able to activate Excel data analysis toolpak.

  • Now, go to the Analyze menu in the Data tab. Here you will find the Data Analysis option.

How to Use Data Analysis Toolpak in Excel


Data Analysis Toolpak That You Can Use in Excel: 13 Awesome Features

We will use thirteen effective and tricky ways to use data analysis toolpak in Excel. Here, we will demonstrate thirteen features of Excel data analysis toolpak. This section provides extensive details on the thirteen ways.  You can use either one for your purpose, they have a wide range of flexibility when it comes to customization. You should learn and apply all of these, as they improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.


1. Anova Analysis

Anova provides the first opportunity to determine which factors have a significant effect on a given set of data. After the analysis is completed, an analyst does extra analysis on the methodological factors that significantly impact the inconsistent nature of data sets. And he uses the Anova analysis findings in the f-test in order to create extra data relevant to the estimated regression analysis. The ANOVA analysis compares many data sets simultaneously to see whether there is a link between them. ANOVA is a statistical method used to analyze variance observed within a dataset by dividing it into two sections: 1) Systematic factors and 2) Random factors

The Formula of Anova:

F=MSE / MST

Here:

F = Anova coefficient

MST = Mean sum of squares due to treatment

MSE = Mean sum of squares due to error

Anova is of two types: single factor and two factors. The method is related to variance analysis.

  • In two factors, there are multiple dependent variables and in one factor, there will be one dependent variable.
  • Single-factor Anova calculates the effect of one single factor on a single variable. And it checks whether all the sample data sets are of same or not.
  • Single-factor Anova identifies the differences that are statistically significant between the average means of numerous variables.

1.1 Single-Factor Anova Analysis

Here, we will demonstrate how to do a single-factor Anova analysis. Let us first introduce you to our Excel dataset so that you are able to understand what we are trying to accomplish with this article. We have a dataset showing a group of factors. Let’s walk through the steps to do a single-factor Anova analysis.

Single Factor Anova Analysis

📌 Steps:

  • First, go to the Data tab in the top ribbon.
  • Then, select the Data Analysis tool.

  • When the Data Analysis window appears, select the Anova: Single Factor option.
  • Then, click on OK.

How to Use Data Analysis Toolpak in Excel

  • Now, the Anova: Single Factor window will open.
  • Provide data in the Input Range box, that you want to determine the Anova analysis by dragging through the column or row.
  • Check the box named Labels in First Row.
  • In the Output Range box, provide the data range that you want your calculated data to store by dragging through the column or row. Or you can show the output in the new worksheet by selecting New Worksheet Ply and you can also see the output in the new workbook by selecting New Workbook.
  • Next, you have to check the Labels in the first row if you select the input data range with the label.
  • Then, click on OK.

  • As a consequence, the Anova outcomes will be as shown below.

How to Use Data Analysis Toolpak in Excel

Interpretation of the Result:

  • In the Summary table, you will find each group’s average and variances. Here you can see the average level is 60.4 for Group A but the variance is 315.15 which is very low than other groups. That means, that in the group members are less valuable.
  • Here, Anova results are not that significant as you are calculating only the variances.
  • Here, P-values interpret the relation between the columns and the values are greater than 0.05 so this is not statistically significant. And there shouldn’t relation between the columns also.

1.2 Anova: Two Factor with Replication

Here, we are going to demonstrate two-factor with replication Anova analysis. Suppose you have some data on different exam scores of a school. There are two shifts in that school. One is for the morning shift other is for the day shift. You want to do a Data Analysis of the ready data to find a relation between two shifts’ students’ marks. Let’s walk through the steps to do a two-factor Anova with replication analysis.

Anova: Two Factor with Replication

📌 Steps:

  • First, go to the Data tab in the top ribbon.
  • Then, select the Data Analysis tool.

How to Use Data Analysis Toolpak in Excel

  • When the Data Analysis window appears, select the Anova: Two-Factor With Replication option.
  • Then, click on OK.

  • Now, a new window will appear.
  • Provide data in the Input Range box, that you want to determine the Anova analysis by dragging through the column or row.
  • Next, input 4 in the Rows per sample box as you have 4 rows per shift.
  • In the Output Range box, provide the data range that you want your calculated data to store by dragging through the column or row. Or you can show the output in the new worksheet by selecting New Worksheet Ply and you can also see the output in the new workbook by selecting New Workbook.
  • Then, click on OK.

How to Use Data Analysis Toolpak in Excel

  • As a result, you will see a new worksheet created.
  • And, two-way Anova result is shown in this worksheet.

Interpretation of the Result:

Here, the first tables, there are showing the summary of shifts. In brief:

  • The average score on the Morning shift in Math score is 65.5 but on the Day shift is 83.75
  • In the Chemistry exam, the average score on the Morning shift is 87.25, but on the Day shift is 77.25.
  • Variance is very high at 91 in the morning shift in the Math exam.
  • You will get a complete overview of the data in the summary.

How to Use Data Analysis Toolpak in Excel

Likewise, you can summarize the interactions and individual effects in the Anova part. In brief:

  • The P-value of Columns is 0.037 which is statistically significant so you can say that there is an effect of shifts on the performance of the students in the exam. But the value is close to the alpha value of 0.05 so the effect is less significant.
  • But the P-value of interactions is 0.000967 which is much less than the alpha value so it is very much significant statistically and you can say that the effect of the shift on both exams is very high.

How to Use Data Analysis Toolpak in Excel


1.3 Anova: Two Factor without Replication

Now, we are going to do variance analysis by following the method of two factors without replication of Anova Analysis. We Suppose you have some data on different exam scores of a school. There are two shifts in that school. One is for the morning shift other is for the day shift. You want to do a Data Analysis of the ready data to find a relation between two shifts’ students’ marks. Let’s walk through the steps to do two-factor ANOVA without replication analysis.

Anova: Two Factor without Replication

📌 Steps:

  • First, go to the Data tab in the top ribbon.
  • Then, select the Data Analysis tool.

  • When the Data Analysis window appears, select the “Anova: Two-Factor Without Replication” option.
  • Then, click on OK.

How to Use Data Analysis Toolpak in Excel

  • Now, a new window will appear.
  • Provide data in the Input Range box, that you want to determine the Anova analysis by dragging through the column or row.
  • In the Output Range box, provide the data range that you want your calculated data to store by dragging through the column or row. Or you can show the output in the new worksheet by selecting New Worksheet Ply and you can also see the output in the new workbook by selecting New Workbook.
  • Next, you have to check the Labels if the input data ranges with the label.
  • Then, click on OK.

  • As a result, you will see a new worksheet created.
  • As a consequence, you will get the two-way Anova result as shown below.

How to Use Data Analysis Toolpak in Excel

Interpretation of the Result:

  • The average score on the Morning shift in Math score is 65.5 but on the Day shift is 83.75
  • But when in the Chemistry exam, the average score on the Morning shift is 87 but in the Day shift is 77.25.
  • The P-value of Columns is 0.24 which is statistically significant so you can say that there is an effect of shifts on the performance of the students in the exam. But the value is close to the alpha value of 0.05 so the effect is less significant.

Read More: How to Use Analyze Data in Excel


2. Correlation Analysis

Now, we are going to do a correlation data analysis which is a great feature of Excel data analysis toolpak. In statistics, correlation or correlation coefficient is the parameter to show coherence between two variables in response to the continuous fluctuating quantity of another. Its value ranges from -1 to +1. Therefore, it has three states of defining variable relations. They are:

  • -1 indicates a negative correlation which means the variables change in the opposite direction.
  • +1 indicates a positive correlation which means the variables change in the same direction.
  • 0 indicates no correlation which means no apparent movements in any direction of a variable upon changing other variables’ values.

Here, we have a dataset containing two stock prices in different time periods. Let’s walk through the steps to do a correlation data analysis.

Correlation Analysis

📌 Steps:

  • First, go to the Data tab in the top ribbon.
  • Then, select the Data Analysis tool.

How to Use Data Analysis Toolpak in Excel

  • When the Data Analysis window appears, select the Correlation option.
  • Next, click on OK.

  • Now, a new window will appear.
  • Provide data in the Input Range box, that you want to calculate the correlation by dragging through the column or row.
  • Now, you have to check the Columns option in the Grouped By option.
  • In the Output Range box, provide the data range that you want your calculated data to store by dragging through the column or row. Or you can show the output in the new worksheet by selecting New Worksheet Ply and you can also see the output in the new workbook by selecting New Workbook.
  • Next, you have to check the Labels in the first row if the input data ranges with the label.
  • Then, click on OK.

How to Use Data Analysis Toolpak in Excel

  • As a consequence, you will get the following correlation result.

From the above calculation, we can see a positive correlation which means the variables changes in the same direction.

Read More: How to Install Data Analysis in Excel


3. Covariance Analysis

Now, we are going to do the covariance data analysis which is a great feature of Excel data analysis toolpak. The covariance of two variables is a measure of how one of them influences the other. Clearly, it’s a necessary evaluation of the deviation between two variables. Furthermore, the variables, do not have to be dependent on one another. Let’s walk through the steps to do covariance data analysis.

Covariance Analysis

📌 Steps:

  • First, go to the Data tab in the top ribbon.
  • Then, select the Data Analysis tool.

How to Use Data Analysis Toolpak in Excel

  • When the Data Analysis window appears, select the Covariance option.
  • Then, press Enter.
  • Then, click on OK.

  • Now, a new window will appear.
  • Provide data in the Input Range box, that you want to calculate the covariance by dragging through the column or row.
  • Now, you have to check the Columns option in the Grouped By section.
  • In the Output Range box, provide the data range that you want your calculated data to store by dragging through the column or row. Or you can show the output in the new worksheet by selecting New Worksheet Ply and you can also see the output in the new workbook by selecting New Workbook.
  • Next, you have to check the Labels in the first row if the input data range with the label.
  • Then, click on OK.

How to Use Data Analysis Toolpak in Excel

  • As a consequence, you will get the following covariance result.

Explanation of the Result:

The correlation matrix allows us to assess relationships between multiple variables and single variables. The highlight section from the following image indicates the variance for each subject.

  • The variance of Math is 154.9375 and the variance of Physics is 76.484375. Next, the variance of Chemistry is 154.9375.

How to Use Data Analysis Toolpak in Excel

The highlight section from the following image indicates the values of variance between the two subjects. Math and Physics, Maths and Chemistry, and Physics and History have a variance value of respectively 60.9375, 3.65625, and 8.8125. When the covariance, in this case, is positive, it indicates that the variables are proportionate, meaning that when one increases, the other tends to rise alongside it.

Read More: How to Make Histogram Using Analysis ToolPak 


4. Descriptive Statistics Analysis

Now, we are going to demonstrate how to do a descriptive statistics analysis. The Excel data analysis toolpak allows us to do a descriptive statistics analysis in order to analyze a dataset to determine its characteristics. Here, we have a dataset containing the physics score for each student. Let’s walk through the steps to do a correlation data analysis.

Descriptive Statistics Analysis

📌 Steps:

  • First, go to the Data tab in the top ribbon.
  • Then, select the Data Analysis tool.

How to Use Data Analysis Toolpak in Excel

  • When the Data Analysis window appears, select the Descriptive Statistics option.
  • Then, click on OK.

  • Now, a new window will appear.
  • Provide data in the Input Range box, that you want to calculate the descriptive statistics by dragging through the column or row.
  • Now, you have to check the Columns option in the Grouped By section.
  • In the Output Range box, provide the data range that you want your calculated data to store by dragging through the column or row. Or you can show the output in the new worksheet by selecting New Worksheet Ply and you can also see the output in the new workbook by selecting New Workbook.
  • Then, check the Summary statistics.
  • Then, click on OK.

How to Use Data Analysis Toolpak in Excel

  • As a consequence, you will get the following covariance result.

  • The above result gives us the characteristics of two variables, i.e. mean, median, standard deviation, and maximum and minimum value of the dataset, which are respectively 77.25, 80, 19.088, 45, and 100.

5. Exponential Smoothing Analysis

Now, we are going to demonstrate how to do an exponential smoothing analysis. The Excel data analysis toolpak allows us to do exponential smoothing in order to make appropriate decisions regarding business volume. Here, we have a dataset containing a number of items sold in different weeks by a manufacturing company. Let’s walk through the steps to do an exponential smoothing data analysis.

📌 Steps:

  • First, go to the Data tab in the top ribbon.
  • Then, select the Data Analysis tool.

How to Use Data Analysis Toolpak in Excel

  • When the Data Analysis window appears, select the Exponential Smoothing option.
  • Then, click on OK.

  • Now, a new window will appear.
  • Provide data in the Input Range box, that you want to calculate the exponential smoothing by dragging through the column or row.
  • Now, you have to enter 0.9 in the Damping factor box. Here, we damping 1-alpha.
  • In the Output Range box, provide the data range that you want your calculated data to store by dragging through the column or row. Or you can show the output in the new worksheet by selecting New Worksheet Ply and you can also see the output in the new workbook by selecting New Workbook.
  • Next, you have to check the Labels if the input data range with the label.
  • Then, check the Chart Output.
  • Then, click on OK.

How to Use Data Analysis Toolpak in Excel

  • As a consequence, you will get the following exponential smoothing result and chart for 0.1 alpha.

How to Use Data Analysis Toolpak in Excel

Here, from the above result, we can say that Excel can not provide data for the first value in this method. If we use a large damping factor, we will get more smooth peaks and valleys.


6. F-Test Two-Sample for Variances Analysis

Now, we are going to do an F-test for two sample variances.  Using the variance of two variables, the F-test provides statistical analysis in Excel. Here, we have a dataset containing two items’ sales prices in different weeks by a manufacturing company. Let’s walk through the steps to do an F-test for two sample variances.

F-Test for Two-Sample Variances Analysis

📌 Steps:

  • First, go to the Data tab in the top ribbon.
  • Then, select the Data Analysis tool.

How to Use Data Analysis Toolpak in Excel

 

  • When the Data Analysis window appears, select the F-Test Two-Sample for Variances option.
  • Then, click on OK.

  • Now, a new window will appear.
  • Provide data in the Input Range box, that you want to calculate the F-test by dragging through the column or row.
  • In the Output Range box, provide the data range that you want your calculated data to store by dragging through the column or row. Or you can show the output in the new worksheet by selecting New Worksheet Ply and you can also see the output in the new workbook by selecting New Workbook.
  • Next, you have to check the Labels if the input data ranges with the label.
  • Then, click on OK.

How to Use Data Analysis Toolpak in Excel

  • As a consequence, you will get the following result of an F-test for two sample variances.

How to Use Data Analysis Toolpak in Excel

Explanation of the Result:

  • From the above data, we can see that the mean value of variable 2 is greater than variable 1.
  • We also know that if the value F is greater than F Critical one-tail value, in this case, we can say it doesn’t follow the null hypothesis. In the above data, the value of F is .8016 and the value of F Critical one-tail is 0.3145 which indicates F is much greater than F critical one-tail. In other words, the variances between the two variables don’t match.

7. Moving Average Analysis

Now, we are going to do a moving average analysis which is one of the best features of Excel data analysis toolpak.. The moving average means the time period of the average is the same but it keeps moving when new data is added. Am moving average smooths out any irregularities (peaks and valleys) from data to easily recognize trends. The larger the interval period is to calculate the moving average, the more fluctuations smoothing occurs. As more data points are included in each calculated average. Here, we have a dataset containing a number of items sold in different weeks by a manufacturing company. Let’s walk through the steps to do a moving average analysis.

Moving Average Analysis

📌 Steps:

  • First, go to the Data tab in the top ribbon.
  • Then, select the Data Analysis tool.

  • When the Data Analysis window appears, select the Moving Average option.
  • Then, click on OK.

How to Use Data Analysis Toolpak in Excel

In the Moving Average pop-up box,

  • Provide data in the Input Range box, that you want to calculate the moving average by dragging through the column or row.
  • Write the number of intervals in the Interval.
  • In the Output Range box, provide the data range that you want your calculated data to store by dragging through the column or row. Or you can show the output in the new worksheet by selecting New Worksheet Ply and you can also see the output in the new workbook by selecting New Workbook.
  • If you want to see the trendline of your data with a chart then check the Chart Output otherwise leave it.
  • Next, you have to check the Labels in the first row if the input data range with the label.
  • Then, click on OK.

  • As a consequence, you will get the moving average of the data along with an Excel trendline showing both the original data and the moving average value with smooth fluctuations.

How to Use Data Analysis Toolpak in Excel


8. Random Number Generation

Now, we are going to generate a random number. The Excel data analysis toolpak allows us to generate random numbers with different criteria. Let’s walk through the steps to do a moving average analysis.

📌 Steps:

  • First, go to the Data tab in the top ribbon.
  • Then, select the Data Analysis tool.

Random Number Generation

  • When the Data Analysis window appears, select the Descriptive Statistics option.
  • Then, click on OK.

How to Use Data Analysis Toolpak in Excel

In the Random Number Generation pop-up box,

  • Provide data on the Number of Variables which indicates the number of columns of random numbers that you want in your worksheet. Here, we enter 2 as we want 2 columns.
  • Next, you have to provide data in the Number of Random Numbers which refers to the number of rows that you want in your worksheet. Here, enter 7 which means we want 7 rows in our worksheet.
  • Then, select the Uniform in the Distribution Here, Distribution means which kind of distribution of random numbers you want.
  • Here, parameters indicate the boundaries of your distribution. In this example, we set 40 to 60.
  • In the Output Range box, provide the data range that you want your calculated data to store by dragging through the column or row. Or you can show the output in the new worksheet by selecting New Worksheet Ply and you can also see the output in the new workbook by selecting New Workbook.
  • Then, click on OK.

  • As a consequence, you will be able to generate random numbers with some specified criteria as shown below.

How to Use Data Analysis Toolpak in Excel


9. Rank and Percentile Analysis

Now, we are going to do rank and percentile analysis. Here, we have a dataset containing students’ ID and their Math exam scores. We are going to calculate the rank and percentile of each student’s math exam score. Let’s walk through the steps to do rank and percentile analysis.

Rank and Percentile Analysis

📌 Steps:

  • First, go to the Data tab in the top ribbon.
  • Then, select the Data Analysis tool.

How to Use Data Analysis Toolpak in Excel

  • When the Data Analysis window appears, select the Rank and Percentile option.
  • Then, click on OK.

In the Rank and Percentile pop-up box,

  • Provide data in the Input Range box, that you want to calculate the moving average by dragging through the column or row.
  • Now, you have to check the Columns option in the Grouped By section.
  • In the Output Range box, provide the data range that you want your calculated data to store by dragging through the column or row. Or you can show the output in the new worksheet by selecting New Worksheet Ply and you can also see the output in the new workbook by selecting New Workbook.
  • Next, you have to check the Labels in the first row if the input data range with the label.
  • Then, click on OK.

How to Use Data Analysis Toolpak in Excel

  • As a consequence, you will get the rank and percentile for each student’s exam score as shown below.

From the above result, we can see that we are able to calculate the rank and percentile of each student’s mark. Here, the Rank 1 mark is 100 which is ID-5’s math score, and the last rank mark is 45 which is ID-6’s math score.

Read More: [Fixed!] Data Analysis Not Showing in Excel


10. Regression Analysis

Now, we are going to do regression analysis. Regression analysis is a part of statistics that helps to predict values depending on two or more variables. Here, we have a dataset containing the player name, the number of matches played by each player, and the number of goals given by each player. Let’s walk through the steps to do regression analysis.

Regression Analysis

📌 Steps:

  • First, go to the Data tab in the top ribbon.
  • Then, select the Data Analysis tool.

How to Use Data Analysis Toolpak in Excel

  • When the Data Analysis window appears, select the Regression option.
  • Then, click on OK.

In the Regression pop-up box,

  • Provide data in the Input Range box, and provide the data ranges in the Input X Range and Input Y Range boxes.
  • In the Output Range box, provide the data range that you want your calculated data to store by dragging through the column or row. Or you can show the output in the new worksheet by selecting New Worksheet Ply and you can also see the output in the new workbook by selecting New Workbook.
  • Next, you have to check the Labels if the input data range with the label.
  • You also have to check the Residuals option to get the output value.
  • Then, click on OK.

How to Use Data Analysis Toolpak in Excel

  • As a consequence, you will get the following result of the regression analysis.

Explanation of the Regression Analysis Result:

Regression Statistics:

How to Use Data Analysis Toolpak in Excel

Regression statistics is an array of various parameters that describe how well the measured linear regression is.

  • Multiple R is a correlation coefficient parameter that indicates the correlation between variables. Its value ranges from -1 to +1. The bigger the value, the stronger the correlative relationships are.
  • R Square symbolizes the coefficient of determination. It indicates the scale by how well the data model fits the regression analysis.
  • The adjusted R square is used in multiple variables in regression analysis.
  • Standard Error is another parameter that shows a healthy fit of any regression analysis. The smaller the standard error the more accurate the linear regression equation. It shows the average distance of data points from the linear equation.

Anova:

How to Use Data Analysis Toolpak in Excel

It analyses the variance of the data model.

  • Here, df represents the degree of freedom.
  • SS( sum of squares) symbolizes the good-to-fit parameter.
  • MS means the Mean Square.
  • F refers to the Null Hypothesis. It tests the overall significance of the regression model
  • Significance of F means the P-value of F.

Co-efficient Outcome:

It helps to determine Y values easily.

Residual Output:

How to Use Data Analysis Toolpak in Excel

So, it compares the estimated value with the calculated value.


11. t-Test Analysis

Now, we are going to do a T-Test analysis of the dataset. The T-Test is of three types:

  • Paired two samples for means
  • Two samples assuming equal variances
  • Two- samples using unequal variances

This section provides extensive details on the three types of t-Test analysis. You can use either one for your purpose, they have a wide range of flexibility when it comes to customization.


11.1 t-Test: Paired Two Sample for Means

Now, we are going to do a t-Test: Paired Two Sample for Means. Here, we have a dataset containing the Students’ IDs and each student’s Math and Physics scores. Let’s walk through the steps to do a t-Test: Paired Two Sample for Means analysis.

t-Test: Paired Two Sample for Means

📌 Steps:

  • First, go to the Data tab in the top ribbon.
  • Then, select the Data Analysis tool.

  • When the Data Analysis window appears, select the t-Test: Paired Two Samples for Means option.
  • Then, click on OK.

How to Use Data Analysis Toolpak in Excel

In the t-Test: Paired Two Sample for Means pop-up box,

  • Provide data in the Input box, and provide the data ranges in the Variable 1 Range and Variable 2 Range boxes.
  • In the Output Range box, provide the data range that you want your calculated data to store by dragging through the column or row. Or you can show the output in the new worksheet by selecting New Worksheet Ply and you can also see the output in the new workbook by selecting New Workbook.
  • Next, you have to check the Labels if the input data range with the label.
  • Then, click on OK.

  • As a consequence, you will get the following result of the t-Test: Paired Two Sample for Means.

How to Use Data Analysis Toolpak in Excel

Explanation of the Result:

  • Here, we can see that the Mean Value of the Math Score is greater than the mean value of the Physics Score.
  • The variance of the Math Score is also greater than the variance of the Physics Score.
  • If t Stat is greater than t Critical two-tail, in this condition we can’t eliminate null hypothesis. In the above calculation, we can see that t Stat is and t Critical two-tail value is respectively 1.603 and 2.36464. That means 1.603< 2.36464, it doesn’t match the null hypothesis. In other words, the variances between two variables don’t match.

11.2 t-Test Two-Sample Assuming Equal Variances

Now, we are going to do a t-Test: Two-Sample Equal Variances. Here, we have a dataset containing the Students’ IDs and each student’s Math and Physics scores. Here, equal variance means that we have taken our data from regular distribution populations. Let’s walk through the steps to do a t-Test: Two-Sample Equal Variances analysis.

t-Test Two-Sample Assuming Equal Variances

📌 Steps:

  • First, go to the Data tab in the top ribbon.
  • Then, select the Data Analysis tool.

How to Use Data Analysis Toolpak in Excel

  • When the Data Analysis window appears, select the t-Test: Two-Sample Equal Variances option.
  • Then, click on OK.

In the t-Test: Paired Two Sample Equal Variances pop-up box,

  • Provide data in the Input box, and provide the data ranges in the Variable 1 Range and Variable 2 Range boxes.
  • In the Output Range box, provide the data range that you want your calculated data to store by dragging through the column or row. Or you can show the output in the new worksheet by selecting New Worksheet Ply and you can also see the output in the new workbook by selecting New Workbook.
  • Next, you have to check the Labels if the input data range with the label.
  • Then, click on OK.

  • As a consequence, you will get the following result of the t-Test: Two-Sample Equal Variances.

How to Use Data Analysis Toolpak in Excel

Explanation of the Result:

  • Here, we can see that the Mean Value of the Math Score is greater than the mean value of the Physics Score.
  • The variance of the Math Score is also greater than the variance of the Physics Score.
  • If t Stat is greater than t Critical two-tail, in this condition we can’t eliminate the null hypothesis. In the above calculation, we can see that t Stat is and t Critical two-tail value is respectively 1.48 and 2.144. That means 1.48< 2.144, it doesn’t match the null hypothesis. In other words, the variances between two variables don’t match.

11.3 t-Test: Two-Sample Assuming Unequal Variances

Now, we are going to do a t-Test: Two-Sample Unequal Variances. Here, we have a dataset containing the Students’ IDs and each student’s Math and Physics scores. Here, unequal variance means that we have taken our data from irregular distribution populations. Let’s walk through the steps to do a t-Test: Two-Sample Unequal Variances analysis.

t-Test: Two-Sample Assuming Unequal Variances

📌 Steps:

  • First, go to the Data tab in the top ribbon.
  • Then, select the Data Analysis tool.

How to Use Data Analysis Toolpak in Excel

  • When the Data Analysis window appears, select the t-Test: Two-Sample Unequal Variances option.
  • Then, click on OK.

In the t-Test: Paired Two Sample Unequal Variances pop-up box,

  • Provide data in the Input box, and provide the data ranges in the Variable 1 Range and Variable 2 Range boxes.
  • In the Output Range box, provide the data range that you want your calculated data to store by dragging through the column or row. Or you can show the output in the new worksheet by selecting New Worksheet Ply and you can also see the output in the new workbook by selecting New Workbook.
  • Next, you have to check the Labels if the input data range with the label.
  • Then, click on OK.

How to Use Data Analysis Toolpak in Excel

  • As a consequence, you will get the following result of the t-Test: Two-Sample Unequal Variances.

Explanation of the Result:

  • Here, we can see that the Mean Value of the Math Score is greater than the mean value of the Physics Score.
  • From the above result, we can see the variance of the Math Score is also greater than the variance of the Physics Score.
  • If t Stat is greater than t Critical two-tail, in this condition we can’t eliminate null hypothesis. In the above calculation, we can see that t Stat is and t Critical two-tail value is respectively 79 and 2.131. That means 1.79< 2.131, it doesn’t match the null hypothesis. In other words, the variances between two variables don’t match.

12. z-Test: Two Sample for Means

Now, we are going to do a z-Test: Two-Sample Means. Here, we have a dataset containing the Students’ IDs and each student’s Math and Physics scores. Here we will use the VAR.P function to calculate the variance of both variables of the following dataset. Let’s walk through the steps to do a z-Test: Two-Sample Means analysis.

z-Test: Two Sample for Means

📌 Steps:

  • First of all, to calculate the variance of the Math score, we will use the following formula in the cell D14:

=VAR.P(C5:C12)

  • Then, press Enter.
  • As a consequence, you will get the following variance of Match Score.

How to Use Data Analysis Toolpak in Excel

  • Next, to calculate the variance of the Physics score, we will use the following formula in the cell D15:

=VAR.P(D5:D12)

  • Then, press Enter.
  • As a consequence, you will get the following variance of Physics Score.

  • First, go to the Data tab in the top ribbon.
  • Then, select the Data Analysis tool.

How to Use Data Analysis Toolpak in Excel

  • When the Data Analysis window appears, select the z-Test: Two-Sample Means option.
  • Then, click on OK.

In the z-Test: Two-Sample Means pop-up box,

  • Provide data in the Input box, and provide the data ranges in the Variable 1 Range and Variable 2 Range boxes.
  • In the Output Range box, provide the data range that you want your calculated data to store by dragging through the column or row. Or you can show the output in the new worksheet by selecting New Worksheet Ply and you can also see the output in the new workbook by selecting New Workbook.
  • You have to enter the value variance of Math and Physics Score respectively in the Variable 1 Variance (known) and Variable 2 Variance (known)
  • Next, you have to check the Labels if the input data range with the label.
  • Then, click on OK.

How to Use Data Analysis Toolpak in Excel

  • As a consequence, you will get the following result of the z-Test: Two-Sample Means option.

How to Use Data Analysis Toolpak in Excel

Explanation of the Result:

  • Here, we can see that the Mean Value of the Math Score is greater than the mean value of the Physics Score.
  • From the above result, we can see the variance of the Math Score is also greater than the variance of the Physics Score.
  • If Z is less than Z critical two-tall, in this condition we can’t eliminate the null hypothesis. In the above calculation, we can see that z and z Critical two-tail values are 52 and 1.95. That means 1.52 < 1.95, which matches the null hypothesis. In other words, the variances between two variables match.

13. Sampling Analysis

Now, we are going to do a sampling analysis which is one of the best features of the Excel data analysis toolpak. Here, we have a dataset containing two items individual sales and total sales value for different time periods. Let’s walk through the steps to do sampling analysis.

Sampling analysis

📌 Steps:

  • First, go to the Data tab in the top ribbon.
  • Then, select the Data Analysis tool.

  • When the Data Analysis window appears, select the Sampling option.
  • Then, click on OK.

How to Use Data Analysis Toolpak in Excel

In the Sampling pop-up box,

  • Provide data in the Input Range box, that you want to calculate the moving average by dragging through the column or row.
  • In the Output Range box, provide the data range that you want your calculated data to store by dragging through the column or row. Or you can show the output in the new worksheet by selecting New Worksheet Ply and you can also see the output in the new workbook by selecting New Workbook.
  • You have to enter data in the Number of Samples option.
  • Next, you have to check the Labels if the input data range with the label.
  • Then, click on OK.

  • As a consequence, you will get the following result of the Sampling analysis. In the following picture, we are able to pick up six samples from the Total Sales column. If you want to pick up more sample data from this column, you have to enter more numbers in the Number of Samples box when the Sampling window appears.

How to Use Data Analysis Toolpak in Excel


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets and methods of using data analysis toolpak in different spreadsheets for a clear understanding.


Conclusion

That’s the end of today’s session. Here, we demonstrate thirteen suitable examples to use the data analysis toolpak. I strongly believe that from now on, you may be able to use data analysis toolpak in Excel. If you have any queries or recommendations, please share them in the comments section below.


Related Articles


<< Go Back to Data Analysis with Excel Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Saquib Ahmad Shuvo
Saquib Ahmad Shuvo

Saquib Ahmad Bhuiyan, holding a BSc degree in Naval Architecture & Marine Engineering from Bangladesh University of Engineering and Technology, skillfully integrates engineering expertise with a passion for Excel. He provides solutions to complex issues as an Excel & VBA Content Developer for ExcelDemy Forum. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing his commitment to excellence. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, Rhinoceros,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo