Checkboxes in cells

Excel 365

Checkboxes can be inserted into cells in Excel.

Previously to show a tick or a cross, you would need to use the Insert menu and then Symbol to select a tick or a cross from the Wingdings font set. Alternately the function CHAR could be used by entering a value between 251 and 254 and formatting the range as Wingdings. For example =CHAR(252) would return to tick. To get a checkbox that could be checked and unchecked, you would need to use the Developer menu and Insert to add a Form Control or ActiveX Control Check Box.

Now Excel has made it easier to insert checkboxes.
Select a range of one or more cells.
Click on the Insert menu at the top of the screen and click on the Checkbox icon in the Controls section .
Checkboxes will be inserted into the selected cells.

To check a checkbox, click on it.
To uncheck a checkbox that is checked, click on it.
To check or uncheck multiple checkboxes, select the checkboxes and press the Space bar.

Checkboxes can be copied to other cells.

The underlying value in the checkbox cell is a Boolean. When the checkbox is checked, it is TRUE. When the checkbox is unchecked, it is FALSE.

Simple example

This is a simple list, where each step can be checked as it is completed.

Conditional formatting

Conditional formatting can be applied to checkboxes.

To set the checkbox colour to green when checked and red with unchecked, follow these steps.

Select the range with Checkboxes.
From the Home menu select Conditional Formatting, Highlight Cells Rules and Equal To….

Enter a value of TRUE and select Custom Format….

Select the Font tab.
Set the Color to green.

Repeat the steps but enter a value of FALSE and a colour of red.

The checkboxes will now be green when checked and red when unchecked.

Setting the checkbox value using a formula

The checkbox can be set to checked or unchecked by a formula that returns TRUE or FALSE.
The formula is entered into the same cell as the checkbox.
There is a small downside to doing this, as the checkbox can only be checked or unchecked by the formula. It cannot be manually set.

As an example, the checked or unchecked value is going to be set based on the value in another cell.

If the value in Column D for the same row is equal to one, then the checkbox will be checked.

Select the first cell with a checkbox and enter the following formula.

=IF(D6=1,TRUE,FALSE)

Copy the formula down across the range of checkboxes.

Setting the checkbox value or show text using a formula

The previous example can be improved by replacing the unchecked checkboxes with text or an empty string.

Modify the formula to return text instead of a FALSE value.

=IF(D6=1,TRUE,"To do")

This is be better for users, as they cannot check checkboxes set by formula.

Referencing a checkbox value in a formula

Each checkbox holds a TRUE or FALSE value depending on it checked state. This can be referenced in a formula.

A summary can be added showing the progress as a percentage complete by evaluating the number of TRUE values against all values.

The formula uses COUNTA to calculate the divisor and COUNTIFS to calculate the dividend.

=COUNTIFS(A6:A15,TRUE)/COUNTA(A6:A15)

Referencing a checkbox value in a formula for conditional formatting

The checked or unchecked value of the checkbox can be used to format text next to the checkbox with a strikethrough format when the checkbox is checked.

To set this, select the range of text next to the checkboxes.
From the Home menu select Conditional Formatting, Highlight Cells Rules and More Rules….

Select a Rule Type of Use a formula to determine which cells to format.
Enter a formula of

=A6=TRUE

Click on the Format button.

Select the Font tab.
Tick Strikethrough under the Effects section.
Click the OK buttons.

When a checkbox is checked, the text next to the checkbox will be formatted as strikethrough.

Alternate layouts

Checkboxes can be inserted anywhere, in any layout.
You are not restricted beyond having them inside a cell.
Here is an example that uses a grid instead of a list layout.