How To Create A ToDo List In Excel With Checkboxes & Conditional Formatting

Here’s how you can create and keep track of your ToDo list in Excel using check boxes and conditional formatting:

You can download the file here to follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Today we want to create a ToDo List in Excel and set it up so that when we check the box next to any of the tasks, it will format that task in red and strike it through like this:

td1

The first step is to create the data range where we will list the tasks and insert the check boxes:

td2

Next, I’ll position my cursor where I want to insert the first checkbox, then go to the Developer tab in the ribbon, click on the down arrow under “Insert” and select the Check Box form control:

td3

(Note: If you don’t have a Developer tab on your ribbon, go to File => Options => Customize Ribbon and make sure the box is checked next to Developer in the right hand column. This should add that option.)

Once you’ve clicked on the Check Box form control, go to the cell where you want to insert the first check box and click and drag your cursor in that cell:

td4

You can now position your cursor over it and move it so it is centered where you want it. Also, right click on the check box and select Edit Text to delete the current text.

Next, using the cursor movement keys, select the cell with the check box, copy it, and paste it to the other cells below:

td5

The next step is to associate each check box with a linked cell that we can use to activate the conditional formatting. To do so, I will insert a row between the check boxes and the tasks:

td6

Now, I will right click on each check box and click on Format Control:

td7

A dialog box will appear. In the Cell link box either type in the cell next to each check box, or use the cell selector at the right to choose the cell. Do that for each check box. Once that’s completed, click on each check box. “TRUE” will appear when each is checked, and “FALSE” will appear when unchecked:

td8

Now we need to format the list of tasks so that when a box is checked and TRUE appears in column J, it will format the cell in red with a strikethrough. Highlight the tasks and go to Conditional Formatting on the Home tab. Click on New Rule:

td9

Click on the last option of Select a Rule Type – “Use a formula to determine which cells to format” and enter the formula: =J2=TRUE. Make sure that the cell reference is in relative format, not absolute (e.g. $J$2). This way the conditional formatting rule will check the cell next to each task rather than just the first cell:

td10

Next, click on the Format button and choose the formatting style you want to occur when the checkbox is checked:

td11

Now when you check off each task, the formatting will occur:

td12

Finally, hide the TRUE/FALSE column (you could have also put that column on another sheet) and your ToDo list is done:

td13

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , ,

Trackbacks/Pingbacks

  1. Checkbox Form Control - September 8, 2014

    […] Have you tried using Conditional Formatting? This might help: ToDo List In #Excel With Checkboxes & Conditional Formatting | Excel Bytes […]

Leave a Reply