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

We have a dataset that contains information about several working time of some men and women. We will analyze the quantitative data using the T-Test, F-Test, ANOVA, and a Histogram.

### Step 1 – Create a Dataset with Proper Parameters

• We will make a dataset that contains information about working hours of some men and women.
• The column headers separate the groups by the conditions.
• Insert the values manually.
• Note that the Gender cells only have values in B5 and B9 and represent merged cells.

Read More: How to Analyze Qualitative Data in Excel

### Step 2 – Enable the Analysis ToolPak Add-in

• Press the File ribbon.
• Select Options (you may need to use the More menu).

• An Excel Options dialog box will appear.
• Select Add-ins.
• Select Excel Add-ins from the Manage drop-down list.
• Select the GO option.

• The Add-ins dialog box will pop up.
• Select Analysis ToolPakÂ and press OK.
• You will get the Data Analysis command inside theÂ Data ribbon.

### Step 3 – Perform a T-Test to Compare Means with Quantitative Data

• From your Data tab, go to Analysis and select Data Analysis.

• A Data Analysis dialog box will appear.
• Select t-Test Paired two Sample for Means under the Analysis Tools drop-down list.
• Press OK.

• A t-Test Paired two Sample for Means dialog box pops up.
• PutÂ \$C\$5:\$C\$12 in the Variable 1 Range box.
• Put \$D\$5:\$D\$12 in the Variable 2 Range box.
• Put \$H\$1 in the Output Range box and check it.
• Press OK.

• You will get a quantitative data analysis result using a T-Test analysis.

### Step 4 – Apply an F-Test Two-Sample for Variances with Quantitative Data

• From your Data tab, go to Analysis and select Data Analysis.

• A Data Analysis dialog box will appear.
• Select F-Test Two-Sample for Variances under the Analysis Tools drop-down list.
• Press OK.

• The F-Test Two-Sample for Variances dialog box pops up.
• Put \$C\$5:\$C\$12 in the Variable 1 Range box.
• Put \$D\$5:\$D\$12 in the Variable 2 Range box.
• Put \$G\$1 in the Output Range box and press OK.

• Here’s the result.

### Step 5 – Perform an ANOVA Two-Factor with Replication Analysis

• From your Data tab, go to Analysis and select Data Analysis.

• Select Anova: Two-Factor With Replication under the Analysis Tools drop-down list.
• Press OK.

• A dialog box pops up.
• Put \$B\$4:\$E\$12 in the Input Range typing box.
• Type 4 in the Rows per sample box.
• Use 0.05 in the Alpha box.
• Put \$G\$2 in the Output Range box andÂ press OK.

• Here’s the result.

• The rest of the AVONA analysis is like below:

Read More: How to Analyze Raw Data in Excel

### Step 6 – Use a Histogram to Analyze Quantitative Data

• From your Data tab, go to Analysis and select Data Analysis.

• Select Histogram under the Analysis Tools drop-down list.
• Press OK.

• A Histogram dialog box pops up.
• Put \$C\$5:\$E\$12 in the Input Range box.
• Put \$F\$5:\$F\$12 in the Bin Range box.
• Put \$H\$2 in the Output Range box.
• Check the Cumulative Percentage option.
• Press OK.

• You’ll get a chart-like histogram.

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.
• If a value can’t be found in the referenced cell, the #N/A! error happens in Excel.
• The #DIV/0! error happens when a value is divided by zero(0) or the cell reference is blank.

Download the Practice Workbook

## Related Articles

<< Go Back toÂ Data Analysis with ExcelÂ |Â Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF