Converting Qualitative Data Into Quantitative Data

In the previous blogs in this series, I introduced the role data plays in decision-making (Part 1), the importance of having a data strategy (Part 2), and how to use multiple data sources in that data strategy (Part 3).

As I stated earlier, two types of data exist: quantitative and qualitative data. Quantitative data’s major advantage is that it can be easily analyzed using mathematical formulas. Quantitative data enables us to use logic and apply mathematical formulas to segment and compare the data as well as charts to assist in visualizing the findings. Quantitative data falls more to the scientific right on the decision-making spectrum introduced in Part 1 of this blog series.

Many surveys use a five-point Likert scale. This scale asks respondents to rate a statement by selecting one of the following:

  • Strongly Agree
  • Agree
  • Neither Agree nor Disagree
  • Disagree
  • Strongly Agree

Questions or statements that use this type of scale are quantifiable because a value is associated with each individual answer. This format also makes it easy to compare and contrast responses against each other.

Qualitative data, such as free form text responses or interview verbatim portions of a survey, are in contrast anecdotal in nature and need to be treated differently. These qualitative answers can be very powerful because they have a direct line of sight to the target audience’s thoughts, feelings, and emotions. But these text answers create a problem for many. Some companies find it difficult to digest large data sets derived from survey text questions or interview comments and quotes collected over a long period of time.

I experienced this problem during a recent project. This post focuses on how I converted qualitative data into quantitative data. I assembled the data into a spreadsheet and used a technique called quantitative data coding. My next post, “Part 5: Making Qualitative Data Count,” will describe how this code process allowed the data to tell its story in a meaningful way. Below are the steps I followed.

Step 1.

Paste all verbatim responses into the first column in a spreadsheet (Figure 1).

Figure 1 Rocky

Figure 1

Step 2.

Create a list of top categories of information the survey or interview targeted across the top (Figure 2).

Figure 2 Rocky

Figure 2

Step 3.

Read each response and determine if there is a sub-category (Figure 3).

Figure 3 Rocky

Figure 3

Step 4.

“Code” each comment (Figure 4). Essentially, this step links each verbatim comment with one or more sub-categories. To do this, read each comment and place an “x” in the sub-categories that best represent the verbatim. It is possible to have multiple sub-categories annotated for a single comment. If no sub-categories accurately reflect a comment, add a new sub-category. As a rule, try to limit the total number of sub-categories to 15 or less. Having more than that will increase the degree of difficulty and time it takes to code the entire data set.

Figure 4 Rocky

Figure 4

Step 5.

Add up each column (Figure 5). Excel can be very helpful in auto-calculating each column. To count the number of “x’s” in column C, begin by inserting a row just under the headers. Next, place the following formula in cell C2.


Note that in this formula, C18 represents the last row with data. If you paste a lot of data into this sheet and the last row of data is below C18, right click on the C18 in the formula (to change that element), and then click on the cell in column “C” of the last row with data. Press Enter. Copy cell C2 and paste into all sub-category columns in row 2.

Copying this formula to the other cells will provide you a view of the total mentions for each sub-category. This process helps you clearly identify the top issues identified in the survey.

Figure 5 rocky

Figure 5

If you think you may present to your leadership team, they may not be interested in this level of detail. Rather, they will likely be interested in a summary by category (versus sub-category).

I find it helpful to code the sub-category first, and then let Excel calculate the major category totals. Having both levels of detail provides the flexibility to have a high-level summary discussion and a detailed discussion, depending on the needs of your audience.

Step 6.

In this step we will summarize the data. It is important that the major category is only counted once for each relevant verbatim (row) (Figure 6).

Figure 6 Rocky

Figure 6

In this case, I entered a one (1) in the category column for each time an “x” appeared in any of its related sub-categories. You can see that in cell B3, a one (1) was entered even though that row has an “x” in two sub-categories (one in “Investing in Employee” and the other in “Communication”).

This process keeps us from “weighting” one category too heavily in comparison to other categories. If you were to add up each “x” in the MANAGEMENT SUPPORT sub-categories (1+5+3+1+2) in cell C2, you would get a total of 12. This value reflects the sum of all relevant mentions for all MANAGEMENT SUPPORT sub-categories. However, we see that only seven verbatim rows address some kind of MANAGEMENT SUPPORT type issues. Again, this is necessary to prevent “double counting.”

Because I used numbers in the major categories (rather than an “x” as I did in the sub-categories), I changed my Excel formula slightly.


WARNING: Be sure to only enter a “1” in the category summary column for each relevant verbatim (row).

Tell me how you analyze your qualitative data. Also look for Part 5: Making Qualitative Data Count to learn how I created a graphic that unlocked the data secrets for the business leadership team.

Rocky Ellens

Rocky Ellens is a GP Strategies Sales Enablement Practice leader, helping drive client’s business-to-business sales team performance. Rocky has over 15 years of experience providing innovative thought leadership and performance consulting across Fortune 100 clients in Manufacturing, Retail, Finance, Food and Beverage, and IT market segments. He holds an MS in Human Resources and Organizational Development. He is a retired Army Colonel experienced at providing a foundation of leadership training and teaming designed to pull work groups together in pursuit of a common objective or goal.

Latest posts by Rocky Ellens (see all)

3 comments on “Converting Qualitative Data Into Quantitative Data
  1. LABAN says:


  2. Sonja Hultsman says:

    So far very helpful! Once you complete the quantitative data from the information, are you utilizing the worksheet into other graphs?

    • Harold Ellens says:

      Yes. I consider the story I want to tell from the data, the audience visualization needs, and the decision this data informs. I land on the graph or other visualization approach that best supports each of these considerations.

      I’m glad you found this information helpful!

Leave a Reply

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


This site uses Akismet to reduce spam. Learn how your comment data is processed.

Sort by Category

GPS Want to Learn More Widget

Want to Learn More?

GPS Connect With Us Widget