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.

 

Screenshot showing where to find the Form Control Checkbox and the ActiveX Checkbox.

 

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.

 

How to Add the Developer Tab to the Ribbon

 

Click Ok.

The Developer Tab will now be on the Ribbon.

 

Screenshot showing the Developer Tab added to 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.

 

Screenshot showing the source data.

 

Step 1:

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

 

Screenshot showing the Insert option in the Controls Group on the Developer Tab, highlighted.

 

In the Form Controls section, select Check Box.

 

Screenshot showing the Check Box Form Control.

 

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

 

Screenshot showing the first checkbox on the sheet.

 

Step 2:

Right-click the Checkbox and select Edit Text.

 

Screenshot showing the Edit Text option highlighted.

 

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

 

Screenshot showing the checkbox with the text added.

 

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.

 

Screenshot showing the textbox resized and repositioned.

 

Step 3:

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

 

How to drag the checkbox down the column

 

Click on another cell to deselect everything.

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

 

Screenshot showing the Gridlines option unchecked.

 

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

 

Screenshot showing the student ticking off a checkbox once has has finished the respective book.

 

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:

 

Graphic showing the five highest priority tasks for the week.

 

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.

 

Screenshot showing the Column Width option highlighted.

 

Change the Column width to 2 and then click Ok.

 

Screenshot showing how to set the Column Width to 2.

 

You should see the following.

 

Screenshot showing the width of Column A set to 2.

 

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.

 

Screenshot showing the Dark Blue fill colour highlighted.

 

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.

 

Gif showing the heading being added and formatted.

 

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…

 

Screenshot showing the Stock Images... option highlighted.

 

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

 

Screenshot showing the search term Mobile Phone entered into the Search Box.

 

Select the following image.

 

Screenshot showing the image that was selected, highlighted.

 

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.

 

Screenshot showing the Crop option highlighted.

 

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.

 

Gif showing image being cropped to include only the mobile phone.

 

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

 

Screenshot showing where the image is placed.

 

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

 

Screenshot showing the Size and Properties option highlighted.

 

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″.

 

Screenshot showing the height of the image and the width of the image changed to new dimensions.

 

You should see the following.

 

Screenshot showing the resized image.

 

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.

 

Screenshot showing the image in PowerPoint.

 

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

 

Screenshot showing the rectangle on the slide.

 

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)

 

Gif showing how to use the Eye Dropper tool to get the RGB colour code.

 

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.

 

Screenshot showing the rectangle on the spreadsheet.

 

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.

 

Screenshot showing the More Fill Colors...option highlighted.

 

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).

 

Screenshot showing the RGB section highlighted.

 

Click Ok.

 

Screenshot showing the rectangle with a new fill.

 

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.

 

Screenshot showing the No Outline option highlighted.

 

The effect of doing this is the following.

 

Screenshot showing the effect of No Outline.

 

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

 

Screenshot showing the Selection Pane option highlighted.

 

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.

 

Screenshot showing both objects selected.

 

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

 

Screenshot showing the Group option highlighted.

 

The two objects can now be moved as one object.

 

Screenshot showing the Grouped Objects.

 

Step 6:

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

 

Screenshot showing the Textbox option highlighted.

 

Draw a Textbox on the mobile phone.

 

Screenshot showing the Textbox added to 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.

 

Screenshot showing the textbox with no fill and the outline removed.

 

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.

 

Screenshot showing the Checkbox added.

 

Right-click the Checkbox, and select Edit Text.

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

 

Screenshot showing the first checkbox with the text added.

 

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

 

Screenshot showing the Format Control... option highlighted.

 

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.

 

Gif showing how to use the Format Control Dialog Box.

 

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

 

Screenshot showing the value of FALSE placed in cell F2, when the Checkbox is unchecked.

 

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.

 

Screenshot showing the duplicated Checkbox.

 

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.

 

Screenshot showing the position of the duplicated Checkbox.

 

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.

 

Screenshot showing the value of TRUE placed in cell F3 when the second checkbox is checked.

 

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.

 

Screenshot showing the third Checkbox added.

 

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.

 

Screenshot showing the value placed in cell F5 when the fourth Checkbox is checked.

 

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.

 

 

Screenshot showing a value of TRUE being placed in cell F6 when the fifth Checkbox is checked.

 

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.

 

Screenshot showing all five checkboxes selected.

 

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.

 

GIF showing how to left-align all the Checkboxes and make the vertical spacing between them equal.

 

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

 

Screenshot showing all the Checkboxes left-aligned and the vertical spacing between them made 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.

 

Screenshot showing the result of the COUNTIF formula in cell F10, when the Checkbox is checked.

 

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. 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.

 

Screenshot showing the result of the formula in cell F11, when 4 Checkboxes are checked.

 

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.

Screenshot showing the result of the formula in cell F12, when four Checkboxes are checked.

 

 

    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.

 

Screenshot showing the selected image highlighted.

 

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.

 

Screenshot showing the cropped image.

 

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.

 

Screenshot showing the New 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.

 

Screenshot showing the first textbox added to the second graphic.

 

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.

 

Screenshot showing the second textbox with the text Tasks Completed:.

 

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″.

 

Screenshot showing the rectangle added.

 

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.

 

GIF showing how to link the value in cell F10 to 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.

 

Screenshot showing 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.

 

Screenshot showing the formatted rectangle.

 

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.

 

Screenshot showing the second rectangle formatted.

 

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.

 

Screenshot showing the third rectangle formatted.

 

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.

 

Screenshot showing the notepad and the textboxes and rectangles on it selected.

 

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.

 

Screenshot showing the position of the Notepad graphic.

 

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.

 

Screenshot showing the Hide Columns option highlighted.

 

The effect of doing this is the following.

 

Screenshot showing the effect of hiding the columns from L onwards.

 

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.

Screenshot showing the effect of hiding the rows from 22 onwards.

 

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.

 

Screenshot showing the effect of removing the Gridlines and the Headings.

 

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

 

GIF showing how the Weekly Planner functions.

 

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

 

Screenshot showing the Zoom level set to 110% of the Weekly Planner spreadsheet.

 

Learning Objectives

You now know how to:

  • Insert a Form Control Checkbox.
  • Create a Simple To-do List Using Checkboxes.
  • Create a Simple Weekly Planner Using Checkboxes and Stock Images

 

Additionally, you have an understanding of:

  • How to take a concept and produce a visualization based on that concept, integrating Excel analytical techniques.
  • How to create user-friendly spreadsheets

 

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.