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.

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


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.

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

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

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

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

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

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

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

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

Convert Qualitative Data to Quantitative Data with Excel Nested IF Formula

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.

Convert Qualitative Data to Quantitative Data with Excel Nested IF Formula

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

Convert Qualitative Data to Quantitative Data with Excel Nested IF Formula

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:

Apply VBA to Convert Qualitative Data to Quantitative Data in Excel

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

Apply VBA to Convert Qualitative Data to Quantitative Data in Excel

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

Apply VBA to Convert Qualitative Data to Quantitative Data in Excel

  • 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


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

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo