Clever ways to use Checkboxes in Excel

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. 

Percentage checked/unchecked

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) 

Excel – How to make and use a checkbox
How to protect Excel sheets from unwanted changes – Part 1
The danger still lurking in Excel and how to stop it