Locked lesson.

Upgrade

Quick reference

Create Dynamic Checklists

Use Form Controls and Conditional Formatting to create Dynamic Checklists. 

When to use

We use this technique whenever we want to create a list with checkboxes that applies formatting when the checkbox is checked. 

Instructions

Form Controls are a great way to create interactive checklists. For example, maybe we have a to-do list of items, or project tasks that we want to be able to check off. 

We can use conditional formatting with form controls to apply formatting to cells once the box has been checked. 

In this example, we have a reading list and we want the book titles to show grayed out with a strike-through once we have read them. 

Turn on the Developer Ribbon

Form Controls live on the Developer ribbon in Excel. By default, the Developer ribbon isn't turned on. 

  • Click on the File tab. 
  • Click Options. 
  • Click Customize Ribbon. 
  • In the right-hand pane, place a check next to Developer. 

  • Click OK. 

Use Form Controls to Add Checkboxes

Now we can add checkboxes to our spreadsheet. 

  • Click in cell D7. 
  • From the Developer tab, in the Controls group, click Insert. 
  • From the Form Controls section, choose Check Box (Form control). 

  • Draw a checkbox in cell D7. 
  • Delete the checkbox label. 
  • Position the checkbox.
  • Use the auto-fill handle and drag down to a checkbox is placed next to every book in the reading list. 

Understand how Checkboxes Work

When a check is placed in a checkbox, Excel sees it as a TRUE result. When a check is removed from a checkbox, Excel sees it as a FALSE result. We can use these TRUE and FALSE results to apply conditional formatting. 

Cell Link

We can link checkboxes to a cell in the worksheet. By linking to a cell, we can see the TRUE or FALSE output. 

  • Click on the first checkbox. 
  • Right-click and choose Format Control. 
  • From the Control tab, click in Cell link. 
  • Select cell E7. 
  • Click OK. 

Now, when we place a check in the box, we can see a TRUE result. 

  • Repeat these steps for all checkboxes. 

Apply Conditional Formatting

We can now set up a conditional formatting rule that uses the TRUE or FALSE result to apply formatting to the reading list. 

  • Select the reading list. 
  • From the Home tab, in the Styles group, click Conditional Formatting. 
  • From the Select a Rule Type area, choose Use a formula to determine which cells to format. 

Our formatting rule says that when the value in the cell is TRUE, apply the specified formatting to the row. 

  • Click Format. 
  • From the Font tab, in the Effects area, select Strike through. 
  • From the Color palette, choose a light gray. 
  • Click OK. 

 

Login to download
  • 00:04 In this lesson, we're going to take a look at how we can create
  • 00:07 Dynamic Checklists using Logic and Form Controls.
  • 00:11 And this is a really cool little technique if you like to be able to check
  • 00:16 off items in a list.
  • 00:17 So that might be checking off items in a To Do list,
  • 00:21 it might be checking off project tasks, or in this case I have a list of all
  • 00:26 of the business analysts books that I want to read over the course of the next year.
  • 00:31 Once I've read them, I want to be able to check a checkbox to check it off.
  • 00:36 And I want the actual name of the book to gray out and show with a strikethrough as
  • 00:42 soon as I put a tick in that checkbox to show that it's completely done.
  • 00:47 So let's dive in and take a look at how we would go about putting this together.
  • 00:51 Now, the first thing we need to do here is we need to add in our checkboxes.
  • 00:55 So, where are we going to find checkboxes in Excel?
  • 00:59 Well, you find them on the Developer Ribbon.
  • 01:03 Now, it's worth noting that the Developer Ribbon isn't always turned on by default.
  • 01:08 So, if you're currently looking at your Excel thinking, well,
  • 01:11 I don't have a Developer Ribbon, you just need to go into Options and turn that on.
  • 01:15 So all you would need to do is jump up to File, go down to Options, and
  • 01:20 then from the Customize Ribbon page, you want to make sure that in the list
  • 01:26 on the right-hand side, you just have a tick next to Developer.
  • 01:31 If you can't see it, you're going to find that this is unchecked.
  • 01:34 So make sure you have a check in there.
  • 01:35 Click on OK, and you should be able to see your Developer Ribbon.
  • 01:40 Now, in this Controls Group just here, underneath Insert,
  • 01:43 this is where we have different types of form controls that we can insert.
  • 01:47 And we have lots of different things in here, things like Option buttons,
  • 01:51 Dropdown lists, all that kind of stuff.
  • 01:53 But one of them is Checkbox, and that's what we want to use.
  • 01:57 So I'm going to select the checkbox Form Control, and
  • 02:01 I'm just going to draw it in this little box over here.
  • 02:05 And this can be a little bit tricky, kind of have to maneuver them around a little
  • 02:09 bit, so I'm going to place that in the middle.
  • 02:11 Now I don't want to have a checkbox name.
  • 02:13 You can see it's trying to put in a name of checkbox, I'm just going to click next
  • 02:18 to my checkbox and I'm just going to delete out all of that text.
  • 02:22 So now that I have my checkbox, I'm simply going to, use autofill to copy this down.
  • 02:28 So let's hover over the corner until we get that tiny little black cross and drag
  • 02:33 down, and that is going to put a checkbox in every single one of those cells.
  • 02:37 So now that I have these checkboxes, I can click in it for the check,
  • 02:41 click again to deselect.
  • 02:42 Now currently these checkboxes aren't really doing anything.
  • 02:46 Now I could leave the checkboxes like this if I just want to check off when I've read
  • 02:50 a book that's absolutely fine.
  • 02:52 But let's go in and make these a little bit more functional because.
  • 02:55 Because what I want to happen is, when I put a check in here,
  • 02:59 I want the name of the book to gray out and show with a strikethrough.
  • 03:03 Now for this, we need to use conditional formatting, but
  • 03:06 we need to understand how checkboxes work, first of all.
  • 03:09 Now if I put a check in the box and right-click, let's go into Format Control.
  • 03:14 Now notice here on the Control tab it says value, and
  • 03:17 currently that checkbox is checked.
  • 03:20 And then underneath that we have Cell link.
  • 03:23 Now this is really important because this is going to output a true or
  • 03:27 false value into whatever cell you specify just here.
  • 03:31 So I'm going to just click in the cell next to where I have the checkbox and
  • 03:36 use that as my cell link.
  • 03:38 Let's click on OK.
  • 03:39 Now notice what happens here,
  • 03:41 as soon as I put a check in the box we get a true result.
  • 03:45 If I remove the check, we get a false result.
  • 03:48 So now that we have these trues and falses,
  • 03:51 we can use those to do other things.
  • 03:53 For example, we can use them in formulas to apply our conditional formatting.
  • 03:58 Now one of the drawbacks of checkboxes is that, I can't just simply copy this down,
  • 04:02 because that's not going to work.
  • 04:04 If I then put a check in here, it only changes the first one.
  • 04:07 So unfortunately, and this is a little bit manual, we need to go through right-click
  • 04:13 on every single checkbox and change the Cell link to the cell next to it.
  • 04:18 So I'm going to go through and do that off camera, and
  • 04:21 I'll join you back here in a couple of moments.
  • 04:23 So now that I have all of those cell links set up, I can go in and
  • 04:27 set up my conditional formatting.
  • 04:29 So what I'm going to do here,
  • 04:31 is I'm going to select every book that I have in this list.
  • 04:36 We're going to jump up to Home and go to Conditional Formatting and New Rule.
  • 04:42 Because what we're going to do here is we're going to use a formula to determine
  • 04:45 which cells to format.
  • 04:46 Now what is our formula going to be?
  • 04:49 We're going to type in equals and we're going to say that when
  • 04:54 this cell equals true, we want it to apply the formatting.
  • 04:58 Because if this cell is true, it means there's a check in the box and
  • 05:02 we've read this book.
  • 05:03 Now before we continue,
  • 05:05 we need to make a slight change to the way this cell reference is set up.
  • 05:09 Because remember, by default when you're using conditional
  • 05:12 formatting with a formula, the default is to make that cell reference absolute,
  • 05:17 it's going to lock the column and the row.
  • 05:19 Now we don't want that because we want to be able to have a formatting to travel
  • 05:24 across the rows, the number row and the reading list row, but
  • 05:28 we always want to refer to the value that we have in column E.
  • 05:32 So if I press the F4 key twice is basically going to lock column E.
  • 05:37 So we're always referring to the data in column E, but
  • 05:40 the row is effectively unlocked.
  • 05:42 So now we can specify what formatting we want to apply.
  • 05:46 So when I've read a book, I basically want the book title to turn
  • 05:51 to a light gray color and I wanted to show with a strikethrough.
  • 05:56 So let's click on OK and OK again.
  • 05:59 Let's test it out and see if it's working.
  • 06:02 I'm going to say that I've read this first book, check it out.
  • 06:05 And as I go through this list, marking of books that I've read,
  • 06:09 I get that really nice effect.
  • 06:11 So as I said, this can be used in multiple different scenarios.
  • 06:15 It might be To Do lists, daily checklists, it might be a list of projects or
  • 06:19 tasks, or in this case, something like a reading list.
  • 06:23 And of course, remember if you don't want column E to show,
  • 06:27 we can simply right-click and hide it away and no one is any the wiser.

Lesson notes are only available for subscribers.

Gift this course