How to Analyze Quantitative Data in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

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.

analyze quantitative data in excel


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.

analyze quantitative data in excel

  • 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.

analyze quantitative data in excel

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.

analyze quantitative data in excel

  • 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.

analyze quantitative data in excel

Read More: [Fixed:] Data Analysis Not Showing in Excel (2 Effective Solutions)


Similar Readings


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.

analyze quantitative data in excel

  • 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.

analyze quantitative data in excel


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.

analyze quantitative data in excel

  • 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:

analyze quantitative 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.

analyze quantitative data in excel

  • 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.

analyze quantitative 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.


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

Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo