- 1 How to activate the Check Box-Option in the Ribbon
- 2 How to add checkboxes to cells
- 3 How to link checkboxes to cells
- 4 How to use connected checkboxes and formulas
- 5 How to create a To-Do list with check boxes and conditional formatting
- 6 Extra: How to Lock Cells in Excel to Protect from Editing
- 7 Extra: How to Find Duplicates in Excel and Remove Their Rows
A checkbox is a simple control that I'm sure everybody will have encountered online, often as part of a cookie dialog or where you'll tell a site to remember you being logged in. Checkboxes in excel are much the same thing, but you may not be aware of how useful they can be.
These are members of a family of control elements called Form Controls and they are one of the ways in which you are able to easily interact with a website or an application on your computer. You may know of them as check boxes, checkmark boxes, or even just tick boxes, but they're boxes that you can click on to add or remove a check symbol or tick in order to show that something has been selected or deselected.
Reasons to insert a checkbox in Excel
Although they might not sound like much, an Excel check box can allow you to enter simple yes/no or on/off data with a click instead of needing to select a cell and enter your data using the keyboard. This makes it easy to set up worksheets to track things like goals, assignments, or even as we will show you in this article, a shopping list.
You may not have thought to add a checkbox in Excel before, but these are a simple and effective way to add functionality to your worksheets and make binary data easy to set up and read.
How to activate the Check Box-Option in the Ribbon
As checkboxes (and other form controls) aren't displayed on the ribbon menu by default, you may wish to add them to make them easily accessible for you. Once added to the ribbon, you will be able to add a checkbox to your worksheet with only three clicks.
You'll find checkboxes under Form Control under the Developer tab, which you will enable in the Customize Ribbon options.
- Open the “File” tab
- Select the “More…” menu and choose “Options”
- Select “Customize Ribbon”
- (Alternative method) Right-click on the Ribbon menu, and select “Customize Ribbon”
This will also open the Customize Ribbon dialog window and can be used instead of going through the File tab.
- Select “Main Tabs” and enable the “Developer” tab
Make sure that the “Customize the Ribbon” dropdown menu has “Main Tabs” enabled, and then click in the checkbox next to “Developer” to activate that tab on your ribbon menu. Once this has been done, click on the “Okay” button at the bottom of the dialog window.
- Check that the “Developer” tab is now being displayed on your ribbon menu
With the new tab now showing on your ribbon menu, we can move on to the next section where we will show you how to add a checkbox in Excel.
How to add checkboxes to cells
The steps required to add a checkbox to your worksheet are fairly straightforward. The only aspect you may find a little unintuitive is the process of positioning it correctly, but we will touch on that later in this section.
- Click on the “Developer” tab, then select the “Insert” icon, and click on the checkbox icon under “Form Controls”
- Click where you wish to add the checkbox
Don't worry about placing it precisely yet, as you will be adjusting its position in the next step.
- Click and drag to position the checkbox precisely
Move your cursor over the edge of the selection box for your checkbox, and when it changes to the move pointer you can click and drag the element to position it accurately on your sheet.
- Right-click on the checkbox, and select “Edit Text”
For this example, you will be removing the “Check Box 1” text completely but you are able to change what is displayed alongside the control if you wish.
- Highlight the “Check Box 1” text and delete it
- Select “Design Mode”
Note: If the Design Mode option is unavailable and cannot be selected, you may need to open the Properties window immediately to the right of the Design Mode icon, which is the top-most of the three icons, to enable it.
- Duplicate your checkbox
Using your arrow or cursor keys, select the cell which contains your checkbox – clicking on the checkbox will enable and disable it, so this is a simpler way to select it. Once selected, move your cursor over the bottom-right corner of the cell until the cursor changes to a bold plus sign (+), then click and drag down to select the empty cells below the highlighted checkbox. When you release the mouse button, your checkbox will be copied into all of the cells you've selected.
- You now have checkboxes added to your sheet, ready to be used
As you can see, you now have checkmarks on your sheet ready to be used, but you'll also see that switching them on and off doesn't make any changes to the content of the sheet itself. In the next section, we will show you how to link your checkboxes to cells on your sheet in order to capture the state of individual boxes and make that information accessible to your formulas.
How to link checkboxes to cells
Now that you know how to add a checkbox, the next step is showing you how to connect it to a cell in order to make the control interactive, and allow the state of the checkbox to be stored on your sheet.
- Right-click on the checkbox you wish to link, and select “Format Control”
- Select the cell to be connected to the checkbox
Make sure the “Control” tab is selected on the “Format Control” dialog window, click in the “Cell link” field, then select the cell you wish to connect to this checkbox. Once selected, you will see the cell location shown in the field, and you can click on “Ok” to confirm the link.
Note: you may need to click outside of the selection box of the checkbox – in this example clicking on the right-hand area of the cell – as its selection box may block your mouse click.
- Repeat for each checkbox, then test by clicking the checkbox on and off
How to use connected checkboxes and formulas
Now that the checkboxes have been linked to cells on the sheet, you will set up two formulas that will use the contents of those cells to display useful information for you.
- Use the COUNTA function to show a count of the number of items in a selection
This formula will display the total number of cells in the selected range. Begin by selecting the cell where you wish to display the result, then in the formula bar enter this:
- Use the COUNTIF function to display the number of items in your selection that match a specific criteria
Unlike COUNTA which counts all cells in the range selected, COUNTIF will allow you to count only the cells which match the criteria set up in your formula – in this case, the cells in the selection which have the value of TRUE. Locate the cell connected to the last checkbox (E10 in this example), and select the cell directly below it and then enter this into the formula bar:
- Use COUNTIFS to count two or more ranges of cells that are each subject to different criteria
COUNTIFS will allow you to count the contents of cells in more than one selection, with each selection having its own criteria. In this example, the formula will check that the cell in column C is not blank or empty and that the accompanying value in column E is equal to TRUE; it then displays the total count of cells where these two conditions are true. Select the cell where you wish to display this result, then in the formula bar enter this:
How to create a To-Do list with check boxes and conditional formatting
In this last section, you'll be shown how to create a To-Do list using conditional formatting to present the information from your sheet in a very easy-to-read manner. In this example, we will set up a list showing which vegetables need to be bought.
- Select the list of items and open the “Conditional Formatting” dialog window, then click on “New Rule…”
Note: This menu item is located on the “Home” tab, so be sure to click on that once you're selected all the list items.
- Select the “Use a formula” rule, then enter the formula which will trigger the format change and click on “Format”
- Change the color and enable “Strikethrough”, then click on “Ok”
- Click on “Ok” to save your formatting rule
- Confirm that the text is now being displayed correctly.
If your conditional formatting has been set up correctly, items that have the check box enabled will be displayed with strikethrough applied to the text of the item name, and the font color changed to a medium gray. Disabling a check box will revert the font color and style to normal.
- Add a new formula to display a message dependent on the checkbox status
Select the cell to the right of the top check box, and enter this into the formula bar:
- Select this cell and drag it to autofill the formula into the cells below
Click on the cell to select it, then move your cursor over the bottom-right corner until the cursor changes to a bold cross (+) and click and drag down to select the empty cells below. When you release the mouse button, the formula will be copied into all of the cells you've selected.
- Check that your To-Do list is working as intended
Your list should now be complete, and enabling and disabling individual checkboxes will change the font color and formatting in the cell to the left-hand side of each, and will display a simple message in the cell to the right indicating whether you need to buy more of each.
Extra: How to Lock Cells in Excel to Protect from Editing
Even if you aren't sharing a spreadsheet, learning how to lock cells in Excel can save you a lot of trouble. No matter how proficient you are with the software, it's easy enough for you or a pet to lean on a key and break your formulas.
Extra: How to Find Duplicates in Excel and Remove Their Rows
Creating duplicate content is so common that Microsoft's spreadsheet software has an in-built Remove Duplicates tool. This isn't always the best way to delete duplicate rows in Excel, however – sometimes the find and replace or advanced filtering options will be more efficient.