In this blog post, we will learn how to create a check box form control in Excel, use it to receive user input and store it in a cell.

A sample implementation is below.

Final checkbox appearance
Final checkbox appearance

We want to allow the user to choose the days that are weekend days.

Let’s type Day and Sunday.

If you select the Sunday cell and drag it down to 6 more cells, Excel will automatically populate the other weekdays.

Fill the cells down
Fill the cells down

 

Excel fills the series for us to help with data entry, as it knows the common series such as days of week, months in a year, etc.

Fill Series
Fill Series

Check Box does not work in Excel Online yet. So, if you plan to use your file online to edit in your browser, then please do not use this technique. Instead you can use the drop-down list technique.

 

1. Enable Developer Ribbon

Click on File –> Options.

File Options
File Options

Select Customize Ribbon on the left sidebar and then check the box next to ‘Developer’ on the right side.

Select Developer ribbon
Select Developer ribbon

 

Press OK.

Now Developer ribbon appears.

Developer ribbon appears
Developer ribbon appears

 

2. Insert Check Box control

Insert Check Box control.

Insert Check box control
Insert Check box control

Then, click where you want the box to be and drag to create the check box.

Create first check box
Create first check box

Right click on the check box and then select Edit Text.

Right click and select Edit Text
Right click and select Edit Text

Remove the default text in the box. This is usually difficult to format. We will not need that text as we will use the text that we typed already in the previous column.

 

Check box with no text
Check box with no text

3. Link Check Box to a cell

Right Click and select Format Control.

Right click and select Format Control
Right click and select Format Control

Now, we want to establish the link from the Check Box control to a cell.

Select the Cell Link
Select the Cell Link

In the Control tab, select cell $F$4 as Cell link.

Now that we have created the link, when you click on the check box to select, the cell F4 will display TRUE.

Selecting the check box will return TRUE
Selecting the check box will return TRUE

If you click on the box again to uncheck or deselect, then the text display in cell F4 says FALSE.

Unchecking the check box will return FALSE
Unchecking the check box will return FALSE

This is how we can let the Check Box control change the value. If it is checked, Sunday is a weekend, if it is not checked, it is a working day.

Weekday selection column
Weekday selection column

4. Copying Check Boxes

Now, we need to create this for other 6 days.

  • Select cell F4 and press Ctrl+C to copy.
  • Now, select cells F5 to F10.
  • Press Ctrl+V to Paste
Create 6 more checkboxes
Create 6 more checkboxes

All the new checkboxes will also be pointing to the same cell F4.

We must change each of them to point to a different day (checkbox next to Monday should point to cell F5 (Monday).)

Change cell link in all the checkboxes
Change cell link in all the checkboxes

After changing all the check boxes, we will change the font color to white.

Change font color to make the text invisible
Change font color to make the text invisible

This is done only for cosmetic purposes. Check box itself visually shows selected or not. So, we don’t need the display of TRUE or FALSE as well. Though the value is necessary for calculations, we don’t need it visible.

We will have the following appearance which is much cleaner.

Final checkbox appearance
Final checkbox appearance