If you need more checklist items for a topic, insert rows and copy down the format from the row above. If you need less checklist items in one or several topics, simply delete the rows. All you have to keep is the first and the last column as they are the description, simply delete this column. Just insert as many additional columns as you need between the first (#) and the last column (status). Here is what you need to do for the changes you will probably want to make:
#Microsoft excel template download#
Download the template (see download link below) and you are (almost) good to go.įor sure you will have to make some changes of the template for your own checklist. Here is the good news: you do not have to understand, let alone change the VBA if you want to use this checklist.
#Microsoft excel template how to#
How to use this template for your own checklists If you are interested, have a look for yourself (download link see below). 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. 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 cell at the top right of the checklist shows the completion rate in percent.Checked topics and items are shaded off with a grey font color.The display of a completion rate and some conditional formatting tops off the look and feel of this little checklist template: 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. This comes in handy especially when you are working with a large list. 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)ĭouble clicking on a topic header expands or collapses all items of this topic.Double clicking again sets the entire topic back to unchecked Double clicking on a topic sets the entire topic to checked, i.e.Double clicking the status column of a checklist item sets its status to checked, another double click sets it back to unchecked.Interaction with a checklist? You guessed it: an intuitive and convenient way to change the status of a checklist item, like double clicking: Having said this, I always like some interactivity in my tools and templates. 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.Ĭreating a checklist in Excel is a piece of cake and for a basic version you do not need one single formula, let alone VBA. However, a nice Excel checklist template is always a useful thing to have in the toolbox. It has nothing to do with data analysis, data visualization or dashboards. Already pretty close to what I was after, but since it did not fulfill all of my requirements, I decided to create my own.Īgreed, today’s post is a bit off topic regarding the focus of this blog. 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. 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. Furthermore it should be easy to use and easy to maintain. 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. However, I couldn’t find a checklist template I really liked. There are tons of Microsoft Excel and Microsoft Word examples available for free. I will revive the blog during the next weeks and I am already working on a couple of new articles.įor one of the planned next posts I needed a checklist template. 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.īe assured that this site is not dead. A Microsoft Excel template for a structured Checklist with the option to check and uncheck by double clicking
0 kommentar(er)
