How to Analyze Qualitative Data in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

Looking for ways to know how to analyze qualitative data in Excel? Then this is the right article for you. When data cannot be counted and is hard to explain using numerical values, then the data is qualitative. We can gather this qualitative data from focus group discussions, in-depth interviews, sentence completions, word associations, casual conversations, etc.


8 Steps to Analyze Qualitative Data in Excel

We have taken three answers from a survey questionnaire to demonstrate our approaches. Here, XYZ is a cafe at the end of a town and students sometimes hang out there. The three questions are as follows:

  • Firstly, a Likert scale question “I am satisfied with XYZ”. There are 5 levels to answer this question.
  • Next, a multiple-choice question “How often I Eat in XYZ in Days”. Participants can choose one of 3 options.
  • Lastly, an open-ended question: “Which food should be included in XYZ”. Any length of text was acceptable here.

Generally, our dataset has 3 columns: “ID”, “Gender”, and “Questions & Responses”. Moreover, in the snapshot below, the three questions are shown in a compact structure. We will discuss how to gain information from each type.

How to Analyze Qualitative Data in Excel Intro


Step 1: Code and Sort Qualitative Data to Analyze in Excel

We will transform the qualitative data into numerical values using codes. Then, we will sort the data to prepare for the next step. Our Likert Scale has 5 levels, therefore the values will be like this:

  • Strongly Agree -> 5.
  • Agree -> 4.
  • Neutral -> 3.
  • Disagree -> 2.
  • Strongly Disagree -> 1.
  • So, we use this to input values in the cell range E6:E11.

  • Then, we separate the “Gender” and “Coding” columns into different cell ranges.

How to Analyze Qualitative Data in Excel 3

  • Afterward, select the cell range C14:D19 and right-click to bring up the Context Menu.
  • Next, from Sort >>> select “Sort A to Z”.

  • Therefore, our values for the same gender will be together.

How to Analyze Qualitative Data in Excel 5

Read More: How to Analyze Quantitative Data in Excel 


Step 2: Enable Analysis Toolpak

We need to enable the Data Analysis feature in Excel before doing any statistical tests.

  • To begin with, press ALT, F, then T to bring up the Excel Options window.
  • Then, from Add-ins >>> select “Go…”.

  • So, the Add-ins dialog box will pop up.
  • Afterward, select “Analysis Toolpak” and press OK.
  • Lastly, we will see the Data Analysis command inside the Data tab.

How to Analyze Qualitative Data in Excel Analysis Toolpak

Read More: How to Analyse Qualitative Data from a Questionnaire in Excel


Step 3: T-test to Compare Means with Qualitative Data

We will be using the “Two-Sample t-test”, which is also known as the “Independent Sample t-test” to analyze qualitative data. We have two hypotheses or assumptions:

Null Hypothesis H0: “Two groups are equally satisfied with XYZ”.

Alternative Hypothesis Ha: “Two groups are not equally satisfied with XYZ”.

If we find our p-value lower than 0.05 then we will fail to reject the null hypothesis. Else, we will reject the null hypothesis.

  • In the last step, we enabled the Analysis Toolpak. This will appear under the Analysis section.
  • Then, click on “Data Analysis”.

  • Next, select “t-test: Two-Sample Assuming Unequal Variances” and press OK.

How to Analyze Qualitative Data in Excel T-Test

  • Afterward, a dialog box will appear. Select these options:
    • Variable 1 RangeD14:D16.
    • Variable 2 RangeD17:D19.
    • We can swap this too, the output will be the same regardless.
  • After that, select “Output Range” and cell C21 as the output location.
  • Then, press OK.

  • So, the output will be like this.
  • Next, we can see the mean is 3 and 4.33. We will check if this difference is significant or not using the p-value. Additionally, the variances are 1 and 0.33, so our assumption of unequal variances was correct. If this value is almost identical then you will need to change it to “t-test: Two-Sample Assuming Equal Variances”.
  • Therefore, we need to concentrate on the P(T<=t) two-tail value only. This needs to be less than 0.05 to be significant. As it is (0.14 if we round up) more than 0.05, hence, we reject the null hypothesis.

How to Analyze Qualitative Data in Excel T-Test Output

  • So, from the analysis, we can say that males and females have different levels of satisfaction with cafe XYZ, which is statistically significant.

Step 4: Prepare Categorical Dataset for Chi-Square Test

We will use the SUM and COUNTIFS functions in this step. Now, we will discuss analyzing the second question. We use the Chi-Square test to find out the relationship between two categorical data. Additionally, it can return a difference between expected and observed values. We want to find out if there is any relationship between gender and the number of times of eating in XYZ cafe.

Null Hypothesis H0: “There is no relation between gender and eating per week at XYZ”.

Alternative Hypothesis Ha: “There is relation between gender and eating per week at XYZ”.

How to Analyze Qualitative Data in Excel Chi-Square Test

  • Firstly, we will name the ranges C6:C11 as “Gender” and D6:D11 as “Times”.

  • Next, we will create a template to calculate the Chi-Squared value.

How to Analyze Qualitative Data in Excel Chi-Square 2

  • Then, we will select the cell range C7:E7 and type the following formula.

=COUNTIFS(Gender,$B$7,Times,C6)

This formula finds the number of cells with males and One time eating per week in cafe XYZ.

  • Next, press CTRL+ENTER. This will AutoFill the formula.
  • After that, we will select the cell range C8:E8 and type the following formula.

=COUNTIFS(Gender,$B$8,Times,C6)

This formula finds the number of cells containing the females and One time eating per week in cafe XYZ.

How to Analyze Qualitative Data in Excel Chi Square 3

  • Then, press CTRL+ENTER.
  • Afterward, we will sum the rows and columns.
  • Select the cell range C9:E9 and type this formula.

=SUM(C7:C8)

  • Press CTRL+ENTER.
  • Then, Select the cell range F7:F8 and type this formula.

=SUM(C7:E7)

How to Analyze Qualitative Data in Excel Chi Square 4

  • Press CTRL+ENTER.
  • Then, we will type 6 in cell F9 as the number of respondents was 6.
  • Now, we will find the expected values. To find it, the formula is Row Total * Column Total/Total.
  • After that, type this formula in the cell range I7:K7 by selecting it beforehand.

=$F$7*C9/$F$9

  • Press CTRL+ENTER.
  • Then, select the cell range I8:K8 and type this formula.

=$F$8*C9/$F$9

How to Analyze Qualitative Data in Excel Chi Square 5

  • After that, press CTRL+ENTER.
  • Now, we will find the Chi-Squared value.
  • So, select the cell range C13:E14 and type the following formula.

=(C7-I7)^2/I7

  • After that, press CTRL+ENTER.
  • Then, we will add these values in cell I12 by typing this formula.

=SUM(C13:E14)

How to Analyze Qualitative Data in Excel Chi Square 6

  • Afterward, press ENTER.
  • Now, df means degrees of freedom. The formula to find it is to use (Number of Columns -1) * (Number of Rows-1). We have 2 rows and 3 columns. Therefore, our df will be (3-1)*(2-1) = 2.

Read More: How to Convert Qualitative Data to Quantitative Data in Excel


Step 5: Analyze Categorical Qualitative Data in Excel with Chi-Square Test

We will use the CHISQ.DIST.RT to find the p-value for this test.

  • So, type this formula in cell I14.

=CHISQ.DIST.RT(I12,I13)

This function returns “the right-tailed probability of the chi-squared distribution”.

  • After that, press ENTER. We will get the value of 0.2 which is larger than 0.05. So, we will fail to reject the null hypothesis. In simpler terms, we can say that the two categories have no relationship.

How to Analyze Qualitative Data in Excel 25

Step 6: Sentiment Analysis for Open-Ended Qualitative Data

Now, we will look into our last question and responses. We will use the manual process of finding the themes of the responses. We have added two columns to the dataset: “Topic1” and “Topic2”.

  • Then, we will read the responses and attach food topics to them. For example, “Chicken Pizza” has 2 topics: “Chicken” and “Pizza” and so on.
  • After that, we added the unique topics only to a new table.

How to Analyze Qualitative Data in Excel 27

Step 7: Use COUNTIF Function to Analyze Open-Ended Qualitative Data

We will use the COUNTIF function to gain insights into the frequency distribution.

  • First, select the cell range D13:D17 and type the following formula.

=COUNTIF($E$5:$F$10,C13)

This formula counts the number of values in the range F5:F10 that have a value from cell C13.

  • Afterward, to AutoFill the formula, press CTRL+ENTER.
  • Next, we will insert a Chart to visualize the frequency distribution.

Step 8: Clustered Column Chart to Visualize Open-Ended Qualitative Data

In this step, we will create a Clustered Column Chart to understand the qualitative data more clearly.

  • So, select the cell range C12:D17 and from the Insert tab, select Recommended Charts.

How to Analyze Qualitative Data in Excel 29

  • Then, the Insert Chart dialog box will appear and Clustered Column will be selected by default. If not then select it.
  • After that, press OK.

  • Then, we can see that the customers want Chicken, Pasta, and Pizza as the top 3 food items in XYZ cafe. The management of XYZ cafe can choose to offer more of these products to generate more revenue.

How to Analyze Qualitative Data in Excel Frequency Distribution

Read More: How to Analyze Raw Data in Excel


Summary

  • We use the t-test when we compare means between two groups and the chi-square test when we work with categorical values.
  • For our dataset, we get these results from the three questions in our survey questionnaire-
    • The males and females have different levels of satisfaction with cafe XYZ.
    • Gender and the number of eating in cafe XYZ is not related.
    • Students or customers want Chicken, Pasta, and Pizza as the top three items to include in Cafe XYZ.

Practice Section

We have added a practice dataset for each method in the Excel file. Therefore, you can follow along with our methods easily.


Conclusion

We have shown you 8 steps to analyze qualitative data in Excel. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Thanks for reading, keep excelling!


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.
Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo