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.
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.
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:
- To select a check box, press the Ctrl key, and click on the check box
- Click in the Formula Bar, and type an equal sign =
- Click on the cell that you want to link to, and press Enter
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.
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.
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.
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”)
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)
_____________________
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
Michael, you could format another cell with WingDings font
Then, enter this formula in the formatted cell:
=IF(A1=”Yes”,CHAR(254),CHAR(168))
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?
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.
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
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 .
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.