The Best Excel Checklist

Checklist

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 Best Excel Checklist uses no Visual Basic, but has a great set of features. It’s easy to create and modify.

The Best Excel Checklist uses no Visual Basic, but has a great set of features. It’s easy to create and modify.

Download the Best Excel 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.

US Air Force T-38 jet trainer.

US Air Force T-38 jet trainer.

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
    Surgical operations
  • 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.

How to Build a Gantt Chart in Excel

Time and Project Management with an Advanced Gantt Chart in Excel

Time and Project Management with an Even More Advanced Gantt Chart in Excel

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.

The Best Excel Checklist uses no Visual Basic, but has a great set of features. It’s easy to create and modify.

The Best Excel Checklist uses no Visual Basic, but has a great set of features. It’s easy to create and modify.

Download the Excel Checklist Template

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
  • Priority
  • % Complete
  • Project
  • Task
  • Start
  • Due
  • Notes

Additional headings you may want to add are,

  • Owner
  • Resources
  • 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,

Columns    Format 
All Left, Top alignment
Start, Due Date format. Use formats starting with * (asterisk) for sheets traveling internationally.
Notes Word wrap

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.

These tables contain contents for the Data Validation drop-down menus.

These tables contain contents for the Data Validation drop-down menus.

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.

Use Data Validation to create a drop-down for the Priority column.

Use Data Validation to create a drop-down for the Priority column.

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.

Use Insert Symbol to insert a wide range of symbols that can appear in your Data Validation drop-down lists.

Use Insert Symbol to insert a wide range of symbols that can appear in your Data Validation drop-down lists.

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.

Additional symbols you can use.

Additional symbols you can use.

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.

Use Conditional Formatting to create task completion bars.

Use Conditional Formatting to create task completion bars.

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.

Total and partial results use more complex formulas included in the downloadable Excel checklist template.

Total and partial results use more complex formulas included in the downloadable Excel checklist template.

Download the Best Excel Checklist Template

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.

Red warning arrows show when the % Complete and the Complete check boxes are out of synch.

Red warning arrows show when the % Complete and the Complete check boxes are out of synch.

Downloadable Excel Checklist Template

The arrow formula is in B7:B31. The formula in B7 is,

=IF(OR(AND(CODE(C7)=120,E7<>1),AND(CODE(C7)<>120,E7=1)),CHAR(232),””)

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

How to Build a Gantt Chart in Excel

Time and Project Management with an Advanced Gantt Chart in Excel

Time and Project Management with an Even More Advanced Gantt Chart in Excel

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.

Share the power...

Leave a Comment:

Leave a Comment: