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.
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
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:
=IF(C5="Yes",1,IF(C5="NO",2,IF(C5="Occasionally",3,0)))
- 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:
=IF(D5="Thriller",4,IF(D5="Comedy",5,IF(D5="Sci-Fi",6,0)))
- 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 Analyze Qualitative Data in Excel
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 Analyze Quantitative Data in ExcelÂ
Download Practice Workbook
You can download the practice workbook from here.
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 Raw Data in Excel
- How to Analyze Large Data Sets in ExcelÂ
- How to Analyze Text Data in ExcelÂ
- How to Analyze Time Series Data in Excel
- How to Analyze Sales Data in Excel
- How to Analyze Likert Scale Data in ExcelÂ
- How to Analyze qPCR Data in Excel
- How to Analyze Time-Scaled Data in ExcelÂ
<< Go Back to Data Analysis with Excel | Learn Excel