How to Create a Checklist in Excel | Interactive Checklist Template with Checkboxes

This tutorial explains how to create checkboxes in Excel.  It also explains how to automatically change the row colour if the row contains a checked checkbox.  In addition, the video above shows you how to create a % complete bar – so it may be worth a watch!

You can download the featured file here.

Create a CheckBox

The first step is to show the Developer tab on the Ribbon.

1. Right-click on one of the available Ribbon tabs and select Customize the Ribbon… In the list on the right of the Excel Options dialog box, tick Developer.  Click OK to confirm.
2. On the Developer tab, click the Insert button and under Form Controls select Check Box and then click into the cell that you want the checkbox to appear in.  Delete the text next to the checkbox – you don’t need it.
3. Resize the container surrounding the checkbox so it fits neatly inside the cell – you can do this by dragging the white circles.
4. Once the container is resized, click into another cell (any cell) and then click back in the cell that contains the checkbox.
5. You can now copy the checkbox to other cells by dragging the green square, located bottom-right of the cell.
6. If you need to reposition any checkboxes within a cell, right-click in the cell so that the resize handles for the checkbox container reappear.  You can then use the arrow keys on your keyboard to reposition the checkbox.  If there are any checkboxes that you don’t need, right-click in the cell that contains the checkbox and select Cut from the short-cut menu.

Link Each Checkbox to an Adjacent Cell

Each checkbox needs to be linked to an adjacent cell.  This adjacent cell will contain TRUE if the checkbox is ticked and FALSE if it isn’t.

1. Right-click on your first checkbox and select Format Control in the shortcut menu, then select the Control tab in the dialog box.
2. Click into the Cell link: box and then select a cell on your worksheet.  In my example the checkboxes are in column B, so I chose an adjacent cell in column C.
3. Click on OK to confirm. A ticked checkbox will now return TRUE in the linked cell.  An unticked checkbox will return FALSE in the linked cell.
4. Repeat steps 1 to 3 for each checkbox in your worksheet.

Get the Row Background Colour to Change When the Checkbox is Ticked

You can get the row background colour to automatically change when a checkbox in that row is ticked.  This can be achieved using conditional formatting.

1. Select the range of cells that you want to apply the conditional formatting to – this can include the cells that contain the checkboxes.  Make sure the first cell that you select is in the same row that contains the first checkbox.
2. On the Ribbon’s Home tab, click on the Conditional Formatting button ( it appears in the middle of the Ribbon), and select New Rule from the menu.
3. In the Select a Rule Type: list select Use a formula to determine which cells to format
4. Click in the box titled Format values where this formula is true:  and then select the cell that is linked to the first checkbox in your worksheet. In my example my first checkbox is in B2, the cell it is linked to is C2, so in this step I selected C2.  The cell reference will appear with dollars in the dialog box, like this: =$C$2.  You now need to delete the second dollar in the cell reference.  In my example, the cell reference  ended up appearing like this =$C2 By deleting the second dollar, but keeping the first I am telling Excel that the condition for my formatting will always be found in column C (I’ve locked column C) but in different rows in column C (I’ve not locked row 2).
5. You now need to set your format, so click on the Format… button and then select the Fill tab in the Format Cells dialog box.  Select a background colour, then click on OK to confirm and then OK again.
6. Check the conditional formating works by ticking a checkbox: the row colour should change.

Hide the TRUE and FALSE Column

The column that contains your linked cells (currently displaying TRUE and FALSE values) can be hidden as it is only needed in the background by the conditional formatting.  To hide the column, right-click on the column letter at the top of the column and select Hide in the shortcut menu.