How to Analyze Quantitative Data in Excel

This guide will discuss in detail how to analyze quantitative data in Excel.

Quantitative data in research refers to using numbers, charts, or tables rather than words or text, variables, and factors. So this would mean analyzing our data and representing it with charts or tables. 

And Excel is an excellent tool to use when dealing with quantitative data analysis. Because it has many built-in tools available for data analysis, analyzing quantitative data is easier and more efficient in Excel.

Let’s take a sample scenario wherein we need to analyze quantitative data in Excel.

Suppose you are a graduating student who needs to complete a research paper. And you have already collected the data from the survey. Now you only need to analyze your data to complete your research. And you opted to use Excel since it would make the task faster and more efficient.

Great! Now let’s dive into a real example of analyzing quantitative data in Excel.

A Real Example of Analyzing Quantitative Data in Excel

Let’s say we have collected data from our research survey. And the next step would be to organize and analyze the data. Finally, we need to represent the results through charts or tables. So our data set would look something like this:

Initial data set

 

Firstly, we need to arrange and filter our data. More importantly, we need to remove duplicated data. Furthermore, we can easily remove duplicate values using conditional formatting. 

Secondly, we can also calculate the average results for each question in our survey. Additionally, we can also calculate the t-test using the T.TEST function in Excel.

Thirdly, we will create a clean table containing the results of each question. Afterward, we can make a chart to display our results using the clean table.

And our final output would look something like this:

Final output

 

You can make your own copy of the spreadsheet above using the link attached below. 

Awesome! Now it’s time to learn the step-by-step process of how to analyze quantitative data in Excel.

How to Analyze Quantitative Data in Excel

In this section, we will explain the process of how to analyze quantitative in Excel, from organizing the data to analyzing and displaying the results with charts or tables. 

1. Firstly, we need to organize our data set. So select the headers of the data set and go to the Data tab. Then, click on the Filter icon to apply a filter to the headers.

Applying a filter

 

2. Secondly, we need to check for duplicated values and remove them. To do this, we first need to create a unique ID to identify them. So select column A and right-click. Then, select Insert Columns in the dropdown menu. And this will insert a new column on the left where we will input our unique ID.

Adding a unique ID

 

3. Thirdly, we will use the CONCAT function to create our unique ID. So input the formula “=CONCAT(B2)” in cell A2. Then, press the Enter key to return the result.

Using the CONCAT function

 

4. Next, drag down the formula to copy to the rest of the column and obtain a unique ID for each response.

Copying the same formula

 

5. Afterward, select columns A and  B and go to the Home tab. In the Home tab, select Conditional Formatting. Then, select Highlight Cell Rules. Finally, click Duplicate Values.

Analyze Quantitative Data in Excel

 

6. And the Conditional Formatting window will appear. Unless you wish to change the highlight colors, we can simply click Done to apply the format. 

Essentially, highlighted cells would imply that they are a duplicate value, and we can delete them from our data set.

Analyze Quantitative Data in Excel

 

7. Then, we can delete the Unique ID column from our data set since we have identified the duplicated values.

Analyze Quantitative Data in Excel

 

8. Next, we will sort of data set. So select the entire table and go to the Data tab. Then, click on Sort Descending. This will sort our data set from the largest to the smallest, making it easier to do calculations later on.

Analyze Quantitative Data in Excel

 

9. Afterward, we can start doing calculations. In this case, we want to obtain the average results for each question. So input the formula “=AVERAGE(B2:B6)”.

Analyze Quantitative Data in Excel

 

10. Next, simply drag the formula to the right to copy and get the results for the rest of the questions.

Analyze Quantitative Data in Excel

 

11. Additionally, we can also perform hypothesis testing in Excel. For example, we can quickly do a t-test in Excel if we have two data results to compare. To use the T.TEST function, simply type in an “=” equal sign to activate a function and type “T.TEST”. 

Then, select the necessary components needed, such as the two groups or ranges you want to analyze, one-sided or two-sided, and the type of t-test you want.

Analyze Quantitative Data in Excel

 

12. Now, we are done organizing and analyzing our data. Next, we will start presenting the results. Firstly, let’s create a new clean table. So select the entire table and press Ctrl C to copy. Then, right-click and choose the Paste Value option.

Analyze Quantitative Data in Excel

 

13. Next, select the questions and press Ctrl C to copy them. Then, choose another cell to paste it. So right-click and choose the Paste Transpose option. And this will paste the questions vertically.

Analyze Quantitative Data in Excel

 

14. Afterward, we will repeat the same process for the average results. So copy the averages and choose the Paste Transpose option to display them vertically.

Analyze Quantitative Data in Excel

 

15. Next, we can display our results using charts. Firstly, select the data and go to the Insert tab. From there, click on the Charts dropdown menu and choose any chart appropriate for your data set. In this case, we will choose a Pie chart.

Analyze Quantitative Data in Excel

 

16. Afterward, the chart will appear. Then, right-click the chart and select Format to edit the formatting of our chart. Alternatively, we can simply double-click any part of the chart.

Analyze Quantitative Data in Excel

 

17. And the Chart window will open. In the Chart window, we can make some changes to the formatting of our chart. So we have the option to edit the Chart Title, Legend, and Series of the chart. Simply click on the arrow to open them and start making changes to your preference.

Analyze Quantitative Data in Excel

 

18. And tada! We have successfully analyzed quantitative data in Excel.

Analyze Quantitative Data in Excel

 

And that’s pretty much it! We have discussed thoroughly how to analyze quantitative data in Excel. And there are still many tools in Excel you can utilize when working with quantitative data besides calculating the average, t-test, and using charts. Now you can apply this in your own work.

Are you interested in learning more about what Excel can do? You can now use the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like