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.
Featured Course
Excel Essentials for the Real World
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.
Featured Course
Master Excel Power Query – Beginner to Pro
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.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
