How to Use Checkboxes in Excel

Checkboxes are a great way to add interactivity to your Excel worksheet.

One of the more creative uses for a checkbox can be to drive multiple behaviors of lists.

Think of how cool it would be to check a box and have an item in one list be lined through and faded in color, while at the same time having that same item removed from a different list.

This can easily be done if you understand this one setting that I’m going to showcase in this post.

We begin with a list of skillsets we are trying to master.

We’d like to add checkboxes to the right of each skill topic.

When we place a check in one of the boxes, we’d like the skill topic to be lined through and faded in color.

Adding the Checkboxes

The Checkbox feature is a Form Control accessible via the Developer tab.

If you do not see the Developer tab at the top of your program, right-click on any visible ribbon and select “Customize the Ribbon…”.

In the Excel Options dialog box, place a check in the box next to “Developer” on the righthand list and click OK.

With the Developer ribbon displayed, in the Controls group, select Checkbox in the upper group of buttons labeled Form Controls.

After you click the Checkbox control, click anywhere on the spreadsheet to add your first checkbox.

Rename the checkbox to “DONE” by clicking within the text of the checkbox to enter edit mode.

Move the newly added checkbox to position it over the cell next to the first topic.

You can test the operation of the checkbox by clicking in the square area.

If you want to create checkboxes for the remaining skill topics, here’s a super cool time-saving trick.

Select the checkbox for editing (hold the CTRL key when you click on the checkbox to activate it for editing) and resize the boundary of the checkbox to be within the boundary of the cell.

Next, click in the cell to display the normal Excel cursor.

Click and hold the Fill Series handle and drag it down to the adjoining cells.

If your objective is to just have checkboxes for purely visual feedback, you have reached the Finish Line; nothing further is required.

However, if you want the checkboxes to alter the appearance of the listed skills, then a few more steps are required to get these items to work together.

Excel basics course Leila Gharani

Excel Essentials for the Real World - Complete Course

Learn Excel from Scratch or Fill in the Gaps.

Become Confident.

Packed with Challenges & Files (Excel 2016, 2019, Office 365).

GET ACCESS

Saving the State of the Checkbox(es)

If you want Excel to use the current checked or unchecked state of the checkboxes, you must tell each checkbox where to save its active state.

This is done by pointing the checkbox to a cell.

For our example, we want the checkbox that is hovering over cell C2 to save its state in cell C2.

Right-click on the first checkbox and select “Format Control…”.

In the Format Control dialog box, enter the cell address of cell C2 in the “Cell Link” option and click OK.

NOTE:  This “Format Control…” step must be repeatedly performed for each of the checkboxes, with each checkbox pointing to a different cell link location.

The “Cell Link” option is not dynamic and does not update even if the checkbox is copied and pasted to a new location.

Testing the Cell Links

Place a check in each checkbox.  You will see the words “TRUE” in the cells behind the labeled checkboxes.  Remove the checks and you will see the words “FALSE”.

It appears a bit messy, but we can fix this by selecting the linked cells and setting their font colors to WHITE.

Power Query course Leila Gharani

Master Excel Power Query Course

Beginner to Advanced (including M) 

Collect, Combine and Analyze Data with Ease - Create Pivot Tables with Data Model.

GET ACCESS

Applying Conditional Formatting to the Skills List

Now that we have cells that change to “True” or “False” depending on the checkbox states, we can leverage those true/false cells to control the look of our listed skills.

This will be achieved using the Conditional Formatting feature in Excel.

Step 1: Select the cells holding the listed skills (ex: B2 through B6).

Step 2: Click Home (tab) -> Styles (group) -> Conditional Formatting -> New Rule…

Step 3: In the New Formatting Rule dialog box, select “Use a formula to determine which cells to format”.  Enter the cell address of the first cell that holds a true/false response (ex: =C2).

Step 4: Click the “Format…” button to open the Format Cells dialog box.  Set the style to whatever format pleases you.  I’m going to go with a strikethrough font that is a medium gray color.

Click OK to close the dialog box.

Testing the Conditional Formatting

Place and remove a check within each checkbox to see the Conditional Formatting feature applied to the related skill topic.

Getting Fancy with Checkboxes and Formulas

Any formula that can use a “True” or “False” in its logic can leverage checkboxes for increased functionality.

Creating a List of Outstanding Tasks

If we wish to have a list of tasks that are yet to be complete (or in this case, skill yet to be attained), we can use a FILTER function to create a list of tasks that are currently flagged as “False” in the checkbox column.

The FILTER function can use the true/false states of cells C2 through C6 to filter the list of items in cells B2 through B6.

We will write a formula that says, “If the state of a cell in column “C” is “False”, display the corresponding skill from column “B”.  If none of the cells in column “C” are “False”, then display the message “All Done ”.

=FILTER(B2:B6, C2:C6=FALSE, "All Done")

Check the boxes and observe the “Outstanding Tasks” list in comparison to the selected checkboxes.

The FILTER function is not looking at the checkboxes, it’s looking at the “True” and “False” entries in the cells behind the checkboxes to select the corresponding skill.

Let Your Imagination Run Wild

As you can see, adding checkboxes is really easy and provides a great means of providing interactivity to a spreadsheet.

Using the outcome of the checkbox to control the rest of your report is also very simple once you learn how to use the linked cell in your formula.

Practice Workbook

Feel free to Download the Workbook HERE.

Excel Download Practice file

Excel Dashboards that Inform & Impress

Use these techniques in your own reports

Unbeatable value!

LEARN MORE