Excel is the most widely used tool for dealing with huge datasets. We can perform myriads of tasks of multiple dimensions in Excel. Sometimes, we take the help of Excel to analyze data. Today, in this article, we’ll learn six quick and suitable steps to analyze quantitative data in Excel.
6 Quick Steps to Analyze Quantitative Data in Excel
Let’s say, we have a dataset that contains information about several working time of some men and women. We will analysis the quantitative data using the T-Test, F-Test, ANOVA, and Histogram. Here’s an overview of the dataset for today’s task.
Step 1: Create Dataset with Proper Parameters
In this portion, we will create a dataset to analyze quantitative data in Excel. We will make a dataset that contains information about several working hours of some men and women. So, our dataset becomes.
Read More: How to Analyze Qualitative Data in Excel
Step 2: Enable Analysis ToolPak Add-in
We need to enable the Data Analysis feature in Excel before doing any statistical tests. Let’s follow the instructions below to learn!
- First of all, press the File ribbon.
- Hence, select Options from the More menu.
- As a result, an Excel Options dialog box will appear in front of you. From the Excel Options dialog box, firstly, select Add-ins. Secondly, select Excel Add-ins from the Manage drop-down list. After that, select the GO option.
- So, the Add-ins dialog box will pop up.
- Afterward, select “Analysis ToolPak” and press OK.
- Lastly, we will see the Data Analysis command inside the Data ribbon.
Read More: How to Analyse Qualitative Data from a Questionnaire in Excel
Step 3: Perform T-Test to Compare Means with Quantitative Data
In this section, we will use the T-test to compare means with quantitative data. This is an easy task. Let’s follow the instructions below to learn!
- First of all, from your Data ribbon, go to,
Data → Analysis → Data Analysis
- As a result, a Data Analysis dialog box will appear in front of you. From the Data Analysis dialog box, firstly, select t-Test Paired two Sample for Means under the Analysis Tools drop-down list. At last, press OK.
- After that, t-Test Paired two Sample for Means dialog box pops up. From the t-Test Paired two Sample for Means dialog box, firstly, type $C$5:$C$12 in the Variable 1 Range typing box. Secondly, type $D$5:$D$12 in the Variable 2 Range typing box under Input Hence, type $H$1 in the Output Range typing box under the Output options. At last, press OK.
- After completing the above process, you will be able to get the quantitive data analysis result using T-Test analysis which has been given in the below screenshot.
Read More: How to Convert Qualitative Data to Quantitative Data in Excel
Step 4: Apply F-Test Two-Sample for Variances with Quantitative Data
Now, we will use the F-test to analysis the quantitative data. Let’s follow the instructions below to learn!
- First of all, from your Data ribbon, go to,
Data → Analysis → Data Analysis
- As a result, a Data Analysis dialog box will appear in front of you. From the Data Analysis dialog box, firstly, select F-Test two-Sample for Variances under the Analysis Tools drop-down list. At last, press OK.
- After that, F-Test two-Sample for Variances dialog box pops up. From the F-Test two-Sample for Variances dialog box, firstly, type $C$5:$C$12 in the Variable 1 Range typing box. Secondly, type $D$5:$D$12 in the Variable 2 Range typing box under Input Hence, type $G$1 in the Output Range typing box under the Output options. At last, press OK.
- After completing the above process, you will be able to get the quantitive data analysis result using F-Test analysis which has been given in the below screenshot.
Step 5: Perform ANOVA: Two-Factor with Replication
ANOVA is a statistical method used to analyze variance observed within a dataset. It is generally done by dividing it into two sections- systematic and random factors. Let’s follow the instructions below to learn!
- First of all, from your Data ribbon, go to,
Data → Analysis → Data Analysis
- As a result, a Data Analysis dialog box will appear in front of you. From the Data Analysis dialog box, firstly, select Anova: Two-Factor With Replication under the Analysis Tools drop-down list. At last, press OK.
- After that, Anova: Two-Factor With Replication dialog box pops up. From the F-Test two-Sample for Variances dialog box, firstly, type $B$4:$E$12 in the Input Range typing box. Secondly, type 4 in the Rows per sample typing box under Input Hence, type 0.05 in the Alpha typing box. Further, type $G$2 in the Output Range typing box under the Output options. At last, press OK.
- After completing the above process, you will be able to get the quantitive data analysis result using AVONA Test analysis which has been given in the below screenshot.
- Rest of the AVONA analysis is like below:
Read More: How to Analyze Raw Data in Excel
Step 6: Use Histogram to Analyze Quantitative Data
Last but not the least, we will use the Histogram to analysis the quantitative data. Let’s follow the instructions below to learn!
- First of all, from your Data ribbon, go to,
Data → Analysis → Data Analysis
- As a result, a Data Analysis dialog box will appear in front of you. From the Data Analysis dialog box, firstly, select Histogram under the Analysis Tools drop-down list. At last, press OK.
- After that, Histogram dialog box pops up. From the F-Test two-Sample for Variances dialog box, firstly, type $C$5:$E$12 in the Input Range typing box. Secondly, type $F$5:$F$12 in the Bin Range typing box under Input Hence, type $H$2 in the Output Range typing box under the Output options. Further, check the Cumulative Percentage option. At last, press OK.
- After completing the above process, you will be able to get the quantitive data analysis result using Histogram analysis which has been given in the below screenshot.
Read More: How to Analyze Time Series Data in Excel
Things to Remember
➜ Press ALT, F, then T to bring up the Excel Options dialog box. From that dialog box, firstly, select Add-ins option, secondly, select Analysis ToolPak option to analyze quantitative data.
➜ While a value can not found in the referenced cell, the #N/A! error happens in Excel.
➜ #DIV/0! error happens when a value is divided by zero(0) or the cell reference is blank.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
I hope all of the suitable methods mentioned above to analyze quantitative data will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
Related Articles
- How to Analyze Large Data Sets in Excel
- How to Analyze Text Data in Excel
- How to Analyze Sales Data in Excel
- How to Analyze Likert Scale Data in Excel
- How to Analyze qPCR Data in Excel
- How to Analyze Time-Scaled Data in Excel
<< Go Back to Data Analysis with Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
