How to Analyse Qualitative Data from a Questionnaire in Excel

Consider an open-ended question in a company questionnaire.

It can not be answered Yes or No.

To analyse responses and determine the most significant theme:

STEP 1 – Create a Dataset to Record Responses and Analyse Themes

Create a dataset and record responses to analyse themes.
Insert columns with the headers ID, Your Post, What would improve your performance?, Theme 1- Pay Raise, Theme 2- Devices, Theme 3- Training, and Theme 4- Game Room.

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

STEP 2 – Record ID, Post, and Responses

Copy the ID, Post, and Response of each employee and paste it into the dataset.

This is the output.

Repeat the procedure to enter responses with IDs and Posts.

STEP 3 – Analyse Responses and Count Themes

Read the response and identify its theme.

For example, the first response states, ‘Workload has increased recently. I think a pay raise is really required.’ The employee is indicating Theme 1- Pay Raise.

Enter 1 in E5.

In the second response, there are two themes; Pay Raise and Devices.
The theme in the third response is Game Room.
Enter 1 in each case.

Follow the same steps to count the number of themes of each response.

STEP 4 – Insert a Pivot Table

Select a cell in the dataset.
Go to the Insert tab and click Pivot Table.

In the dialog box, click OK .

A new window will be displayed with the PivotTable Fields:

STEP 5 – Calculate the Sum of Each Theme

Select Theme 1, Theme 2, Theme 3, and Theme 4 in ‘Choose fields to add to report’.
Drag ‘Values’ to Rows.
Drag Sum of Theme 1, Sum of Theme 2, Sum of Theme 3, and Sum of Theme 4 to Values.