Skip main navigation

Analyzing Categorical data with Pivot tables

Pivot tables can be used to analyze categorical data just as well as it can be used to analyze numerical data. Watch now as Paula demonstrates.
We have some categorical data in a data set here. So with no numerical data at all. And we want to be able to visualize the demographics of this data. So what we have is we have some job roles, we have salary, and then we have whether the person upgraded or not. And we want to see the breakdown of the upgrade, what demographics influence the actual upgrade. So as I mentioned, this is categorical data. Now, the job role would be nominal data because it has no sense of order. Whereas, the salary would be ordinal data because it has, because it kind of had, it kind of has a rank between high, low, and medium.
So we’re going to insert a pivot table, because a pivot table is a great way to analyze categorical data in Excel. So let’s go insert and we will select pivot table. Now, I’m going to put this pivot table on the existing worksheet but you need to make sure that it’s selected the right data source first. Once you’ve selected where you want it to go, we are then given a pivot table options to analyze the data. So I’m going to take upgrade because it’s upgrade that we want to look at. So we’ve got yes, and we’ve got no.
Now I’m going to drop into the values the count of the upgrade, we’ve had 24 people in the survey and 13 of them upgraded and 11 of them didn’t. But, what demographics actually influence. this. Well, if we take our job role and we stick our job role in here, we can now see that Accountants equally upgraded to didn’t upgrade. Bookkeepers tended to upgrade more. CFOs tended to upgrade less. And Trainee Accountants seem to have upgraded more. So the lower the role type seem to influence whether the person upgraded or not.
Now, we are looking at the count there and we had to kind of work out, we had to decipher which one was greater than which. But, what we can do is we can change the value field settings. So I right click on the chart there and I’m going to select value field settings. And in here, select show value as, and I’m going to select show value as of row total. And then I’m going to select, okay. Now, we can quickly see that 50 percent of Accountant said yes to no, 66 to 33 of Bookkeepers, 83 percent of CFO said no, and 88 or 83 percent Trainee Accountant said yes. More people said yes than said no.
Now we can add another metric in here too. We drop in our salary. We can see if the salaries has had an impact on this as well. Now, we can see all CFOs seem to be on a high salary. The Trainee Accountants that are on a low salary, have adopted a little bit less than those on a medium salary. The same with the Bookkeepers, they are actually with the Bookkeepers, it’s higher. On Accountants it is equal. So that’s how you can use pivot tables to quickly analyze categorical data that you may have in Excel. [END]

In the examples we have looked at so far, we have used numerical data in our values fields. But we can also use categorical data (or text fields) The default aggregation for text fields is count. Make sure you understand the concepts in this video before you move on.

Comment below:

What are your main takeaways from this video?

This article is from the free online

How to Use Pivot Tables to Analyse Data in Excel

Created by
FutureLearn - Learning For Life

Our purpose is to transform access to education.

We offer a diverse selection of courses from leading universities and cultural institutions from around the world. These are delivered one step at a time, and are accessible on mobile, tablet and desktop, so you can fit learning around your life.

We believe learning should be an enjoyable, social experience, so our courses offer the opportunity to discuss what you’re learning with others as you go, helping you make fresh discoveries and form new ideas.
You can unlock new opportunities with unlimited access to hundreds of online short courses for a year by subscribing to our Unlimited package. Build your knowledge with top universities and organisations.

Learn more about how FutureLearn is transforming access to education