Now you know how to insert checkboxes in Excel, here are some ways to use check box cells in Excel. Counting the checked/unchecked boxes, calculate percentage checked and even showing a separate list of items based on their checked status.
A cell with a check box has two values:
TRUE – checked
FALSE – UNchecked
You can use the values TRUE or FALSE in formulas.
Sometimes you want a value (usually 1 for True or Zero for False) instead of the Boolean values. As we’ll see CountIf() can return the number of cells either checked or not.
Mixed check box
Excel checkboxes can have a ‘Mixed’ value instead of either True/False. It’s rarely used in the real world but worth keeping in mind when making formulas based on checkboxes. If Mixed is possible in the sheet …
The Mixed setting for a check box is screwy because it’s neither True nor False and there’s no other option available. Instead Excel returns the #N/A error. Test for that using the =ISNA() function.
For example to test for a Mixed check box in B12 before doing other things
=(IF(IsNA(B12),<return some value for mixed>,IF(B12,<return something if True>,<return something if False>).
Total Amount Checked or Unchecked
You can use the formula =COUNTIF (range, criteria) to calculate the total amount of checkboxes ticked / True.
For our example, we have used =COUNTIF(B2:B7,TRUE) which will calculate all the checkboxes returning the TRUE result within that range.
You can use the formula =COUNTIF (range, criteria) / COUNTIF (range, criteria) to calculate the total percentage of checkboxes ticked.
For our example, we have used =COUNTIF($B$2:$B$7,TRUE)/COUNTIF($B$2:$B$7,"<>") which calculates all the checkboxes returning the TRUE result within that range divided by “<>” which refers to not equal to, resulting in all cells within that range that are TRUE or FALSE.
List of Checked or Unchecked items
If you would like to know straight away how many from the list are left outstanding you can use the dynamic array formula in Excel 365.
=FILTER (array, include, [if_empty])
In our example, we have used the following formula =FILTER(A2:A7,B2:B7=FALSE)
This has provided the list of outstanding names left to interview, because we’ve tested for FALSE within the formula (the remaining interviews left unticked).
Of course, if you want a list of checked items, change the FALSE to TRUE e.g. =FILTER(A2:A7,B2:B7=TRUE)