September 21st in Basic Help by Excelbud .

Check boxes in Excel

Unknown to many excel users, you can easily set up check boxes in Excel. You can use these check boxes to total a list of data by selecting various boxes.

Excelbud

Owner and site operator for Excelbuddy.com

Quick Overview

Unknown to many excel users, you can easily set up check boxes in Excel. You can use these check boxes to total a list of data by selecting various boxes.

The following Excelbuddy.com video demonstrates what this tutorial will accomplish.

No Flash you for you, sorry :)

The following tutorial will show you how to accomplish this task by creating an auto-totaling grocery list.

Lets Begin: Add the Developer Tab

  1. First, we need to make sure the developer tab is displayed. Click the Microsoft logo in the upper left .
  2. Next, click the “Excel Options” button.
  3. Next, click the box for “Show the Developer tab in the Ribbon”
  4. Now click “OK” – Not sure why I typed this step, but oh well!

menu

How to make an auto-totaling grocery list using Check Boxes

The following example will show how to make an auto-totaling grocery list. The steps in this example can be adapted to fit a multitude of other applications. The files used in this tutorial are available at the bottom of this post. If you have any questions, please comment.

  1. Start off with a list as shown below.
    1
  2. Next, goto to the “Developer Tab.”
    2
  3. Click “Insert”
    3
  4. Then click “Check box (Form Control)”
    4
  5. Once selected, the mouse will turn into a “+” sign. Click around Cell D4.
  6. To Edit/Move the check box, you must right click with your mouse. Using the arrow keys, you can easily align the check box with column “D.”
    6
  7. To removed “Check Box 1,” right click and deleted the text.
  8. Right click again on the Check Box and select “Format Control”
    8
  9. In the “Format Control” box, enter cell “$C$3″ in the cell link box and hit “OK”
    9
  10. Deselect the check box by click outside of the highlight box.
  11. Click the box. Once clicked, you will now see the word “True” in Cell C3. Remove the check and the word “False” is displayed.
    10
  12. Now comes the fun part. Enter the following formula into cell “E3.”
    • =SUMIF(C3:C3,TRUE,B3:B3)
  13. If the check box is selected, the dollar amount in displayed in “Column E”
    11
  14. If the check box is not selected, the dollar amount in not displayed in “Column E”
    12
  15. Now, repeat steps 3 – 12 for the remainder of the list.
    13
  16. Now use the AutoSum function to count the selected items in the grocery list from column “E”
    15
  17. To make the Grocery List more aesthetically pleasing, hide columns “C” and “E”
    16

That sums up the basic introduction into the world of check boxes in Excel. Hopefully you found this tutorial easy to follow.

Please comment below if you have any questions.

9 Comments

  • Dee Sledge
    November 22, 2009

    I tried this on my XP machine, EXCEL 2007 at work and the check boxes always stay “FALSE”. Even with the check mark, they continue to be “FALSE”. I sent the file to 2 people and it works just fine. I tried it on a coworkers machine at my work with the same result…does not change to “TRUE”, Any ideas? Dee Sledge

  • Mohamed
    December 22, 2009

    useful tutorial. it is also correct to write the formula as =SUMIF(C3,TRUE,B3)

  • Toni
    February 16, 2010

    I have Microsoft Excel 2007 and I am not able to find the developer tab. Is it located some place else?

  • Toni
    February 16, 2010

    Nevermind I found it

  • QQ
    March 10, 2010

    i would like to ask about step 15, is there any quicker way to do that?

  • Excelbud
    March 10, 2010

    You can copy and paste the formula’s to speed things up. Unfortunately you can’t use the auto fill feature. It sucks, I know :)

  • Matthew
    May 22, 2010

    You can use the fill handle (the square in the bottom right hand corner of the highlighted cell(s)) to easily populate new cells with updated formulas (ie if b1 has the formula 1+a1 in it and you use the fill handle to populate the following 2 rows (2 and 3) the formula in b2 will be 1+a2 and the formula in b3 will be 1+a3)

    You can copy and paste check boxes but you will have to right click, go to form control, and change the cell that the TRUE/FALSE is connected to.

    Hope this helps!

  • Jake
    June 24, 2010

    I am using an excel spreadsheet that has check boxes. When a box is checked the to its right goes from a light grey colour to a bright green colour. The “sumif” formula cells are hidden somewhere and I can’t find them.

    I’ve added some boxes and need to keep the green wording consistent when I tick the box. Any ideas how this is done?

  • Jake
    June 24, 2010

    Sorry. The second sentence should read “when a box is ticked the word to its right goes from being a light grey colour to a bright green colour.

Leave A Comment.