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.

**Table of Contents**hide

## 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
and right-click to bring up the*C14:D19*.*Context Menu* - Next, from
>>> select â€ś*Sort*â€ť.*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*, then*F*to bring up the*T*window.*Excel Options* - Then, from
>>> select â€ś*Add-ins*â€ť.*Goâ€¦*

- So, the
will pop up.*Add-ins dialog box* - Afterward, select â€ś
â€ť and press*Analysis Toolpak*.*OK* - Lastly, we will see the
command inside the*Data Analysis*tab.*Data*

**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 â€ś

**â€ť to analyze**

*Independent Sample t-test***qualitative data**. We have two hypotheses or assumptions:

**Null Hypothesis H**_{0}**:** â€śTwo groups are equally satisfied with** XYZ**â€ť.

**Alternative Hypothesis H**_{a}**:** â€śTwo groups are not equally satisfied with** XYZ**â€ť.

If we find our** p-value** lower than

**0.05**then we will

**. Else, we will**

*fail to reject the null hypothesis***.**

*reject the null hypothesis*- In the last step, we enabled the
. This will appear under the*Analysis Toolpak*section.*Analysis* - Then, click on â€ś
â€ť.*Data Analysis*

- Next, select â€ś
â€ť and press*t-test: Two-Sample Assuming Unequal Variances*.*OK*

- Afterward, a
will appear. Select these options:*dialog box*â€“*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 â€ś
â€ť and cell*Output Range*as the output location.*C21* - Then, press
.*OK*

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

**functions in this step. Now, we will discuss analyzing the second question. We use the**

*COUNTIFS*

*Chi-Square***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**

*test***XYZ cafe**.

**Null Hypothesis H**_{0}**:** â€śThere is no relation between gender and eating per week at **XYZ**â€ť.

**Alternative Hypothesis H**_{a}**:** â€śThere is relation between gender and eating per week at **XYZ**â€ť.

- Firstly, we will name the ranges
as â€ś*C6:C11*â€ť and*Gender*as â€ś*D6:D11*â€ť.*Times*

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

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

`=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
. This will*CTRL+ENTER*.*AutoFill the formula* - After that, we will select the cell range
and type the following formula.*C8:E8*

`=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
and type this formula.*C9:E9*

`=SUM(C7:C8)`

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

`=SUM(C7:E7)`

- Press
.*CTRL+ENTER* - Then, we will type
in cell*6*as the number of respondents was*F9*.*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
by selecting it beforehand.*I7:K7*

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

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

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

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

`=(C7-I7)^2/I7`

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

`=SUM(C13:E14)`

- Afterward, press
.*ENTER* - Now,
means*df*. The formula to find it is to use*degrees of freedom*. We have*(Number of Columns -1) * (Number of Rows-1)*rows and*2*columns. Therefore, our*3***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

**for this test.**

*p-value*- 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
. We will get the value of*ENTER*which is larger than*0.2*. So, we will fail to*0.05*. In simpler terms, we can say that the two categories have no relationship.*reject the null hypothesis*

### 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**â€ť hastopics: â€ś*2***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
and type the following formula.*D13:D17*

`=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
the formula, press*AutoFill*.*CTRL+ENTER* - Next, we will insert a
to visualize the frequency distribution.*Chart*

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

In this step, we will create a ** Clustered Column Chart** to understand the

**more clearly.**

*qualitative data*- So, select the cell range
and from the Insert tab, select*C12:D17*.*Recommended Charts*

- Then, the
will appear and*Insert Chart dialog box*will be selected by default. If not then select it.*Clustered Column* - 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
when we*t-test***compare means**between two groups and thewhen we work with*chi-square test***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**.

- The males and females have different levels of satisfaction with

## 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!