Use Check Box Result in Excel Formula

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. Then, use check box result in Excel formula solutions.

Form Controls on 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)

_____________________

61 thoughts on “Use Check Box Result in Excel Formula”

  1. 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.

  2. 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.

  3. 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?

  4. 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?

  5. @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.

  6. 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.

  7. 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

  8. 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

  9. 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.

  10. 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

  11. 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.

    1. 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.

      1. 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!

  12. 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

  13. 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.

    1. @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),””)

  14. 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.

    1. Hi Scott,
      You could put the minimum price somewhere in the workbook, and name that cell MinPrice.
      Then, compare the selling price to the min price in the formula:
      =IF(G10,(D11-((D11>MinPrice)*E11*0.08625)),D11)

  15. 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?

  16. 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?

  17. 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 * =

  18. 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

  19. 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!

  20. 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.

  21. 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

  22. 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?

  23. 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.

  24. 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

    1. 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

  25. 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

  26. 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

  27. 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?

  28. 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.

    1. 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.

  29. 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

  30. Hi

    Guys i need a help to calculate my overtime sheet. As i put a checkbox in attendance panala and i want to know if i tick that checkbox and if that checkbox is true i want to add time as my duty off time and redeuce my overtime .

  31. I’m trying to create a skills based rota but it is challenging.
    Sheet 1 has a list of names in column A and checkboxes in columns B to H for each skil. Each of these checkboxes are each linked to a column in sheet 3 to be able to create a source list for data validation.
    I want to create a ‘create new’ button that pops up a user form to ask for the persons name. The user enters their names and clicks okay. The name is inserted in the next available row and also checkboxes are inserted and links are created automatically.
    I would like to use wingdings checkbox rather than a control form as it looks better and cannot be edited with a right click.

  32. This is such a ridiculous circuitous route toward something that should be one step, as it is in such programs as iWork numbers. Figured it out right away there – but a friend wants to do it in the “industry standard” so I’ve been trying to figure it out for days in excel and even saving the numbers file as excel file tells you “checkboxes not supported… will be replaced with true/false”.

  33. I am working on a estimation program for our website. I am having problems finding the right formulas for multiple check boxes.
    If this box is checked then it will be this price per feet and inches. Some of our colors only come in one the check boxes. So I would also need something that would distinguish if a certain was being used it would a different price per feet/inches.

    Metal 40 Yr Lifetime Panel Loc Panel Loc + (Panel Loc & Panel Loc = $0 (profile only)

    29Ga $2.15 $2.25 – Limited Colors available.
    26Ga N/A $2.65 – All Colors are available

  34. I am trying to create a boundary diagram that shows which components of an assembly interact. I have the control form check boxes with formulas created (shown below) where someone can say I am designing parts A,B, and C and they will interact with parts X,Y, and Z. My issue is that I want to create another IF statement into the cell which says that if you click part A on its true and IF part A is on it will interact by bolt or clip etc.

    =IF(names!$C$13,names!$B$13,NA())

  35. I created a checklist with checkboxes that I would like to send to my customers to use. I have locked all cells except the ones containing the checkboxes. But when I send it out, the checkboxes do not appear.

  36. How to limit number of checkboxes selected by using simple excel statement?
    i.e. 10 checkboxes, when more than 4 selected, it does not allow

  37. Hello I want to join 2 formulas together. i have drop down option that gives me a number and i want to incorporate another formula where when i select my checkbox all numbers default to 0.

    =IF(ISTEXT(B7),(VLOOKUP(B7,$M$6:$N$20,2,)),(” “))

  38. Hi I have a spreadsheet of a list of items to be serviced, I have a check box that generates a TRUE or FALSE cell, this then links to a table that calculates all the items serviced and totals the price of servicing. However, my customers now want the items to be individually priced on the main sheet aswell as the table. I am trying to write a formula that will automatically produce a price in an adjacent column when the check box is ticked and return the correct price according to that description. I have several different items, with individual prices. So B26 (item from dropdown list, selected by the engineer) K26 True/False (created from the check box) and the prices are in a separate worksheet A1:A8 (are the different items) B1:B8 (are the different prices for each item).

  39. im trying to do the opposite of everyone else. lol i want the boxes to automatically check if this word appears in a cell. is that possible?

  40. Hey I’m trying to make a check box that will change the date in 3month intervals in another cell
    So tick check box then it’s changes the service date to today’s date or a 3 month interval

Leave a Reply to Debra Dalgleish Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.