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.


Download Practice Workbook

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.

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


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

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

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo