Mastering Descriptive Statistics in Excel: A Step-by-Step Guide

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

Excel in this video I'm going to cover descriptives which is how you get information about single

variables now we've talked about multiple different types of variables but I'm just going to focus on a single

Choice option here like our are we a freshman question in order to do descriptives the

simplest and easiest way is to use what's called a pivot table in Microsoft Excel we get a pivot table by clicking

the insert Tab and selecting this very first option pivot table it's going to give you a little

bit of popup and there's two things we need to do with it the first is to select the range or the data that we

want the pivot table to be able to pull from so in this case we want our survey questions

when you have table range selected you can just drag and select across your whole Microsoft Excel spreadsheet to

include everything so that's your range you can also say whether or not you want the

pivot table to appear here on this spreadsheet along with your data or on a new worksheet I'm going to send it to a

new worksheet CU I think it looks cleaner um but you could certainly leave it here especially if you've got a lot

of computer screen real estate that can be convenient so I'm going to click okay it's going to make a new worksheet and

it will have this weird blank pivot table here and then on the right it will have opened this thing called pivot

table fields and it'll have a list of your variables

here I'm going to analyze this are you a freshman question in order to do that I need to tell it that this is the

variable I want to use and so I'm going to click and drag are you a freshman down to this rose box

so now Excel knows that we have are you a freshman is going to appear in the rows

of this table but it doesn't show us any data yet and that's because we haven't told it what data we want it to be

accounting for we really just want to learn about how many students are freshman and how many students are not a

freshman so we're going to drag are you a freshman into the values box as well this says display me the number for this

variable okay unfortunately Microsoft Excel does not automatically assume that you're a

statistici um and because of that it sometimes defaults to having your variable set as a sum so really what

it's doing is it's adding up all the ones and twos and giving you totals we don't want that what we want is a count

and so the way we switch this to count is you you see it says under this values box it says sum of are you a freshman

we're going to click that and we're going to change the value field settings uh in this little menu here

okay and so the second option is count count is what we want we want to know how many people answered one and how

many people answered two that's counting so select count and click okay and now you've

got a count of how many people selected a one and how many people selected a two the next step is to figure out how

we can get percentages out of this table you may remember from class that counts are not necessarily very

informative percentages are often times much better because it allows us to think about how many

individuals uh in terms of proportion question s answered in each particular way that's often easier to interpret

otherwise you find yourself kind of doing math in your head nobody wants that so how do we get percentages well

the easy way to do it is to click any either one of these cells where our values are displayed and we're going to

right click we're going to go down to this show values

as and we're going to select percent of column total because we want this column this count to add up to 100% here so

we're going to go ahead and click percent of column total and now you have

percentages okay the other thing that we might want to manipulate though it's not so

relevant here is maybe there are response options that are listed here that we're not so interested in say for

example there was one person who had a missing response we might have a whole another row dedicated to who was missing

who didn't give an answer if we wanted to change what gets displayed maybe we only wanted to know

about the uh the folks who said or answered one to are you a freshman we can click this arrow on the row labels

and we can select what gets included and what does not get included so let's say I only wanted one to appear I can

uncheck two and now the table only includes one but of course it adds up to 100% so that's not terribly informative

we want the whole group okay we can also get both counts and percentages in the same table as we if

we want we can just drag are you a freshman into values again we can add a whole another entry and again it's

counted as a sum we got to change that back to account no problem and now we can see both the

counts and percentages for these answers the last thing that we might want to do to make this easily readable is to

label what is one and two so we go back to our code book real quick and we say oh are you a freshman one means yes and

two means no so one means freshman two means upper classmen so I can just type into these boxes

and now I have a clear label that makes this very very easy to read okay the other form of descriptive

information that you could possibly need is something like an average or a median and in this particular survey

they had a variable that could be measured anywhere from 0 to 100 and so we can calculate an average quite

easily to calculate an average all we need to do is create a minor Microsoft Excel

formula so the way that we do that is with the equal sign so I'm going to click any cell any cell I want I'm going

to pick this one and I'm going to use the equal sign first which is

Microsoft cell's immediate code that says hey we're going to compute a formula in this

cell and I'm going to type in average which is the command I want it to do I'm going to open parentheses and I'm going

to go ahead and drag and select all of the cells I would like it to average together these are the cells I want you

to average and then I'm going to close that parenthesis and hit enter and that's the average value across this

whole variable you can do the same thing with a median you can say equals

median open parentheses and select this value select all the values that are relevant and now you know that the

median score is 40 so these are ways that you can get basic descriptive information about

single variables in the next video we will look at Cross tabulation and how we might

compare two variables together