In this series, we’re showing you how to analyze qualitative survey data without coding. In a previous article, we showed you how to manually code survey responses by topic, and then we showed you an alternative method using Unigrams. In this article, we’ll be working with that data and showing you how to segment your data in Excel. Segmentation is a useful data analysis technique and is one way that qualitative data can be quantified. We will also show you how this qualitative data can be graphically represented using the basic tools in Excel.
This article covers two methods. The first is a simple method that uses a pivot table to summarize the data. This method works on both PC and Mac versions of Excel. The second method uses the Power Query function available on pc versions of Excel. This tutorial assumes that you’ve already sorted your data into relevant topics, using either of the two methods from the last article.
In the examples below, I'll be using a dataset of student course reviews, and segmenting by the concentration of each course. You could also segment by product line or associated demographic data.
Segmenting Data with a Pivot Table
Pivot tables are a staple of data analysis in Excel, and those who use Excel often will undoubtedly be familiar with them. If this is the case, feel free to skip to the next section.
If you’ve stayed, your first step will be to format your dataset as a Table, under “Insert” and “Table”. Once this is done, highlight your entire table. This can be done by double-clicking on the top left corner, or by selecting a cell in the table and hitting control + a. Now that you’ve highlighted your entire dataset, choose “Pivot table” under “Insert”. Your pivot table should open in a new tab of your workbook. Now drag the field you’d like to segment by to “Rows” and your topics to “Values”. It should look something like this. In this case, I'm segmenting by the type of course the students took.
Now select your pivot table and insert a bar chart. You can sort your data using the “Sort & Filter” function in the top right corner.
I’m going to sort from largest to smallest, to make the bar chart a bit more readable. Now you can see that my data is segmented by the concentration area (type) each course belongs to and is ordered from the most mentioned topics to the least mentioned. If I wanted to focus on one particular concentration, I could click the filter button beside “Row labels” and select the concentration I want to focus on.
This method of segmentation is nice because it’s very simple and is one of your only options on the Mac version of Excel. However, if you’re using a PC and would like to get a better idea of how each topic ranks, you can use the Power Query functions to transform your dataset.
Segmenting Data with Power Query
Power Query is Excel’s built-in data transformation and preparation engine. It’s a handy tool for connecting and reshaping datasets before performing analysis. In this section, we’re going to use the transformation tools on our dataset, so that we can present our data more intuitively.
The first step is to make sure your data is in a table. If you’re unsure of how to do this, check the section above. Once that’s done, highlight your table and go to the "Data" tab. Here, you’ll see the “Get Data” function. Choose “From a Table/Range”
This will open us the Power Query Editor. Go and select all the columns with your topics, just like the image below. Then head to “Transform” to hit the “Unpivot” button.
Unpivot will transform your data so that you have two new columns: attributes and values. Attributes will be the title of each topic. Values will be a 1 or 0, depending on if the comment is matched to that topic. You’ll want to go to the filter button and de-select the 0’s to filter out null values. Once you’ve done that, go ahead and remove the values column. You can now close the Power Query Editor, choosing “Keep” if prompted.
From here this is very similar to the section above. Select your table and insert a pivot table. Place the field you’d like to segment by under “filters”, the new attribute column under “rows” and “values”. I find it helps to show values as a percentage of the grand total and sort them from largest to smallest.
Now you’re ready to present your data. Insert a bar chart, and maybe a slicer so you can quickly switch between different segments. Your final product should look something like this:
Now you have two methods you can use to segment your qualitative data. By segmenting your qualitative data, you can explore the different ways stakeholders feel about a topic. In the example used here, we were able to see how students felt about their writing courses. Alternatively, this technique could be used to see how customers felt about different products or the differences in how male and female employees view inclusion initiatives at work.
If qualitative data is something that interests you, consider signing up for our newsletter at the bottom of this page! It’s a monthly bulletin where we discuss important topics in the industry and keep you up to date with what’s happening at Kai Analytics.
If you want to learn more about what Kai Analytics can do for you, schedule a free consultation. In 30 minutes we can discuss your needs and see what we can do to help.