Rafiul Haq
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.
Download Practice Workbook
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.
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.
- 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.
Read More: How to Use Analyze Data in Excel (5 Easy Methods)
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.
Read More: [Fixed:] Data Analysis Not Showing in Excel (2 Effective Solutions)
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.
- Afterward, a dialog box will appear. Select these options:
- Variable 1 Range – D14:D16.
- Variable 2 Range – D17: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.
- 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”.
- 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.
- 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.
- 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)
- 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
- 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)
- 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 Analyze Large Data Sets in Excel (6 Effective Methods)
Similar Readings
- How to Analyze Sales Data in Excel (10 Easy Ways)
- Analyze Data in Excel Using Pivot Tables (9 Suitable Examples)
- How to Analyze Time-Scaled Data in Excel (With Easy Steps)
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.
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.
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.
- 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.
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. Moreover, you can visit our site ExcelDemy for more Excel-related articles. Thanks for reading, keep excelling!
