How to Convert Qualitative Data to Quantitative Data in Excel

This guide will explain how to convert qualitative data to quantitative data in Excel.

Converting qualitative data to quantitative data can be helpful when trying to analyze and compare data sets. 

What is the difference between qualitative and quantitative data?

Qualitative data cannot be measured numerically and is instead categorized. Quantitative data is data that can be measured numerically.

Let’s take a look at a simple example where we can benefit from converting qualitative data to quantitative data.

Suppose you have a list of survey responses and want to analyze the data. The survey responses might be categorized as “positive”, “negative”, or “neutral”. 

By converting the qualitative data to quantitative data, you could change the responses to 1 for positive, 2 for negative, and 3 for neutral. This would make the data easier to analyze. 

In the example provided, converting qualitative data to quantitative data makes it easier to analyze the survey responses. We can also perform this conversion using a few methods in Excel, including the Find and Replace tool.

Now that we have a grasp on when to convert qualitative data to quantitative data, let’s see how this works on an actual sample spreadsheet.

 

 

A Real Example of Converting Qualitative Data to Quantitative Data in Excel

The following section provides several examples of how to convert qualitative data to quantitative data in Excel. We will also go into detail about the formulas and tools used in these examples.

First, let’s take a look at a sample spreadsheet with qualitative data.

The spreadsheet below contains a dataset of survey responses. The survey asked for some personal data from the respondent and asked them three questions. Each question could be answered with either a ‘Yes’, ‘No’, or ‘I Don’t Know’.

qualitative data in sheet

 

Columns E, F, and G contain qualitative data. We can use the Find and Replace tool to convert these values into quantitative data instead.

use Find and Replace tool to convert qualitative data to quantitative data in Excel

 

Suppose we have another column with multiple unique responses to a question. Is there an easier way to convert all these values into numerical data?

qualitative data to quantitative data in Excel

 

Instead of replacing each value manually, we can use a lookup table instead. For example, the table below lists all unique values found in column H. From there, the user can assign a quantitative value either manually or programmatically.

lookup tables to convert qualitative data to quantitative data in Excel

 

To get the unique values in column H, we can use the following formula:

=UNIQUE(H2:H15)

Do you want to take a closer look at our examples? You can also make your own copy of the spreadsheet above using the link attached below. 

If you’re ready to try out converting qualitative data to quantitative data, head over to the next section to read our step-by-step breakdown on how to do it!

 

 

How to Convert Qualitative Data to Quantitative Data in Excel

This section will guide you through each step needed to convert qualitative data to quantitative data in Excel. You’ll learn how to use the Find and Replace tool to convert qualitative values such as “Yes” and “No” to numerical data. We will also explain how to create a lookup table to set up a mapping between qualitative and quantitative values.

Follow these steps to start converting qualitative data to quantitative data:

  1. Type the keyboard shortcut Ctrl + H to access the Find and Replace tool.
    use Find and Replace tool
  2. Next, indicate the value you want to replace and the value to replace it with. In this example, we’ll convert all instances of ‘Yes’ to ‘1’. Click on the Replace All button to replace all instances of Yes. If the keyword appears elsewhere in the spreadsheet, you should also select a specific range first before using the Find and Replace tool.
    convert "Yes" to "1"
  3. Excel will return a dialog prompt indicating how many replacements were made. Click OK to return to your spreadsheet.
    Excel will indicate how many replacements were made
  4. Use the Find and Replace tool to replace all other qualitative values in your spreadsheet.
    qualitative data to quantitative data in Excel
  5. Your spreadsheet should now have columns with quantitative data rather than qualitative data.
    qualitative data to quantitative data in Excel

If a particular column contains several unique values, it might be tedious to replace each value manually. We can create a lookup table instead to create a second column with the corresponding qualitative values.

  1. First, we must find all unique values in the column. We can use the UNIQUE function for this purpose. In this example, we’ll output the unique values found in column H.
    get unique values
  2. Create another column to the right. Add the equivalent quantitative data for each value. In the example below, we’ve assigned each unique color a specific number between 0 and 6.
    create lookup table
  3. Lastly, we can use the VLOOKUP function to return the quantitative value for each value in our original column.
    convert qualitative data to quantitative data in Excel

 

These are all the steps you need to convert qualitative data to quantitative data in Excel. 

 

 

Our overall guide should provide you with all the information you need to convert qualitative data to quantitative data. 

We’ve also shown you how easy it is to use the Find and Replace feature and lookup tables to perform this task. 

Converting qualitative data to quantitative data is much easier when using Excel features. Our website offers hundreds of other functions and methods to help you get more out of Microsoft Excel.

With so many other Excel functions available, you can find one appropriate for your use case.

Don’t miss out on our team’s new spreadsheet tips, tricks, and best practices. Subscribe to our newsletter to stay updated on the latest guides from us!

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like