Analyzing Data with Cross Tabulation in Microsoft Excel

hello hello hello and welcome to your continuing video lecture series all about analyzing your data using

Microsoft Excel in this video we are going to tackle comparing two variables against each other most of the time we

will be using cross tabulation in order to accomplish that cross tabulation is done using a

pivot table just like our descriptive statistics were done so let's do

it I'm going to go to my insert tab just like I've done before and create a new pivot table I'm going to put that on a

new worksheet and I'm going to set my range as again my whole table with all of my variables so I can

pull on any of them that I want okay and then I'm going to click okay and now I've got my new pivot

table in this case I want to analyze two variables are you a freshman and have you declared a major now this is

probably a not very interesting question the vast majority of freshmen should not have declared their major yet so this

should produce a pretty straightforward and strong relationship no problems there we'll use

that just for the demo for convenience sake now I want you to remember a little rule here

in order to create tables that are really easily interpreted we want to put our

independent variable in the columns and we want to put our dependent variable in the rows in this case that means we

think whether you're freshman or upper class that should be what predicts whether or not you have declared your

major that means that are you a fresh that question that should be our independent

variable have you declared a major becomes our dependent variable so that means that freshman needs to go into the

columns and major declaration needs to go into the rows so I'm going to drag are you a

freshman into the columns box in my pivot table fields and you can see now I have

columns and I'm going to drag have you declared a major into the rows and so now I have rows and

columns and then I'm going to drag have you declared a major again into values because I want the dependent

variable to be the numbers that we see okay and so here's what we get we get a sum so we got to fix that real quick so

a sum of have you declared a major no no no we want to count I'm going to switch that to count real quick

Bingo now we have the basic foundations of a cross tabulation but remember we usually want to compare percentages and

that's because the grand totals or well the totals of these uh of each column are different and so we can't say that a

one in column one means the same thing as a one in column 2 just as before we need to change these to

percentages so I'm going to collect one of these cells and right click and I'm going to go to show values as and we're

going to go to percent of column total just like we've done before and now we've got percentages and

we can see a really clear relationship so of individuals who scored a one on

freshman which we know is that they are a freshman 95% of them have picked

response option number two for have you declared a major and we can check our code book really quick and make sure

that oh yeah no I have not declared a major is two and so that makes perfect sense we'd

expect pretty much all of our freshmen not to do that and in fact I'm going to quickly label my columns so

that I remember what that means okay and I can do the same thing

with these rows two means not declared and one means declared right so now I can read across

and I can say that for freshmen only 5% of freshmen in our sample have declared their major the other 95% have not

declared their major on the flip side we can look at the upper class students and we can see that

86% have declared their major but another 133% have not okay and just like before we might want to add counts to

this cross tab so that we have counts and percentages and so we can just drag have you declared a major into values a

second time and then switch it to count just like we've done before sometimes when you do this it

really like spreads out the table and so you can drag The Columns back down and clean this up a little

bit I'm going to fix it all there we go um and and then we might want to do a little bit of extra labeling so these

column these little subheadings here uh for counts are really annoying and they're not even accurate because we

made it percentages so we can easily rename these so since this section is freshman we can just say percent

here and count here and you'll notice it actually updates um the row across the board because those things have to be

named the same thing and so now we have the basis of a pretty darn goodlook table we can continue to massage these

columns a little bit so they look extra [Music] nice there we go and if you wanted to

make it look even nicer you can actually get rid of these little arrow buttons we can use them to select who we want to

include so maybe we didn't want to include all categories we could subtract some if we want wanted but once we're

done with them one thing we can do is we can come up to the top here and click this pivot table analyze

Tab and we can toggle off this field headers button way off on the end and it will get rid of those ugly arrow keys

and now you have something that's actually whoops uh pretty darn presentable you could copy and paste

this right into a presentation of some kind okay um so that is the basics of creating a

cross tab the other sort of cross tab or the other sort of comparison you might be interested in if you have some sort

of numerical variable you might want to compare averages across multiple groups an easy way to do that is to do it with

a pivot table and I'll show you very quickly what that looks like so I'm going to go back to my original data

again I'm going to make a brand new pivot table again and again I'm going to collect I'm going to select all of that

previous variable and make another new pivot table and I'm going to say just as

before are you a freshman is going to be um is going to be how I want to divide it up so I want to see if are you a

freshman changes the average of our numerical variable we'll see um um and so I'm going to drag are you a freshman

into the rows but instead of dragging areu a freshman back again like we did with

descriptives I'm going to drag this approximately how much of your need-based financial aid this numerical

question into the values okay and again so this one actually automated to account which is

weird um what we want instead we're going to go to the value field settings like we have

in the past but instead we're going to select an average and click okay and so now we can

see the average difference between freshman and non-freshmen in terms of how much need-based financial aid what

percent of need-based financial aid uh covers their yearly tuition so these two options represent

different ways in which you can examine the relationship between two different variables now if you wanted to visualize

this you could then plug these percentages into something like a bar chart or a pie chart um Microsoft Excel

can actually build these charts for you but they tend to be a lot of work and no matter how much time you put into them

they never seem to look quite as nice as the things that you can get out of something like canva Microsoft

PowerPoint or Google slides and so my recommendation to you when it comes to visualization creating bar charts and

pie charts or any other visualization you might like is to take the percentages you get out of these cross

tabulations and then put them into your presentation software of choice open up a blank pie chart or a blank bar chart

and simply plug the percentages in in a way that makes the most sense to your visualization that is going to be much

easier than what we would normally do inside of my Microsoft Excel which tends to be fiddly and um and even once you've

cleaned it up it never looks quite as professional as some of these other options so I highly recommend that you

explore those options as you build your presentations