Skip main navigation

New offer! Get 30% off your first 2 months of Unlimited Monthly. Start your subscription for just £35.99 £24.99. New subscribers only T&Cs apply

Find out more

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

Reach your personal and professional goals

Unlock access to hundreds of expert online courses and degrees from top universities and educators to gain accredited qualifications and professional CV-building certificates.

Join over 18 million learners to launch, switch or build upon your career, all at your own pace, across a wide range of topic areas.

Start Learning now