How to Analyse Qualitative Data from a Questionnaire in Excel

In this article, we will learn to analyse qualitative data from a questionnaire in Excel. Often, Excel users need to analyse qualitative data from a questionnaire. A questionnaire contains a list of questions. To analyse qualitative data, we need to follow some specific steps. Today, we will demonstrate these easy steps and show you how you can analyse qualitative data in Excel. So, without any delay, let’s start the discussion.

Step-by-Step Procedures to Analyse Qualitative Data from a Questionnaire in Excel

To explain the step-by-step procedures, we will focus on an open-ended question from a company questionnaire. If you have multiple questions, then you will have to apply the method individually for each question.

An open-ended question is a question that can not be answered in Yes or No. For example, a company has asked its employees ‘What would improve your performance?’ in a questionnaire. The employees will answer this question and their responses will be recorded. After that, we will analyse their responses and determine which theme is the most significant. The theme basically denotes the main point of responses. So, without further ado, let’s follow the steps now.

STEP 1: Create Dataset to Record Responses and Analyse Themes

In the first place, we need to create a dataset and record responses to analyse themes.
For that purpose, create columns with headers ID, Your Post, What would improve your performance?, Theme 1- Pay Raise, Theme 2- Devices, Theme 3- Training, and Theme 4- Game Room.
You can add more themes according to your needs.
But in our case, these themes are commonly found in qualitative data.

STEP 2: Record ID, Post, and Responses

Secondly, we need to record the ID, Post, and Response of each employee to the question.
In order to do that, you can copy the ID, Post, and Response first.
Then, paste it into the dataset. Or you can type manually.
In the picture below, we have recorded the first response.

You can repeat the same procedure to record all responses with IDs and Posts.

STEP 3: Analyse Responses and Count Themes

In the third step, we need to analyse each response and count the number of themes in that response. This is the most important step of this process.
To analyse a response, you need to read the response carefully and find out which theme it indicates.
For example, the first response states, ‘Workload has increased recently. I think a pay raise is really required.’
From this response, we can say, the employee is indicating Theme 1- Pay Raise.
We have found 1 theme in the first response. So, we have typed 1 in Cell E5.

In the second response, we can find two themes; Pay Raise and Devices.
Similarly, we can see one theme in the third response and that is Game Room.
We can find each theme a single time in the second and third responses. So, we have typed 1 in those cases.

After that, you can follow the same step to count the number of themes of each response.

STEP 4: Insert Pivot Table

In the following step, we need to insert a Pivot Table using the dataset.
To do so, select a cell in the dataset.
After that, go to the Insert tab and click on the PivotTable icon.

Instantly, a dialog box will appear.
Click OK to proceed.

As a result, a new sheet will appear with the PivotTable Fields on the right of the excel sheet.

STEP 5: Determine the Sum of Each Theme

In the fifth step, we need to determine the sum of each theme. It means how many times a theme occurred in the responses.
For that purpose, select Theme 1, Theme 2, Theme 3, and Theme 4 in the ‘Choose fields to add to report’.
You need to make sure that the ‘Values’ is in the Rows box.
Also, ensure the Sum of Theme 1, Sum of Theme 2, Sum of Theme 3, and Sum of Theme 4 are in the Values box.

After completing the previous, you will see the sum of each theme in the Pivot Table.

STEP 6: Find Total Number of Themes

To determine the total number of themes, you need to copy the Pivot Table first and then, paste it anywhere on the sheet.

After that, select Cell B16 and type the formula below:

=SUM(B11:B14)

Here, we have used the SUM function to calculate the total number of themes in the responses. Remember, these are not the number of responses. From the dataset, you can see, that there were 11 responses.

STEP 7: Find the Most Significant Theme Using Percentage

Finally, you need to find the most significant theme using percentages.
In order to do that, add another column. We have named it ‘Percentage’.
After that, select Cell C11 and type the formula below:

=B11/$B$16

Now, hit Enter to see the result.

In the next step, drag the Fill Handle down to copy the formula in the range C12:C14.

As a result, you will see results like the picture below.

At this moment, select Cell C11 to C14.
Navigate to the Home tab and select Percentage in the Number section.

Final Result

After completing all the steps, you will find the analysis of the qualitative data.
From the findings, we can say that Theme 1- Pay Raise is the most significant theme to improve performance.

Read More: How to Analyze Raw Data in Excel

Download Practice Book

You can download the workbook from here to practice.

Analyse Qualitative Data from a Questionnaire.xlsx

Conclusion

In this article, we have demonstrated step-by-step procedures to Analyse Qualitative Data from a Questionnaire in Excel. I hope this article will help you to perform your tasks easily. Moreover, we have also added the practice book at the beginning of the article. Furthermore, you can download it to test your skills. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.

<< Go Back to Data Analysis with Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!