How to Convert Qualitative Data to Quantitative Data in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

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:

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

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

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mukesh Dipto

Mukesh Dipto is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including Rhino3D, Maxsurf C++, AutoCAD, HTML, CSS, JavaScript, and WordPress. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF