Use Check Box Result in Excel Formula

To make it easy for people to enter data  on a worksheet, you can insert a check box control, using the Form Control tools on the Developer Tab. If you don’t see a Developer tab, there are instructions here for showing it.

checkboxformula01

Adding these controls to a worksheet can make it easy for people to enter data – they just click to select the option that they want.

optionorcheck02

But, after they’ve checked that box, how do you capture that information, and use it in your formulas?

Link the Check Boxes to Cells

When you add a check box to the worksheet, it isn’t automatically linked to a cell. If you want to use the check box result in a formula, follow these steps to link it to a cell:

  1. To select a check box, press the Ctrl key, and click on the check box
  2. Click in the Formula Bar, and type an equal sign =
  3. Click on the cell that you want to link to, and press Enter

optionorcheck04

Check Box Result is TRUE or FALSE

If you have multiple check boxes, you can link each one to a separate cell on the worksheet.

In the screen shot below, Option 1 check box is linked to cell E3, and Option 2 is linked to cell E4. When the box is checked, the linked cell shows TRUE, and if it is not checked, the linked cell shows FALSE.

optionorcheck03

Use the Check Box Result in a Formula

In this example, each option has a price, and I’ve entered the prices in column B.

checkboxformula02

In a worksheet formula, if you use TRUE or FALSE in a calculation:

  • TRUE has a value of 1.
  • FALSE has a value of 0.

So, we can use the results in the linked cells, to calculate the cost for each option. We’ll multiply the cost in column B, by the check box result in column E.

  • The formula in cell G3 is:  =B3 * E3  and the result is 5, because 5 multiplied by 1 equals 5.
  • In cell G4, the result is 0, because 10 multiplied by 0 equals 0.

checkboxformula03

Test the Result with IF

If your formula is fancier than a simple multiplication, you can use the IF function to test the result in the linked cell.

In cell H3, the following formula shows a text string if cell E3 is TRUE, and a different message if it is not TRUE.

=IF(E3,"This option was selected","Not selected")

checkboxformula04

Another Check Box Formula Example

To see another example of using a check box result in a formula, take a look at Dave Peterson’s loan table formula on my Contextures website. There is a sample file that you can download.

A check box at the top of the worksheet is linked to cell C1. Check that box if you want to see the total amount that will be paid back, instead of the monthly payment required.

If cell C1 is TRUE, then the monthly payment in the table is multiplied by the number of payments. If C1 is FALSE, the monthly payment is multiplied by 1.

This is the formula in cell C12:

=-PMT($B12/12,12*$A12,C$11)*IF($C$1,$A12*12,1)

checkboxformula05

_____________________

You may also like...

48 Responses

  1. Samuel Kingston says:

    I liked this.

  2. Manny says:

    Im trying to figure out how to do a checkbox for a three-option answer where only one box may be checked at a time. I have found VB code to do it, but cant seem to make it work properly.

  3. Sharon says:

    How can I set a master check box on the top of the column, so whenever I select the check-box all other check-box under are selected.

  4. Nisha says:

    Thanks, helped me a lot :)

  5. Peter says:

    i’m trying to do an attendance / absences excel spreadsheet. and i want the checked boxes to show whether or not the students were absent. what formula can I use to add up the checkboxes in the absences total column? i’ve been working with the formulas and have been unsuccessful at this. the boxes used were the ones created from the ‘developer’ tab. is there a formula to add up the rows of checked boxes vs. the ones unchecked?

  6. Doug D says:

    Just wondering if it’s possible to set up a column of check boxes that would insert the current time (in 24 hour format in the cell to the right. Any ideas?

  7. Biejai says:

    @Doug D

    Use =IF(“Cell that the box is linked to”=TRUE;NOW();””)
    Formula checks if the “Cell that the box is linked to” is TRUE or not, and displays current date/time if TRUE.
    And displays nothing if condition isn’t met.
    You may need to format the cell for the proper date and time.

  8. David Bates says:

    Can the checkbox be hidden?
    Depending on the value of another cell, I hide the row on which the checkbox is located, I want the checkbox to disappear as well.
    Thanks.

  9. carel says:

    Good morning,

    when i try to link a formula to my check box for example =B2 i keep getting a invalid reference error message. Can you please help

  10. Milo19 says:

    I am trying to setup a document involving a several formulas. Basically I want tick boxes to indicate how a client was contacted…email or phone. From there I want excel to add up to the number of boxes ticked for calls and the number boxes ticked for emails. I have done this for one row and linked the tick boxes to different cells in a hidden sheet.

    I have then highlighted the cell with the tick boxes and pulled down to copy in down the page but of cause its all linked to the first row of ticks…i.e I tick the “phone” box on row one, and all 399 other phone tick bxes become ticked.

    This is a spreadsheet with 400 plus names

  11. Gropsan Loredana says:

    Hi,

    Short question:
    Is it possible to use the check box (form controls)and to have the possibility to check only one answer available (do not allow checking the other check boxes.

  12. Tom Tagart says:

    Thanks this helped.

  13. Jonathan Jackson says:

    Is it possible to insert a column and add enough checkboxes to match the rows of data needed? I can’t insert one checkbox at a time and readjust each box…

    Thanks,
    Jonathan

  14. Dana says:

    Using excel please design a formula to use a checkbox if checked then multiply by 7%

  15. Jeff Brown says:

    Hi Debra,
    Great information. I have a spreadsheet where we track jobs and the collection of documents. I have 5 columns with check boxes that are checked as each document arrives back in the office. The order they arrive in can be random. I would like to use conditional formatting to colour rows once all the boxes are checked. I was also wondering if check boxes could be created as new jobs are added to the list or do I have to continually add spare rows of check boxes. We use about 500 rows per year and I can clear completed rows to start a new year but I am not sure how this affects associated check boxes. I currently have an automated sort/hide button that uses a macro to sort the rows and then filter the rows to only show jobs that are incomplete.

    • Hi Jeff,
      Wow, that’s a lot of check boxes to manage on a worksheet! You’d have to keep adding them to new rows, throughout the year.
      If you want to keep track of what’s filled in, those check boxes would need to be linked to cells in that row, so you could count the number of TRUE cells.
      Could you get rid of the check boxes, and just have people put an X in the cell instead? Then you wouldn’t have to worry about links, and could just use a COUNTIF formula to count the number of Xs.

      • Jeff Brown says:

        Thanks Debra, That is actually how I have been doing it and I guess we’ll go back to it. I just thought check boxes would be easier so I added them but it looks like it complicates things more than needed. Thanks for the insight. Great Blog by the way!

  16. Jorge says:

    Hi. Good Day! :)) How can i make a responsive checkbox in MS Excel. Once i click the checkbox it will give 1 in respond and then after the process complete it will have the tally form below as total the checks i did? Thank you so much.. <3

  17. Chanveasna Sun says:

    Thank you for the solution. It helped me a lot.

  18. Brian says:

    I am working on a roster for a daycare. I have a list of the students and for each student I need 5 checkboxes for the days of the week (M-F). Based on the boxes checked, another sheet displays the time the child attends, i.e, if Monday=TRUE, display time, etc. I’m using VLOOKUP to get the times but need to populate only the day of the week cells that the child attends.

    • @Brian, in your IF formula, use 2 VLOOKUP functions.
      –The first one would get the value for Monday
      –The second one would get the time, if Monday is TRUE.
      –If Monday is FALSE, the result is an empty string “”
      For example,
      =IF(VLOOKUP(A2,Sheet2!$A$2:$D$30,2,0)=TRUE,
      VLOOKUP(A2,Sheet2!$A$2:$D$30,4,0),””)

  19. Scott says:

    Hi Debra,

    I’m using a check box for an NY sales tax issue. All my purchases are taxed. If a sale is taxable, I get to deduct the sales tax on my purchase, on the return. If the sale is not taxable, the sales tax becomes part of my COGS. I have a quote template setup so that if checked when the sale is taxable, it will deduct the sales tax from my cost & lower my selling price by that same amount. What I’m trying to do is, if my selling price is below $7,500.00, do not deduct the sales tax cost. This is my formula: =IF(G10,(D11-(E11*0.08625)),D11). So G10 is the linked cell to the check box. If checked (meaning the sale is taxable), it will take my selling price (D11) & subtract the sales tax I paid (E11)= my cost * tax rate & subtract it from D11. I want it to only to this if D11 is > than $7,500.00.

  20. NIck says:

    I’m using options buttons to display rows where Column I contains either “Mandate” or “Flex” in the cells. All cells in column I contain either “Mandate”, “Flex” or “Mandate and Flex”. I want the option button if “Mandate” is selected then the row that corresponds to the cell containing “Mandate” or “Mandate and Flex” will be displayed. How do I write that code for the button?

  21. Jonathan Hall says:

    Hi, I’m using a check box to control the display of a chart in a dashboard (on/off). I’ve set up a named range which uses the indirect function linked to the value displayed in the check box B2:

    Getchart1 : =if(Data!$B$2,Indirect(chart_to_display!$D$2,NA())

    When I try and assign the Getchart1 range to a random shape on the dashboard, so the indirect and the check box functions can work, I get the error message ‘Reference is not valid’.
    Any ideas what is happening?

  22. Mike says:

    I am trying to make a budgeting tool where all the options would check boxes with a dollar value assigned to them and would total everything. something like below

    Option 1-10 Qty Total
    Add 1-10 * =

  23. Becca says:

    HI, I’d like to create spreadsheet of 10 questions, all with 3 possible answers, yes|no|don’t know. Basically I’d like to click the corresponding checkboxes for each question. But instead of using loads of rows, I have 1,000 questionnaires at the moment and more to come, is it possible to somehow have the questions once and once saved, the checkboxes clear and I can add in the more data. Kind of like a database? And in another tab have the results, which would be easy to read, i.e. question 1 – 1,000 people said yes, question 2 – 500 people said yes 300 said no and 200 said don’t know. Does that make any sense? It does in my head but I’m completely Excel basic so have no idea if this is even possible or how to start. Many thanks

  24. Matthew says:

    How would I use a checkbox to copy a billing address to a shipping address? I have seen it done in the past, but I am not sure how. Thank you!

  25. Randy says:

    Hi, I’m building a checking account ledger and want to link a my balance to a checkbox. I don’t want the balance column to adjust unless the check-box is selected. Like a reconcile function you see on some finance apps. Thanks.

  26. Margaret says:

    Hi,
    I am trying to create a score sheet for a project, I have a group of yes/no check boxes that if 1 is yes the score is 1, but if two or more are checked the score is 2. I can do the true/false and count, but not sure how to make a group of 5 only equal 2

  27. Shannon says:

    I’m trying to find a way to add a check box option, but only if a certain selection is picked from a drop down menu. For example:
    If “2017” is selected from a drop down menu list in Column A then a check box needs to appear in Column B.
    Is this possible?

  28. Zuhairi says:

    Hi,

    Do you have any idea why suddenly in my spreadsheet, all cells linked to TRUE & FALSE are not calculated when i tick/untick the checkbox?

    Thank you.

  29. Moule says:

    1 box = 8picess convert excel formulas, totel tiles 3 box, 2box 5 picess sales (3-2.5=3picess) stock 3picess how to formula for excel pls formula send

    • MOULE says:

      1 box = 8picess convert excel formulas, totel tiles 3 box, 2box 5 picess sales (3-2.5=3picess) stock 3picess how to formula for excel pls formula send

  30. blanco says:

    Hi I have one box link to a cell and I wouyld liek to create a formula which works as:
    if the box is ticked and cellA is> the cellB then Xvalue but if cellB>0, then value Z

    Coudl you help me to create the formula which could work?
    Like I have one tick box link to a If formula but I want to Hav 3 options as answer and not only true or false

  31. Michael says:

    Is it possible to reverse this whereby if a selection is made from a list using data validation, a tickbox can be ticked automatically as a result of that selection. For example. List in cell A1 contains:
    ‘Select’
    ‘Yes’
    If the list is on ‘Select’ by default then the tickbox remains unticked, but if i select ‘Yes’, the the tickbox will be ticked
    Would that work and if so, what do i need to do in order for that to produce?

    Thanks a lot

  32. Debra says:

    Great Blog and super helpful.. I am looking to create multiple checkboxes, based on what user selects the out put would include details for the one that was checked or if multiple it will show multiple lines of text, any ideas?

  33. Janice Baker says:

    How do I link a checked box in excel and get text in another box ex: column A2 is “procedure change” column B2 is the checkbox, once the box is checked, I want the words “procedure change” to appear in cell A10, add’t cell A3 is “employee education” column B3 is the checkbox, once the box is checked, I want the words “employee education to appear in cell A11….
    If someone can assist me. Thank you.

    • Janice, link the check box in A2 to cell C2, and link the A3 check box to cell C3
      In cell A10, put this formula, and copy it down to cell A11:
      =IF(C2,A2,””)
      Then, if C2 contains TRUE, the value from A2 will also appear in A10, and similar for cell A11.

  34. Bartek says:

    Hi, is it possible to add a checked record line to another tab without macro? I have a price list and would like my customers to tick their selections from other tabs but when they go onto order form all the relevant records/selections are listed on one form. I hope this makes sense. Cheers

Leave a Reply

Your email address will not be published. Required fields are marked *