Creating awesome and simple interactive checklist in MS Excel in three steps

What we need from our checklist

Looking through some solutions describing the check-list creation I realized that a lot of the cases require from me being aware of VBA or some components paradigm for inserting the check-boxes into the cells and make them impact the check-list items in such or another way. That frustrated me so I'm presenting some alternative solution here which will not take a lot of time to create your own and awesome-looking checklist. So what the people usually need from the check-list:

• Mandatory
• Check-boxes which are easy to fit into the cell
• Check-boxes which can visually take two states: checked and un-checked
• Nice-to-have
• Check-box states affect the look-n-feel of some other parts of the check-list
• Scalable structure so we're capable to add the items with no need to configure new rows

P.S. - Looking for a cool checklist but you do not have MS Excel installed? Check how you can implement cool checklist in LibreOffice/OpenOffice Calc.

What we're going to do

The checklist we're doing will consist of the simple table structure where one of the fields is intended to hold the check-box. Changing that check-box'es state causes the entire row shading or unshading (depending on whether it is checked or not)

The check-box will not require any scripting so you do not need to know VBA.

Nice.. Implementing skeleton

The first and the simplest thing we need to do is to design the fields which our steps in the check-list will consist of. For the presentation lets limit the field set with five ones, where the last field will be holding the check-box. That should look like this:

Good. Implementing check-boxes

We need some content to make the example more vivid, right? And of-cause we need what we're reading this post for - the check-boxes. So let's do that. Say, we're going to get into the room and we know how to do that in the most effective way. Thus, we'd like to describe the step sequence not to confuse the order when we'd like to do that again. See the example of what we got.

Okay, so.. Howe we managed to do this? The heading row is just colored with blue - not a rocket science. Lets make the check-boxes. To make the check-boxes select the column area under "IsDone" header. Right-click it and choose "Format cells" menu item. Configure the font like it's shown on the screen-shot:

Align the text in the cells so that the check-boxes are in the center of the cell. That is it. Now when you set the focus to the check-box cell and press "x" key the check-box gets checked. If you press "o" key the check-box gets unchecked. Simple and visually acceptable solution, isn't it?

Awesome! Implementing items highlighting

Let's now add some flavor to our checklist. I'd like my checklist shade the row when I set the check-box checked. I'm going to reach this goal using the conditional formatting. Let's follow the steps (the steps are applicable to the example. you should slightly reconsider the formula according to your check-box relational position in your checklist):

1. On the home tab click "Conditional Formatting" and choose "Manage rules" menu item
2. Click "New rule..."
3. In "Edit Formatting Rule" dialog select "Use a formula to determine which cells to format"
4. In "Format values where this formula is true" type =($E2="x") as in the example the first check-box resides in the cell E2
5. Click "Format..." button. Switch to "Fill" tab
6. Choose the color you'd like to shadow your checked rows. Click OK
7. Click OK on "Edit Formatting Rule" dialog
8. You should see your new rule added. Now we should specify the area that rule covers (see the screen-shot for the example). 

Now we have the awesome check-list. You might want to have the row (aka steps) grouping in your checklist. Visit this tutorial to get aware of how to implement row grouping.

Aaaand finally... you should get something like this: