in this video I'm gonna show you how to handle yes or no fields in a pivot table
I have data here and this firm has a number of restaurants and they have been
handing out satisfaction surveys to these restaurants and they would like to
know the reason why or the locations where they're getting good response and
the locations that they're getting poor response and so to start a pivot table I
always click on your field of data and I go to insert on your menu bar and I
insert a table and it's a plain ordinary table to start with and I make sure that
my table has headers is checked here you say ok and so there you have your data
so now I would like to create a pivot table out of this so I'm going to go to
my insert insert tab again and I'm going to instruct add table and I'm going to
select pivot table and I'm going to put this on on a new new sheet and that will
be fine and so what I'm going to do is I am going to I am going to put the
restaurant location in the rows column or in the rows field here so I'm going
to drag it down throughout there and then I am going to select the survey and
I'm going to put that into the values and it says count of surveys but if you
notice here everybody got a survey and so or and so
this doesn't this doesn't indicate whether there yes or no they've
completed the survey or not it just said that these were handed out so let's go
back to our sheet 1 let's look at the data that we have here so if you look at
the survey it says yes or no it doesn't say yes we sanded
nothing for the other one so let's create another field here let us go yes
and then we're gonna do a simple if statement we're gonna go equals and
we're gonna go if and I'm going to go and I'm going to go Open bracket and I'm
gonna select this serve or this field here and I'm gonna say if it equals
quotation marks yes
and if quotation marks I'm going to put a comma and I'm going to say if it says
yes then we're gonna put a 1 there and if it's if it says no we're gonna put a
0 there and then I press ENTER and that will go through and so now we have
number of yeses there and so let's go back to our sheet 3 where our pivot
table is let's go to the data tab here and let's go refresh all and so now what
we're going to do is we're going to take the count of survey out because we know
that they had surveys and we're going to put the yes in there we're going to
click on the yes and we're gonna drag it down to the values and it's the sum of
yeses so this is a much more accurate representation of what of what the
Cerveny surveys that were returned by the customers as opposed to those that
were handed out so let's go to the analyze tab here and let's put in a
pivot chart and this one looks fine and so there you have the locations of your
restaurants and the total number of surveys that were completed by the
customers so this is how you handle a yes/no field in a pivot table please
subscribe