adding a checkbox to an Excel spreadsheet is a little more complicated
than putting an X in a cell but it's also a little magic and I'll show you
how it works
so I know you're thinking a checkbox how complicated could this be but actually
checkboxes and other objects you can put into Excel start treating your
spreadsheet like a database and that's part of the magic of Excel is that it's
not just a place where you put in numbers and add and divide them and
stuff like that it can be a database and it can act on the data you put in there
and help you make decisions in these cool ways so I'm going to show you how
checkboxes work that way so here I've set up a very simple
spreadsheet I have three foster kittens and I'm tracking how well they're
learning to play with different toys here's the feather toy and what I'm
doing is I'm counting how often they catch the feather but if they catch it
in midair they get extra points so here I've set up a checkbox for mittens to
see when he's catching it in midair at the moment he hasn't cuz he's a lazy ass
but say that he gets off his butt and catches the feather in midair all I have
to do is click this checkbox and you see that the cell underneath it changed from
false didn't catch it midair to true yes he did and suddenly these bonus points
show underneath he gets three bonus points every times he catches it in
midair and then his total points go up by four now think about this all I did
was turn this checkbox on or off and three things happened underneath it that
is what is magic about the checkbox tool and all these other objects you can use
in Excel so now we're going to set up a checkbox for my second kitten socks and
the first thing we have to do is make sure that you have the developer menu
activate in Excel go to file options
customize ribbon and over on the right you see all the different menus you can
have in Excel go down and you see developer is not checked so I'm going to
check it click OK and now the Developer tab is showing at the top of Excel
that's great I'm going to click it click insert and this box comes up with all
these form controls and ActiveX controls this is like Wonderland for me
but we're going to do checkbox today so I'm going to click check box and then go
down to the cell where I want it click there and there it is
now this checkbox has corner handles this is how you move the checkbox around
in your spreadsheet remember this is not like regular Excel data in the cell
you're actually kind of moving it around on top of the spreadsheet so I'm going
to do a couple of things here I'm going to right-click inside here and edit the
text because I don't need this text next to the check box now I'm going to
right-click within the corner handles again and go to format control and here
you set up some basic settings for the check box like what default value do you
want for it do you want it to be unchecked by default checked by default
or mixed which means it's kind of grayed out and I want it unchecked by default
so I'm going to keep it that way and then I'm going to go down to cell link
and this is where the magic starts happening if you put a cell link in here
that cell will reflect the status of the check box so I want my cell status to be
right in the cell below it so I'm going to click on that cell the cell link will
show there I click OK does it look like anything yet though but watch this I
click on the check box and true shows up I unclick it and false shows up the
check box is a very basic true/false on/off sort of data object and I could
just sit here all day on a really boring day and just watch this go true false
true false but that's what's so cool about how data entry works in Excel
because now that I have this true/false set up I can set up a formula so that
when I check that box or uncheck it Sox will err won't get his midair points for
catching that feather so we're going to show how that works we're going to write
a formula here I'm going to type if cell c6 that's where it says false
equals true then stocks will get three points and if it doesn't equal true
he'll get zero points so right now it says false he gets zero points but if I
click that he does catch it in midair he gets three points so look at that now
the check box is turning the cell below it into true or false and it's also
causing the Mitter the bonus Mitter points to show up as either zero or
three now I want to add the feather points and the bonus points to get Sox's
total score for play time so I'm going to write another formula where I add up
his playtime points and his bonus points and now we have his total of five points
and again this is all happening because of this check box if I make this false
all the points change if I make it true they all change again so now you've seen
how objects like check boxes can add a little magic to your Excel spreadsheet
and if you have any other questions about Excel just email us at answer at
PCWorld com