This Excel checklist template has a lot of features. It’s easy to create and modify and it could save you from serious mistakes. If you or your co-workers do important, repetitive tasks where a missed step could cause injury, failure, or serious problems, then you need to use a checklist.
The next blog will include tips from hospitals on what it takes to get smart doctors to use checklists. I’ll also include a collection of Excel checklist templates for different uses.
Using a checklist does not mean you’re stupid. It means you’re smart!
While I’m at it, next week I’ll tell you how failing to read a checklist correctly when I was a military student pilot caused one of my most embarrassing moments. Luckily no one died and the plane survived, but my ego was severely bruised and battered.
Awesome Excel Checklist without the use of VBA
Business checklists save hundreds of thousands, if not millions, of dollars. I’m sure they have saved a few careers as well.
One company I’m familiar with could have used a checklist in their event planning. By missing steps in their event planning their annual international event overbooked before key customers were able to register. As a consequence, they faced the costs of creating another international event as well as taking special efforts to sooth key customers.
Anything important and repetitive needs a checklist. Pilots that want to live use checklists. Doctors that care about their patient’s post-op success use checklists. Research has shown the hospital use of checklists makes significant improvements in patient outcomes, yet some doctors still balk at using them. They next blog will include tips on how to get checklists adopted.
Some examples where checklists are used are,
- Pilot procedures
- Financial audits
- Complex or large marketing programs
- Event management
- Machine setup, tear down, or repair
- Intricate manufacturing or chemical processes
- Litigation management
If you are interested in managing time conflicts, then checkout the three Gantt chart templates in Critical to Success. Like checklists they are used to manage tasks, but they are used in Project Management to monitor time conflicts.
What Features Does the Best Excel Checklist Have?
I’ve made this Excel checklist easy to create, expand, and use. Any mid-level Excel user should be able to build it and expand on it. Best of all, it uses is no VBA code. A few simple Excel functions and features give it all the power you probably need.
This Excel checklist template includes,
- Check boxes to mark task completion
- Task percentage completion bars
- Total percentage complete
- Percentage of partials complete
- Task priorities
- Start and due dates (with shortcut keys)
- Reporting with filters
Enter Column Headings for the Excel Checklist
This Excel checklist begins as a simple Excel list with the following headings,
- % Complete
Additional headings you may want to add are,
- Estimated costs
- Actual costs
- Estimated hours
- Actual Hours
Enter and Format Initial Content
In the example file I’ve used tasks for completing a department budget. When making your own checklists you can identify tasks by gathering a group of subject matter experts and <brainstorming> what tasks are needed and if they need a special order of completion.
For the basic format of the table use,
|All||Left, Top alignment|
|Start, Due||Date format. Use formats starting with * (asterisk) for sheets traveling internationally.|
Use Data Validation to Create Drop-Down Menus
The drop-down menus used in the Complete, Priority, and % Complete columns are Data Validation lists. These drop-down menus are available when you click in the Complete, Priority, or % Complete columns.
The choices for these Data Validation lists come from the items in the tables shown in the figure below. Create the table as shown. A later step will show you how to create the symbols in M7:M8.
The Complete, Priority, and % Complete columns use Data Validation lists for drop-down menus. The big advantage to using these over using Drop-Down controls from the Developer tab is that Data Validation lists are easy to create and the drop-downs can be copied down the column by copying cells. Drop-Down controls, entered from the Developer tab, are difficult to replicate. Since checklists need a lot of drop-downs using drop-down controls creates a lot of work.
Create the Priority and % Complete Drop-Downs
Create the drop-downs for the Priority column,
1. Select cells D7:D31.
2. On the Data tab, in the Data Tools group, click Data Validation.
3. Select the Data Validation tool to display the Data Validation dialog box.
4. Select List from the Allow drop-down. Check Ignore Blank and In-Cell Dropdown. This will display drop-down arrows in the column whenever a cell in the column is selected.
5. Select the Source box and enter the range O7:09. Click Ok.
Clicking anywhere in the range D7:D31 will display a drop-down arrow to the right of the selected cell. Click the drop-down arrow and select from 1 to 3 to set the priority for that task.
Repeat this same process to create % Complete drop-downs in E7:E31. Use Q7:Q11 as the Source for the % Complete drop-down list.
Another advantage of using Data Validation lists is that you can copy results. If you want to fill an entire column with Priority 3 for example, just enter the 3 in one cell and copy that cell down.
Create Check Boxes in the Complete Column
Now, this for something interesting. By using the process above to create Data Validation drop-down lists and also using symbols from the Wingdings fonts we can make checkmarks or empty boxes appear in the Complete column. In fact, when you learn this technique you will be able to create lists containing any symbol.
To create the checkbox symbol in M7,
1. Select M7.
2. On the Insert tab, in the Symbols group, click Symbols.
2. Select Wingdings in Font and the checkbox symbol (X) with Character Code 120, then click Insert.
These steps format cell M7 with Wingdings font and inserts the selected checkbox symbol.
Repeat the same process to insert a clear checkbox in cell M8. The clear checkbox is character 111.
If you don’t like the two symbols shown in M7 and M8 you can choose from a wide variety of characters for checks, checkboxes, and empty boxes. Here are a few you might want to use in your Excel checklist template. I have included in the <downloadable Excel checklist template> a table showing the checkbox symbols available in Wingdings, Wingdings 2, and Webdings. Notice that some of these characters would be great for Consumer Reports ™ type ratings.
Create the Drop-Down Containing the Check Box Symbols
The Data Validation list for the Complete column contains the clear and checked boxes. To create them use the same steps shown above in Create the Priority and % Complete Drop-Downs, however, the Source will be the symbols you inserted in cells M7:M8.
Now, when you click any cell in C7:C31 the drop-down arrow will appear to the side of the cell. Select your choice of symbol and it appears in the cell.
Create Percentage Complete with Conditional Formatting
A nice finishing touch is to use conditional formatting to display completion bars for each task. The conditional formatting is based on the percentage in each cell of the % Complete column.
To use the conditional formatting,
1. Select E7:E31.
2. On the Home tab, in the Styles group, click Conditional Formatting and New Rule.
3. In the New Formatting Rules dialog box select Format all cells based on their values.
4. Select the Format Style as Data Bar.
5. If you want to display only the bar and not show the percentage, then select the Show Bar Only checkbox.
6. In the Type and Value use Minimum Number of 0 and Maximum Number of 1.
7. Use Solid Fill as the Fill selection. While gradient fills look artistic they do not give a true impression of the percent complete.
Calculating Total and Partial Completion
The downloadable Excel checklist template contains formulas that calculate the number of Complete checkboxes compared to total and the percentage of partial tasks completed in the % Complete column.
Adding Warning Arrows
You don’t want a checklist where the % Complete says 100%, but the Complete check box shows the task isn’t finished. They should match.
In the downloadable version I’ve added a little formula that pops-up a bright red arrow when the % Complete and Complete checkbox are out of synch.
The arrow formula is in B7:B31. The formula in B7 is,
This formula compares the checkbox symbol, CODE(C7), and the 100% value in E7. If they don’t agree, then it displays the ASCII character 232 in cell B7. When you format B7:B31 with Wingdings font the 232 character displays as a right-facing arrow.
Using your Excel Checklist
If you want to quickly fill one of the columns containing Data Validation lists remember you can make a selection to set one cell and then copy that cell down through as many cells as you need. This is a fast way to reset columns for a new template.
To quickly enter a date in the Start or Due column, select the cell and press Ctrl+; (semi-colon).
To quickly enter a time, select the cell and press Ctrl+: (colon).
Setting Priorities on Your Tasks
Setting a priority level for your tasks is an easy way to help you identify what is Critical to Success. Some people use 1, 2, 3 and others use A, B, C where 1 or A are top priority.
Try not to set more than three number one priorities. While a large project will have many high priority tasks, you don’t want to mark all of them “1” at the beginning. Seeing a checklist of “1s” is overwhelming.
Mark only three 1s at a time. You might even want to create a column with Today’s Priorities.
Ideas for Enhancing Your Checklist
Checkout the three Excel Gantt charts in Critical to Success. They are similar to checklists, but they are used in Project Management to monitor time conflicts. They use some Excel features you may want to add to your c
Checklists only work if an individual creates them for personal use or if management endorses them and the users help build them. The next article will give tips on how to promote the use of checklists.
If tasks must be completed in a specific order, then put a numbered sequence in the first column.
If you want a warning when a due date approaches, apply a conditional format to the Due column. Use a conditional formula that test the difference between today’s date and Due date. See the <Gantt chart> for tips on how to do this.
One attractive touch would be to use conditional formatting to format the Project and Task text with strikethrough when the Complete checkbox is entered.
To create reports, use a Data Filter to filters for what you want to see in the checklist. This enables you to filter for specific projects, filter out completed tasks, or show incomplete tasks. Make sure there are no filled cells in the row above the title or Excel will be unable to find the top of your table.
If you need printed checklists you may want to create them with this Excel checklist template, print them, and then laminate them. Use a grease pencil to mark them so the template can be reused.
After the checklist has been use multiple times, hold a meeting to see what tasks should be added or removed.
Put a date on your checklists so people know which is the most current version or when a checklist needs to be reviewed.
- 1 Awesome Excel Checklist without the use of VBA
- 2 What Features Does the Best Excel Checklist Have?
- 3 Enter Column Headings for the Excel Checklist
- 4 Enter and Format Initial Content
- 5 Use Data Validation to Create Drop-Down Menus
- 6 Create the Priority and % Complete Drop-Downs
- 7 Create Check Boxes in the Complete Column
- 8 Create the Drop-Down Containing the Check Box Symbols
- 9 Create Percentage Complete with Conditional Formatting
- 10 Calculating Total and Partial Completion
- 11 Adding Warning Arrows
- 12 Using your Excel Checklist
- 13 Setting Priorities on Your Tasks
- 14 Ideas for Enhancing Your Checklist