A Microsoft Excel template for a structured Checklist with the option to check and uncheck by double clicking
Unfortunately my blogging activities slowed down to a crawl during the last few months and I left you waiting for new posts far too long already.
Be assured that this site is not dead. I will revive the blog during the next weeks and I am already working on a couple of new articles.
For one of the planned next posts I needed a checklist template. There are tons of Microsoft Excel and Microsoft Word examples available for free.
However, I couldn’t find a checklist template I really liked. Formats are always easy to change, but I was particularly looking for a checklist providing a convenient way to change the status of the checklist items. Furthermore it should be easy to use and easy to maintain. Some of the templates I found simply expect to type in an X (or something similar) to check an item, some are working with data validation lists, some have form control checkboxes. The one that came closest to what I was looking for is provided by my good friend Daniel Ferry, the Excel Hero here: Excel Dynamic Checkmark. Already pretty close to what I was after, but since it did not fulfill all of my requirements, I decided to create my own.
Agreed, today’s post is a bit off topic regarding the focus of this blog. It has nothing to do with data analysis, data visualization or dashboards. However, a nice Excel checklist template is always a useful thing to have in the toolbox.
With today’s article I am trying to kill two birds with one stone: to show a sign of life and to share my little checklist template with you.
The Idea and the Features
Creating a checklist in Excel is a piece of cake and for a basic version you do not need one single formula, let alone VBA.
Having said this, I always like some interactivity in my tools and templates.
Interaction with a checklist? You guessed it: an intuitive and convenient way to change the status of a checklist item, like double clicking:
- Double clicking the status column of a checklist item sets its status to checked, another double click sets it back to unchecked
- Double clicking on a topic sets the entire topic to checked, i.e. all items belonging to this topic. Double clicking again sets the entire topic back to unchecked
- If some, but not all items of a topic are checked, the status of the topic automatically turns into “mixed”, indicated by a box-in-box symbol (see main topic 2 in the screenshot above)
Double clicking on a topic header expands or collapses all items of this topic. This comes in handy especially when you are working with a large list. Agreed, you could also do this by hiding / unhiding or grouping / ungrouping rows, but the double clicking option is more convenient from my point of view.
The display of a completion rate and some conditional formatting tops off the look and feel of this little checklist template:
- Checked topics and items are shaded off with a grey font color
- The cell at the top right of the checklist shows the completion rate in percent. The fill color of the cell is red if less than 75% of all items are checked, green if all items are checked and yellow if the completion rate is between 75 and 100 percent.
The Implementation
I won’t go into the details here. Except for the very simple standard conditional formatting, the main job is done by a couple of small VBA routines (little more than 100 lines of code) based on the event driven sub Worksheet_BeforeDoubleClick.
If you are interested, have a look for yourself (download link see below).
How to use this template for your own checklists
Here is the good news: you do not have to understand, let alone change the VBA if you want to use this checklist. Download the template (see download link below) and you are (almost) good to go.
For sure you will have to make some changes of the template for your own checklist. Here is what you need to do for the changes you will probably want to make:
- More or less columns
Just insert as many additional columns as you need between the first (#) and the last column (status). If you don’t need e.g. the description, simply delete this column. All you have to keep is the first and the last column as they are
- More or less checklist items
If you need less checklist items in one or several topics, simply delete the rows. If you need more checklist items for a topic, insert rows and copy down the format from the row above. Finally make sure the numbering in the first column meets the defined convention (the number of the topic followed by a period and the number of the checklist item).
- More topics
Copy one entire topic (the topic header and the check items) and insert it at the end of the checklist. Change the numbering in the first column for the new topic and its items. Enlarge the named formula “myCheckList” to cover the entire checklist.
- Formats
You don’t like the look and feel? No problem. Change the formats to whatever you like. Fill or border color, font and font size, borders, row height or column width. No matter what. The interactive feature will not be affected. Just make sure to keep the font type “Wingdings2” in the status column in order to display the empty or checked boxes.
Important:
Please make sure that your entries in the first column (#) follow the convention “topic number followed by a period followed by the item number”, e.g. 1.1, 1.2, etc. This is important because the VBA code uses the period to separate the topics from the items.
I am pretty sure this isn’t everything you could wish for, but I think it should cover the most important changes to turn this template into your own checklist.
The Download Link
Download the checklist template here:
Download Check List Template – (Excel 2003 workbook, 74K)
As mentioned in the introduction, this post is just the starting point of reviving my blog activities. So please stay tuned, more things will hopefully come soon.
Update on Tuesday, 20th of January, 2015
This post is one of the most popular articles here on Clearly and Simply and many people have asked for various modifications and enhancements of the template provided above.
I implemented and posted most of them, but since the download links are buried in the avalanche of comments below, I pulled together a compilation of the most interesting modifications and enhancements in this follow-up post:
Leave a Reply to Chriss J Cancel reply