How to Convert Qualitative Data to Quantitative Data in Excel

This tutorial will demonstrate how to convert qualitative data to quantitative data in Excel. It is inaccurate to say that a qualitative study differs from a quantitative one. Researchers prefer quantitative results over qualitative data as it is more scientific, intense, objective, and acceptable. However, researchers also use a combination of qualitative and quantitative data. In this case, the strength of one type of data balances the limitation of another kind of data.

You can download the practice workbook from here.

3 Easy Methods to Convert Qualitative Data to Quantitative Data in Excel

Throughout this article, we will illustrate 3 easy methods to convert qualitative data to quantitative data in Excel. In the following dataset, we have qualitative data for two questions. The dataset includes data from a survey about people’s preferences in movies. Here, we will convert these qualitative data to quantitative data.

1. Use Excel Find and Replace Options to Convert Qualitative Data to Quantitative Data

In the first method, we will use the Find and Replace options to convert qualitative data to quantitative data in Excel. Basically, in this method, we will find a type of qualitative data. Then, we will replace it with quantitative data. Let’s follow the below steps to perform this action.

STEPS:

• To begin with, go to the Home tab.

• In addition, select Replace from the ‘Find and Replace’ option of the ribbon.

• A new dialogue box named ‘Find and Replace’ will appear.
• Furthermore, type Yes in the ‘Find what’ field. Also, type 1 in the ‘Replace with’ field.
• Then, click on the Replace All button.

• As a result, we can see the result in the following image.

• Again, type No in the ‘Find what’ field. Also, type 2 in the ‘Replace with’ field.

• So, the qualitative value No converts into quantitative value 2.

• Similarly, type 3, 4, 5, and 6 in the ‘Replace with’ field for the values Occasionally, Thriller, Comedy, and Sci–Fi in the ‘Find what’ field.
• Lastly, we get the result like the following image.

Read More: How to Analyse Qualitative Data from a Questionnaire in Excel

Similar Readings

• How to Analyze Time Series Data in Excel (With Easy Steps)
• [Fixed:] Data Analysis Not Showing in Excel (2 Effective Solutions)
• How to Analyze Sales Data in Excel (10 Easy Ways)

2. Convert Qualitative Data to Quantitative Data with Excel Nested IF Formula

In the second method, we will convert qualitative data to quantitative data with the nested IF formula. That means we will use multiple IF functions in our formula. To illustrate this method we will use the dataset of our previous example. Using the dataset we will convert the qualitative data to a different place on the same worksheet.

Just follow the below steps to implement this method.

STEPS:

• First, select cell H5.
• Next, type the following formula in that cell:

• Press Enter.
• So, in cell C5 we get the value 1.

• Then, drag the Fill Handle tool from cell H5 to H10.
• As a result, we can see results like the following image.

Here, the above formula returns 1 if the cell value is Yes, 2 if the cell value is No, and 3 if the cell value is Occasionally.

• Moreover, select cell I5. Insert the following formula in that cell:

• Hit Enter.
• So, we get the value 4 in cell I5.

• Now, drag the Fill Handle from cell I5 to I10.
• Finally, we get results like the following image.

Here, the above formula returns 4 if the cell value is Thriller, 5 if the cell value is Comedy, and 6 if the cell value is Sci-Fi.

Read More: How to Use Analyze Data in Excel (5 Easy Methods)

3. Apply VBA to Convert Qualitative Data to Quantitative Data in Excel

If you are an advanced Excel user you may be familiar with VBA. Using VBA we can easily convert qualitative data to quantitative data. We will use the same dataset that we used in the previous methods. To apply VBA code in this method we will follow the below steps.

STEPS:

• Firstly, go to the Developer tab. Select the option Visual Basic from the ribbon.

• A new VBA window will appear.
• Secondly, right-click on Sheet4 (VBA).
• Thirdly, select Insert > Module.

• The above action will open a blank VBA code window.
• Afterward, type the following code in that blank code window:

Sub Use_VBA()
Range("B4:D10").Replace What:="Yes", Replacement:="1", MatchCase:=True
End Sub

• Now, click on the Run button.

• From the highlighted part of the code, we can say that it will replace Yes with 1.
• So, we can see the result in the following image.

• Lastly, in the VBA code type 2, 3, 4, 5, and 6 in the ‘Replace with’ field for the values No, Occasionally, Thriller, Comedy, and Sci–Fi in the ‘Find what’ field.

Read More: How to Add Data Analysis in Excel (with 2 Quick Steps)

Conclusion

In conclusion, this tutorial shows how to convert qualitative data to quantitative data in Excel. Use the practice worksheet that comes with this article. It will help you to develop your skill. If you have any questions, please leave a comment below. Our team will try our best to respond to you as quickly as possible. Keep an eye out for more creative Microsoft Excel solutions in the future.

Related Articles

• How to Analyze Large Data Sets in Excel (6 Effective Methods)
• How to Analyze Likert Scale Data in Excel (with Quick Steps)
• Analyze Data in Excel Using Pivot Tables (9 Suitable Examples)
• How to Analyze Time-Scaled Data in Excel (With Easy Steps)
• How to Perform Case Study Using Excel Data Analysis
• How to Analyze Text Data in Excel (5 Suitable Ways)