Form Controls: How To Use A Checkbox In Excel

In this article, we will show you how to use form control Checkboxes in your spreadsheets. You can utilise Checkboxes, to make your spreadsheets more interactive. An Excel form control tick box is easier in some instances, to use than ActiveX controls.

The reason being that you don’t need to use VBA with form controls.

If you would like to learn more, about how to use form controls in your spreadsheets then please consider joining one of our top-rated Excel courses.

 

What is a Checkbox?

A Checkbox is a small square, which can be either checked or unchecked.  You will often see Checkboxes on web forms or when filling in surveys.

There are two types of Checkboxes one can utilise in Excel. The form control Checkbox relies on formulas and linked cells for functionality. The ActiveX Checkbox relies on VBA for functionality. In this tutorial we are going to focus on form control checkboxes.

 

 

Checkbox Use Cases

You can use Checkboxes to create:

• simple to-do lists
• simple daily and weekly planners
• interactive charts, reports and dashboards

 

Using The Developer Tab

You have to add the Developer Tab to the Ribbon first, in order to insert and use Checkboxes.

Step 1:

To do this, go to the File Tab and select Options. Select the Customize Ribbon option. Ensure the Developer option is checked.

 

 

Click Ok.

The Developer Tab will now be on the Ribbon.

 

 

Adding A Checkbox To Your Sheet

In our first example, a hypothetical student has his desired reading list for 2022 on a spreadsheet. As soon as he finishes a book on the list, he wants to tick it off, using a Checkbox.

The source data is shown below.

 

 

Step 1:

So go to the Developer Tab, and in the Controls Group, click  Insert.

 

 

In the Form Controls section, select Check Box.

 

 

Draw a checkbox on the sheet (you can place it anywhere you like), in this case it is in cell C4.

 

 

Step 2:

Right-click the Checkbox and select Edit Text.

 

 

Now you can either delete the text entirely, or in this case we will type Finished.

 

 

Now working with a Checkbox can be a bit tricky at first. In order to select the Checkbox, hold the CTRL key on your keyboard first and then left-click the Checkbox.

You can reposition the Checkbox by selecting it and then using the arrow keys on your keyboard, to move it to where you want it to be. Additionally, you can resize the Checkbox by dragging the handles.

We have resized and repositioned the Checkbox as shown below.

 

 

Step 3:

Now you can copy the Checkbox to the other cells, by selecting cell C4 and then dragging down the column.

 

 

Click on another cell to deselect everything.

Now go to the View Tab and in the Show Group, uncheck Gridlines.

 

 

Now as soon as the student finishes a book on his reading list, he simply ticks the Checkbox.

 

 

Checkboxes – Using Conditional Logic

We will now look at how you can use Checkboxes for more sophisticated needs. We will build on what you learned in the previous example.

In this example, an executive admin assistant, working for a sales and marketing company, is investigating a certain time management technique. She wants to manage her time more effectively and efficiently.

If you are interested in learning more about different time management techniques, then please visit this post.

She has prioritised five key tasks for the week. She wants to use an Excel spreadsheet with Checkboxes to help her manage her schedule. So we will see how to use Excel to create a Weekly Planner to track her progess.

 

Step 1:

She starts by evaluating her needs in more detail. She has five high priority tasks for the week.  If she completes four or five tasks for the week, then she would like to have a value returned saying Good Job.

If she completes three or less of her high priority tasks for the week, then she would like to have a value returned saying Needs Improvement.

Additionally, she would like to design her Weekly Planner on her spreadsheet, in a visually appealing, user-friendly manner.

The five high priority tasks are:

 

 

Step 2:

We’ll start with some basic formatting and adding our first heading to the spreadsheet.

So, select Column A. Go to the Home Tab and in the Cells Group, select Format. Select the Column Width option.

 

 

Change the Column width to 2 and then click Ok.

 

 

You should see the following.

 

 

With Column A still selected, go to the Home Tab, and in the Font Group, select the Fill Color option. Fill the entire column with Dark Blue.

 

 

Tip: It’s good practice when designing a spreadsheet, to add a heading. A heading helps to orientate and inform the user.

It can be quite confusing when opening a workbook and seeing multiple spreadsheets with data. It’s the same reason why one adds headings to a report that one is creating in Word, otherwise readers of the document would be quite confused.

Type the text WEEKLY PLANNER in cell B1. Afterwards select cell range B1:J1. Go to the Home Tab, and in the Alignment Group, select Merge and Center.

Change the font to Tenorite and make the font weight bold.

Note: Tenorite is a new font, which is available for Office 365 subscribers. For those using older versions of Office, you can use a standard Sans Serif font such as Calibri.

 

 

Step 3:

We want to insert the first image. So go to the Insert Tab, and in the Illustrations Group, select Pictures. Choose Stock Images…

 

 

Select the Illustrations Tab and type Mobile Phone in the Search Box.

 

 

Select the following image.

 

 

Click Insert.

 

Step 4:

We will crop the image, so that only the mobile phone is shown. So select the image. Go to the Graphics Format Tab and in the Size Group, select Crop.

 

 

We will increase the Zoom level of the spreadsheet at this point to 130%, in order to crop the image accurately. We want to crop the image so that only the mobile phone is included.

 

 

You can now change the Zoom level of the spreadsheet back to 100%. Move the image to the position shown.

 

 

Now select the image and go to the Graphics Format Tab. Click to see the Format Graphic Dialog Box.

 

 

Using the Format Graphic Dialog Box, uncheck the Lock aspect ratio option. Change the height of the image to 4.05″ and the width to 2.3″.

 

 

You should see the following.

 

 

Step 5:

We now want to cover the two white rounded-corner rectangles on the phone itself. Now we could insert a rectangle of another colour over the two rounded-corner rectangles.

However, we want the effect to be seamless in a way, so we need the rectangle to be the same colour, with it’s outline removed.

Excel does not have an Eye dropper tool.

So to match the colours exactly, we will make use of the Eye dropper tool in PowerPoint.

• • So, open PowerPoint and create a Blank Presentation.
  • Then go back to your Excel spreadsheet and press the Print Screen button on your keyboard, in order to take a screenshot.
  • Then go back to PowerPoint and press CTRL-V on your keyboard in order to past the screenshot.

 

 

While still in PowerPoint, insert a shape on the slide. It can be any shape. In this case we used a rectangle.

 

 

Now select the rectangle. With the rectangle selected, go to the Shape Format Tab, and in the Shapes Styles Group, select Shape Fill. Choose the Eye Dropper.

Hover over the grey colour that colours the bottom half of the mobile phone. Note the RGB code given which in this case is RGB (210, 210, 210)

 

 

Now close PowerPoint and go back to your Excel spreadsheet.

Insert a rectangle that covers the two white rounded-corner rectangles on the mobile phone.

 

 

With the rectangle selected, go to the Shape Format Tab and in the Shape Styles Group, select Shape Fill.

Choose the More Fill Colors… option.

 

 

Using the Colors Dialog Box, in the Custom Tab, change the Red, Green and Blue Values to 210, (remember our RGB code was RGB(210, 210, 210).

 

 

Click Ok.

 

 

Now we want to remove the blue outline. So select the rectangle again and in the Shape Format Tab, in the Shape Styles Group, select Shape Outline.

Select No Outline to remove the blue outline.

 

 

The effect of doing this is the following.

 

 

Now we want to group the mobile phone image and the rectangle we just created, so that we can move it as one object.

To do this, go to the Home Tab,  and in the Editing Group, select Find and Select. Choose Selection Pane…

 

 

Using the Selection Pane. Select both the mobile phone and the rectangle by clicking on the Rectangle object first in the Selection Pane.  While holding the CTRL key on your keyboard, select the Graphic object by clicking on it in the Selection Pane.

 

 

Now go to the Graphics Format Tab and in the Arrange Group, select Group. Choose Group.

 

 

The two objects can now be moved as one object.

 

 

Step 6:

Now go to the Insert Tab, and select the Text Group and then choose Text Box.

 

 

Draw a Textbox on the mobile phone.

 

 

Type the text TASKS in the Textbox. Centre the text. Change the font to Tenorite and the font weight to bold.

(If you are using an older version of Office then you can use any Sans Serif font such as Calibri)

Remove the fill from the Textbox and make sure there is No Outline.

The result is the following.

 

 

Step 7:

We will now add our first Checkbox. So, go to the Developer Tab and in the Controls Group, click Insert. In the Forms Control section, choose Check box.

Now draw a Checkbox on the mobile phone.

 

 

Right-click the Checkbox, and select Edit Text.

Change the text to reflect the first task, which is Organise Desk.

 

 

Now right-click the Checkbox and select the Format Control… option.

 

 

Using the Format Control Dialog Box, in the Control option, under Cell link:, select cell F2. Click Ok.

Now if you tick the Checkbox, the value of TRUE will be placed in cell F2.

 

 

Subsequently, if you decide to uncheck the Checkbox, a value of FALSE will be placed in cell F2.

 

 

Now select the Checkbox, by pressing CTRL on your keyboard first, and then selecting it with the left mouse key.  Now with the Checkbox selected, press CTRL-D on your keyboard in order to duplicate it.

 

 

Select, the duplicate Checkbox and move it to the position shown. You’ll note it is not exactly aligned with the first Checkbox, but we will sort this out later.

 

 

Right-click the second Checkbox and choose Edit Text. Change the text to Send leads to Sales.

Now right-click this Checkbox again and select Format Control…Using the Format Control Dialog Box, in the Control Tab, change the cell link of this second Checkbox to cell F3.

If you check this Checkbox, then the value of TRUE will be placed in cell F3.

 

 

You can uncheck the second checkbox if you’d like to.

Duplicate the second checkbox. Move the third checkbox below the second one. Change the text of the third checkbox to Assist new hires. Change the cell link of the third checkbox to cell F4.

Now if you check the third Checkbox, you should see a value of TRUE being placed in cell F4.

 

 

You can uncheck all the checkboxes.

Duplicate the third Checkbox. Move the fourth Checkbox below the third one. Change the text of the fourth textbox to Organise files. Change the cell link of the fourth checkbox to cell F5.

Now if you check the fourth Checkbox, you should see a value of TRUE being placed in cell F5.

 

 

You can uncheck all the checkboxes.

Duplicate the fourth Checkbox. Move the fifth Checkbox below the fourth one. Change the text of the fifth textbox to Order office supplies. Change the cell link of the fifth checkbox to cell F6.

Now if you check the fifth Checkbox, you should see a value of TRUE being placed in cell F6.

 

 

 

We will now align all the Checkboxes, we just created and make the vertical spacing between them equal.

To do this, we will need to select all the Checkboxes. Click on the the CTRL key first and while holding the CTRL key, click on each Checkbox.

 

 

With the Checkboxes selected, go to the Shape Format Tab, and in the Arrange Group, select Align. Choose Align Left.

Select Align again and this time choose the Distribute Vertically option.

 

 

The effect of doing this will be that all the Checkboxes are left-aligned and the spaces between them will be equal.

 

 

Step 8:

In this step, we will introduce conditional logic by using the COUNTIF formula. In cell F10 type the following formula:

=COUNTIF(F2:F6,TRUE)

This formula counts all the values, in the range that are TRUE. So it will reflect the number of Checkboxes that have been ticked.

If you tick one Checkbox, then the formula will return 1.

 

 

If you would like to learn more about the COUNTIF Function, then please read this post.

In cell F11, enter the following formula:

= IFS(F10=0,””,F10<=3,”Needs Improvement”,TRUE,”Good Job”)

1. 1. When the executive admin assistant starts the week, no tasks, have as yet been completed. So when none of the Checkboxes have been ticked, we’d like no value in cell F11.
   2. If the executive admin assistant by the end of the week has ticked 1, 2 or 3 Checkboxes, then we’d like a value of Needs Improvement to be returned in cell F11.
   3. If the executive admin assistant completes 4 or 5 tasks for the week, then we’d like a value of Good Job, returned in cell F11.

 

 

Now we will enter another conditional formula in cell F12.

=IFS(F10=0,””,F10<=3,”D”,TRUE,”C”)

With cell F12, selected change the font to Wingdings.

 

 

1. 1. When the executive admin assistant starts the week, no tasks, have as yet been completed. So when none of the Checkboxes have been ticked, we’d like no value in cell F12.
   2. If the executive admin assistant by the end of the week, has ticked 1, 2 or 3 Checkboxes, then because we used the Wingdings font, a thumbs down will be returned in cell F12.
   3. If the executive admin assistant completes 4 or 5 tasks for the week, then because we used the Wingdings font, a thumbs up will be returned in cell F12.

 

Step 9:

We will now insert another image.

So go to the Insert Tab and in the Illustrations Group, select the Pictures option.

Choose Stock Images…

Select the Illustrations Tab. Enter Note as the Search term and select the following graphic.

 

 

Click to Insert the Image. Crop the image as shown below since we don’t need all the extra space and this will make the image easier to work with.

 

 

Resize the image to make it smaller, the dimensions should be 3.95” by 3.86”

With the image selected, go to the Graphics Format Tab, in the Graphics Styles Group, choose the Graphics Fill option. Select the Orange, Accent 2 fill.

 

 

Insert a Textbox in the position shown below, change the text to PROGRESS REPORT. Change the font to Tenorite and the font weight to bold. Centre the text, and ensure that the outline of the Textbox is removed.

 

 

Add another Textbox in the position shown below with the text Tasks Completed:. Change the font to Tenorite and the font weight to bold. The size of the font should be 10. Remove the outline from the Textbox.

 

 

Go to the Insert Tab, and in the Illustrations Group, click on the Shapes option. Select rectangle. Insert a rectangle in the position shown. The dimensions of the rectangle should be 0.3″ by 0.3″.

 

 

Select the rectangle. With the rectangle selected, type = in the Formula bar and then select cell F10. Press Enter on the keyboard. By doing this the value in cell F10, will be reflected in the rectangle.

 

 

With the rectangle still selected, go to the Shape Format Tab and in the Shape Styles Group select the Subtle Effect – Blue, Accent 5 Theme Style.

 

 

Centre and middle align the text in the rectangle. Change the font to Tenorite and make the font colour black. Change the font weight to bold.

 

 

Insert another rectangle in the position shown below. The dimensions of this rectangle should be 0.29″ by  1.45″. Link this rectangle to cell F11, so that the value in cell F11 is reflected in the rectangle.

With the rectangle still selected,  go to the Shape Format Tab and in the Shape Styles Group select the Subtle Effect – Blue, Accent 5 Theme Style.

Change the font to Tenorite. Make the size of the font 9.5. Change the weight of the font to bold. Set the colour of the font to black.

Don’t forget to centre and middle align the text.

 

 

Now add another rectangle in the position shown below. The dimensions of this rectangle should be 0.55″ by  0.55″. Link this rectangle to cell F12, so that the value in cell F12 is reflected in the rectangle.

Remove the fill and the outline from this rectangle. With the rectangle still selected, centre and middle align the font and change the font size to 40. Set the font colour to black. Keep the font as Wingdings in this case.

 

 

Now select the Notepad and all the textboxes and rectangles on the Notepad. By clicking on the CTRL key and then selecting the graphic, textboxes and rectangles while holding the CTRL key.

 

 

Go to the Graphics Format Tab and in the Arrange Group, select Group. Then select Group again.

This will allow you to move the Notepad, the textboxes on the Notepad and the rectangles on the Notepad as one object.

Step 10:

This step is really all about improving the aesthetics of our spreadsheet.

So select cell range F2:F12 (this is the range where our formulas are) and change the font colour to white.

Move the Notepad graphic to the position shown below.

 

 

Now select all the columns from L onwards. The way to do this, is by first selecting column L and then pressing CTRL-SHIFT-Right Arrow on the keyboard.

With all the columns from L onwards selected, go to the Home Tab, and in the Cells group, choose Format. Under the Visibility section. Choose Hide and Unhide. Select Hide Columns.

 

 

The effect of doing this is the following.

 

 

Select all the rows from row 22 onwards. The way one does this, is to select row 22 first and then press CTRL-SHIFT-Down Arrow key on the keyboard.

With the rows from 22 onwards selected, press CTRL-9 on the keyboard in order to hide these rows.

The effect of doing this is the following.

 

By hiding the unused columns and rows, we ensure that the user’s eyes are focused on our key graphics. This also helps prevent others, from using other parts of the spreadsheet for other data/formulas and charts.

Now go to the View Tab, and in the Show Group, uncheck Gridlines and Headings. You can also uncheck the Formula Bar if you would like to.

The effect of doing this is the following.

 

 

Now let’s see the Weekly Planner in action below.

 

 

Delete Your Checkbox!

Let’s say you want to delete a checkbox you created, there are a few ways to do this. We will look at the simplest way of accomplishing this.

Select the Checkbox that you would like to delete, by holding down the CTRL key and then left-clicking the Checkbox of interest.

Then simply press the Delete key on your keyboard and the Checkbox will be deleted.

 

Troubleshooting

Let’s discuss what to do when things are not working the way you expected, with your form control checkboxes.

• Firstly establish that it is a form Control checkbox and not an ActiveX Checkbox. If you right-click on the Checkbox and the menu has an Edit Text option then it is a form control Checkbox. If you right-click the Checkbox and the menu has a Properties option then it is an ActiveX control.

 

• Check your cell link. If you drag a form control down a column, and you added a cell link to the first one, then all the others will be linked to that same cell. So you will have to change each cell link individually of each of those checkboxes, if you intend to use conditional formulas.

 

• If you find it’s difficult to see the Checkbox or work with the Checkbox because it’s too small, unfortunately there is no way to change the font or size of the actual square currently. A workaround involves increasing the Zoom level of the entire sheet. This is the Zoom level set to 110% of the Weekly Planner spreadsheet

 

 

 

Conclusion

In this tutorial we have shown you how to produce a visualization from a concept by incorporating Checkboxes. You can use form control Checkboxes to design dashboards, reports, and Excel based widgets.

Looking for more Excel tips, then please read our guide on creating a Gantt Chart in Excel here.