The wonderful uses of Excel checkbox: a quick guide

This article will guide you how to use check boxes efficiently in Excel, covering adding, deleting, using it in combination with IF, AND/OR functions, and applying conditional formatting. Easily track task progress without complex developer tools.

Quick link

• How to add and delete check boxes
• Operation of space bar and Delete keys on check boxes
• How to combine checkboxes with IF functions
• How to combine checkboxes with AND or OR functions
• How to apply conditional formatting to check boxes
• How to apply conditional formatting to other cells based on checkbox status

Adding checkboxes in Excel sheets before June 2024 requires using form controls in the developer tab, which requires some expertise. But now, Microsoft offers an easier way.

How to add and delete check boxes

Excel's checkbox option is located in the Controls group on the Insert tab of the ribbon. The Control group is not visible by default, you need to enable it first.

Right-click anywhere in the ribbon and click Customize Ribbon.

In the Select Command from the following location field, click Main tab.

Next, click the Insert arrow to expand the Insert option, and select Controls. Then, click Add to add it to the ribbon.

Lastly, click OK to close the dialog box. You will see the Checkbox in the Controls group on the Insert tab, and you can click it at any time to add a checkbox to the worksheet.

To reposition the checkbox button in the Insert tab of the ribbon, use the arrow to the right of the Customize Ribbon dialog box.

Operation of space bar and Delete keys on check boxes

It is important to understand how check boxes are affected by different keys before understanding the different ways to use them.

When you click the checkbox icon in the Insert tab, you will see an unselected check box in the selected cell. If you select multiple cells, multiple check boxes are added—one for each cell. Clicking the checkbox with the mouse can add and remove check marks, and you can also do the same with the space bar, which is a handy trick if you prefer to work in Excel using just the keyboard. Similarly, if you select multiple cells containing the checkbox and press the Spacebar, all checkboxes will be selected and unchecked at the same time.

To delete a check box from the worksheet, select the cell where the check box is located and press the Delete key. If your checkbox is selected, pressing the Delete key will first uncheck the checkbox, pressing the Delete key again will completely delete the checkbox.

How to combine checkboxes with IF functions

The first step to getting used to using checkboxes and Excel functions is to try using IF functions alone.

In the following table, we want the Status column to tell us whether the task has been completed or not based on whether the check box is selected.

The value of the check box is TRUE, and if not selected, it is FALSE. So when using the IF function, we will tell Excel to consider these parameters.

Starting with cell C2, we will create an IF formula with the following syntax:

<code>=IF(x=y,"a","b")</code>

where x is the cell containing the checkbox, y is TRUE or FALSE, a is the result we want to produce when the condition we entered is satisfied, and b is the result we want to produce when the condition is not satisfied.

In our example, we will type

<code>=IF(B2=TRUE,"Complete","Incomplete")</code>

Because we want Excel to find out whether the checkbox in B2 is selected and generate the words "completed" or "not completed" accordingly in C2.

If you want Excel to generate text based on the state of the checkbox, remember to use quotes around the values ??a and b.

If you format the table using Excel's table formatter, when you press Enter after entering the above formula, you will see that the formula is automatically copied to other cells in the column.

Now, select some check boxes to see the results changes in column C.

If you click down and drag the table handle (format the bottom right corner of the table), the new row will automatically contain the checkboxes and status formulas you added.

How to combine checkboxes with AND or OR functions

We can now use the checkbox to check the progress of tasks containing multiple stages.

Use checkboxes with AND function

In this example, we want the Status column to tell us whether all stages of each task are selected.

This is why we need to use AND functions with IF functions - AND tells Excel that we evaluate multiple conditions at the same time.

The following is the syntax:

<code>=IF(AND(x:y=z)"a","b")</code>

where x:y is the range containing the checkbox, z is TRUE or FALSE, and a and b are the results we want to see, depending on whether all conditions are met.

So, in our case, we will type

<code>=IF(x=y,"a","b")</code>

Because we want Excel to evaluate the checkboxes in cells B2, C2 and D2 to tell us whether they are all selected.

Use checkboxes with OR function

You can also follow the same principle using OR functions:

<code>=IF(B2=TRUE,"Complete","Incomplete")</code>

where x:y is the range containing the checkbox, z is TRUE or FALSE, and a and b are the results we want to see. This tells Excel to evaluate the checkbox to see if any (and not all) checkboxes are selected.

In this example, Excel tells us whether the task is pending (if no checkbox is selected) or operated (if any checkbox is selected), because we entered the following formula:

<code>=IF(AND(x:y=z)"a","b")</code>

How to apply conditional formatting to check boxes

To make it easier to visualize data and track task progress, you can apply colors to the checkbox or the cell where it resides in using conditional formatting.

In this example, we want the cell containing the checkbox to turn green when selected.

To do this, we need to select all the cells that contain the checkboxes, click Conditional Format in the Start tab, and then select New Rule.

In the "New Format Rule" dialog box, follow the steps below:

1. Click "Format only cells containing the following".
2. Set the first and second drop-down menus to "cell value" and "equal" respectively.
3. Enter TRUE or FALSE, depending on whether you want the format to be applied to the selected checkbox or the unchecked checkbox.
4. Click Format and format it according to what you want to happen when the check box is selected or unchecked. In our example, when the checkbox is selected, we format the cell as green fill.
5. Click OK.

How to apply conditional formatting to other cells based on checkbox status

The last option is to format the entire line if all check boxes are selected. In our example, we want to clearly show that tasks 3 and 5 have been completed.

First select the entire table (except the title row), and then start the New Format Rule dialog box by clicking "Conditional Format" > "New Rule".

Then, follow the steps below:

1. Click "Use formulas to determine the cell to format".
2. In the empty field box, enter =AND($B2:$D2,"TRUE"), where $B2:$D2 is the cell with the first row of the table containing the check box, "TRUE" tells Excel to all The format is applied when the checkbox is selected. Make sure to add the dollar sign before the column references ($B2 and $D2) - this tells Excel that we want the conditional formatting rules to apply to all rows in these fixed columns.
3. Click Format to determine what happens when the conditions you set are met. In our case, we choose yellow fill.
4. Click OK.

check box is not the only way to track the progress of a task. In fact, you can create an entire data dashboard in Excel to monitor your workflow at a glance.

The above is the detailed content of How to Use Checkboxes in Excel to Track Task Progress. For more information, please follow other related articles on the PHP Chinese website!