Analyzing Categorical data with Pivot tables

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]