Insert Checkboxes In Excel 2010

Excel provides very useful Form Controls which enable users to create a control & operate several things simultaneously. You can add Buttons, checkboxes, Labels, Combo-box, Scroll list etc. The real usage of form controls can be attained when you are dealing with colossal datasheet, and you need to invoke several functions and actions in desired order. These controls also abet you, when the data cells interconnect with each other. In this post we will be using Checkbox form control in a scenario where more than two ranges are related with each other.

Launch Excel 2010, and create a datasheet on which you want to link checkboxes with different actions. For instance, we have created an attendance sheet of students, containing fields, Name, and Attendance. We have also included another table in our datasheet that contains fields Total, Present, and Absent.

attendance sheet

To start off, we need to make Developer tab apparent on Excel window. In case you don’t find Developer tab, go to File menu, click Options, and in left pane click Customize Ribbon, from right pane enable Developer check-box. Click OK to to see Developer tab on the ribbon.

developer tab

Now we will include checkboxes in the datasheet, in order to populate table entries with single click.  Navigate to Developer tab, from Insert options, click checkbox image present under Form Controls, as shown in the screenshot below.

developer

Upon Click, you will see plus sign pointer, click where you want to add checkbox.

checkbox

Select the text and remove it, and then right-click over it, click Format Control.

right click

The Control tab of Format Control dialog will open-up. By Cell link, select the location in the datasheet where you want to show the check/uncheck status of checkbox, which will be TRUE or FALSE respectively. Click Ok to continue.

check box options

Now we will move checkbox to the end of the Attendance cell, you will notice that the cell it is referring to H2 location, which will change the values TRUE/FALSE.

false 1

Upon enabling checkbox, the value in H2 location will automatically change. We will write formula in Attendance column at location C2, and that will check the value in H2 location,

=IF(H2=TRUE, “Present”, “Absent”)


The first parameter of the formula H2=TRUE, checks the value in H2 that if it is TRUE, keyword Present will be appear in the cell, and if it is False then Absent will appear in the cell.

Attendance present 1

Now follow the same procedure for including checkboxes with all the cells in Attendance field. As you can see in the screen shot below,  that we have created check boxes with the cells, and where the check box is enabled, the corresponding value at H2 column will change to TRUE, eventually through the formula evaluation the Present will appear in corresponding cell present in Attendance field.

complete table

Now we will start populating next table, here we have entered 10 in Total row (as we have 10 students).

next table 1

We will count the occurrence of keyword Present in the table Attendance column. So we will write formula as;

=COUNTIF(C2:C11, “Present”)

The result will be 9, as there is only one student Absent.

present studs `1

Now for checking how many students are absent, we will simple subtract number of students present, from total students. The formula goes like this

=($B$14-$B$15)

It will simply subtract value at B15 (students present) from value at B14 (Total), and yield the desired result.

attendance absent

You will also notice that on enabling/disabling checkbox, it will automatically update all the related info.

complete

You can also check out previously reviewed guides on Using Macros through Buttons & Using Camera Tool in Excel 2010.

Advertisement
  • Pingback: Excel 2010: Password Protect Spreadsheet

  • Man

    very nice.

    thx and god bless. :>

  • Sergio Nobre

    Many Thanks.
    That’s a very nice job. Congratulations.

  • Amandeep Singh

    Awsome job. Thank you very much

  • GLEN

    i create a check box but this appears instead of a thin border, like check box 23 above, only the bottome and right borders are thick, when done making the check block those thick boarders stay, i did not set them that way and other checkboxes did not do this, but now any i create do these. help!

  • Log

    question for connoisseurs ..;)

    1. I have an “object” .. which is divided into three parts … these are three small tables
    2. from these tables get 5 different data .. x 3 Size, Length x 2
    3. Selection of all data used by a checkbox ..
    So .. that would use all 5 data .. .. I have to click on 5x and it is only one object ..
    objects have a lot ..

    I would like to include a selection of these 5 data ..
    So with one click on the checkbox to include the previous 5 checkbox’s ..
    can it be done? and how?

  • Jim Philp

    When I make a spreadsheet with a lot of names and check boxes in adjacent columns, and then try to sort the names alpahbetically, the check boxes don’t sort, but the names do, so everything is out then. Is there a way to get the check boxes to sort as well ?

    • http://www.facebook.com/TAZEEBOO Fatima Flores-Goya

      I need help in this same issue… I had only 36 lines of people and had to manually and individually change the BOX formula for each… and when I widened the column to fit the word “ATTENDING” to appear next to the check box (not behind bc it was too long) I had to move each individual box over, causing a little bit of a dis-alignment of the check boxes. Took forever! Is there a way to edit the boxes together (i.e. MOVE all the check boxes over a mm.) and copy the boxes to the other cells sequentially as we can with the actual excel cells?

      • Elliott

        If you hold CTRL when selecting the check boxes you can move them all at once.

    • MattOfAllTrades

      Did you ever get an answer for this? I see an answer for moving a group, but I am having the same issue you had. That the check box doesn’t seem to sort with the rest of the columns (so checks against a name don’t follow that name when a sort is applied)

  • Jack

    Is there a way to increase the size of the box itself? With my tired old eyes it’s hard to see it. Many thanks.

  • Ian

    Clear concise and it WORKS – well done!

  • Jk1302

    It was very useful, Thanx !!

  • Rahul2trivedi

    Good thanks!

  • mister

    thanks, maturnuwun mas

  • Steve Butler

     Thanks for the post, very clearly explained.

    Thank you/…

  • Pacittk

    I want bigger boxes for the check.

    • cps

      Increase check box width & height as per your requirement.

      • JennS

        How? When I change the size of the object under format it doesn’t actually make the check box itself bigger.

    • GG

      Did you succeed making the box and the text bigger? I want to do it to!

  • http://www.facebook.com/people/Marko-Wolf/1676554438 Marko Wolf

    Hmm, i have one problem…
    Can i copy checkboxes with true and false without that i must open one by one and fixing want to copy checkbox with a cell link

    • lpellica

      Did you get an answer to this question? I can’t seem to see the rest of the discussion.

  • Naomi

    I seem to have fallen at the first hurdle, as I can only seem to customize the quick access toolbar, and not the ribbon! :-/

    • Naomi

      It seems I was looking so hard for what is shown in your image above, I failed to see it has actually been made simpler to find… d’oh!

  • Mike

    At the step where you enter in the value for c2 cell, then you click the check box and it toggles between present and false on your screen it seems, where as on my screen it just comes up with ‘#name?’ even though i copied paste exactly the formula you have done and changed it to the correct corresponding cell in my excel. What am i doing wrong?

    • Wil

      Im having the same issue, cant see any replies to this that clarify. Can someone help us out?

      • wil

        Got it figured out… dont cut/paste. Type out the formulas on your own.

  • Jim Z

    Is it possible to copy and paste the check boxes without opening each one to fix the cell link?

  • pumpernickle

    thx for tutorial,.
    it is help me :)

  • pumpernickle

    thx for tutorial,.
    it is help me :)

  • Hany Fouda

    Thanks for Info. , it helps me alot

  • http://www.facebook.com/renee.dauplaise Renee Dauplaise

    Is there a way to auto-fill the check boxes based on a selection in another cell? To explain, I have a Y/N question that is a header over a series of sub-steps with check boxes. If all the check boxes are TRUE, I can get the high level indicator to say Yes. Can I also do the reverse?

  • atessier

    Still can’t change the size of the actual checkbox itself…what’s the trick? It’s way too small.

  • Elle

    how do I add check boxes that will calculate percentages?

  • uvialde

    Thank you! This was super helpful!

  • Asha

    Thank u very much

  • Moov

    Thanks needed it

    • Sapna Pari

      can you plz tell me how to fix the boxes when i check in print view all boxes are mnove from their place pplzzz help me

  • JON

    so helpful. Thank you and keep it up.

  • Colleen

    This was perfect, exactly what I was looking for. Thank you for writing this up.

  • Mik

    I really like the idea, but very cumbersome for larger spreadsheets. Is there a checkbox cell, that I can just copy /replicate much more easily?