Md. Abdur Rahim Rasel
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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 Data in Excel Using Pivot Tables (9 Suitable Examples)
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 Add Data Analysis in Excel (with 2 Quick Steps)
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: [Fixed:] Data Analysis Not Showing in Excel (2 Effective Solutions)
Similar Readings
- How to Convert Qualitative Data to Quantitative Data in Excel
- Analyze Time Series Data in Excel (With Easy Steps)
- How to Analyze Sales Data in Excel (10 Easy Ways)
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:
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.
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.
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.
