The Best Excel Checklist for Consultants

One of the best things consultants can do for a company is transmit the best practices they have learned from their experience in other companies to their new client. Talking about these best practices doesn’t work, but checklists do.

Checklists insure new processes and procedures are followed. In some critical fields, missing an item on a checklist can be a fatal error. (I was a military pilot for 7 years and I LOVE checklists.) Surgeons and pilots could not perform their lifesaving skills without checklists.

Use them and get your consulting clients to use checklists.

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!

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 also used 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.

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,

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 display in the cell when you click in the Complete, Priority, or % Complete columns.

The choices to be used in the drop-down menus are first entered in short tables in the worksheet as shown here.

The choices for these Data Validation drop-down lists come from cell contents shown in the figure below. Create the table as shown. A later step will show you how to create the box and checkbox symbols in M7:M8.

In the checklist data in the Complete, Priority, and % Complete columns are entered from drop-down menus in each cell. These drop-down menus use Data Validation lists that refer to the tables in the figure above.

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 from the Developer tabs makes a lot of extra work.

Create the Priority and % Complete Drop-Downs

Create the drop-downs for the Priority column,

1. Select cells D7:D31. These are the cells where drop-down lists will later appear.

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, let's do something interesting.

By combining the Data Validation drop-downs with 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 columns 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.

Download the Best Excel Checklist

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.

Download the Best Excel Checklist

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 chart.

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.