How to Create a Checklist in Excel
Pinterest Stumbleupon Whatsapp
Advertisement

You can use Excel for your checklists. Many apps promise to handle your to-do list, but do you really need yet another app? If you’re already spending your days in Excel 5 Excel Document Settings You Must Never Forget to Check 5 Excel Document Settings You Must Never Forget to Check When you pick an Excel template or share a spreadsheet, it may contain custom settings that you don't want anyone to see. Always check these settings. Read More , look no further.

Even if you don’t want to use it as an everyday to-do list app, a checklist is a good way to keep track of what you still need to do in your spreadsheet, directly in the spreadsheet itself.

Let’s see what you can do with checklists in Excel.

How to Make a Checklist in Excel

We’ll show you how to create a checklist with checkboxes you can tick off as you complete the items. It will even indicate when you’ve checked off all the items, so you can tell at a glance. Here are the simple steps we’ll outline in more details below:

  1. Enable the Developer Tab.
  2. Enter the checklist items into your spreadsheet.
  3. Add the checkboxes and advanced formatting.

1. Enable the Developer Tab

To create a checklist, you must enable the Developer tab on the ribbon. To do this, right-click on the ribbon and select Customize the Ribbon.

how to create a checklist in excel like the pros

In the list of Main Tabs on the right side of the Excel Options dialog box, check the Developer box and then click OK.

how to create a checklist in excel like the pros

2. Enter the Checklist Items Into Your Spreadsheet

Enter your to-do list, one item per cell. In our example, we’re going to have a cell showing the Total Items and one showing the total Items Packed, or how many items are checked off on our list. The Am I good to go? cell will be red with NO in it if all the items are not checked off. Once you’ve checked off all the items, the Am I good to go? cell turns green and reads YES.

Click the Developer tab. Then, click Insert in the Controls section and click the Check Box (Form Control).

how to create a checklist in excel like the pros

3. Add the Checkboxes

Click in the cell into which you want to insert the checkbox. You’ll see that there’s text to the right of the checkbox. We only want the text box, not the text. While the checkbox control is selected, highlight the text next to the checkbox and delete it.

how to create a checklist in excel like the pros

Working with checkboxes in cells is tricky because it’s hard to select the cell containing the checkbox without checking the box. But, there are ways around this.

The checkbox control does not automatically resize once you’ve deleted the text in it. If you want to resize it, right-click on the cell to select the checkbox and then left-click on the checkbox. It will be selected with circles at the corners (as shown above). Drag one of the circles on the right side towards the check box to resize the outline to just the size of the checkbox. Then, you can move the checkbox to the center of the cell.

Now, we want to copy that checkbox to the rest of our to-do list items. To select the cell containing the checkbox, select any cell around it without a checkbox. Then, use one of the arrow keys on your keyboard to move to the cell with the checkbox.

To copy the checkbox to the other cells, move your cursor over the bottom-right corner of the selected cell with the checkbox until it turns into a plus sign. Make sure the cursor is NOT a hand. That will check the box. Drag the plus sign down over the cells into which you want to copy the checkbox and release the mouse button. The checkbox is copied to all those cells.

how to create a checklist in excel like the pros

Advanced Checklist Formatting

Depending on what you want to use your checklist for, you can add further formatting elements to validate your list and summarize its status.

Create a True/False Column

For this step, we need to use the column to the right of the checkboxes to store the TRUE and FALSE values for the checkboxes. That allows us to use those values to test if all the boxes are checked or not.

Right-click on the first checkbox and select Format Control.

how to create a checklist in excel like the pros

On the Control tab on the Format Object dialog box, click the cell selection button on the right side of the Cell link box.

how to create a checklist in excel like the pros

Select the cell to the right of the checkbox cell. An absolute reference to the selected cell is inserted in the Cell link box on the compact version of the Format Control dialog box. Click the cell selection button again to expand the dialog box. Click OK on the dialog box to close it.

Repeat the procedure from right-clicking on the checkbox to selecting the cell to the right for each checkbox in your list.

how to create a checklist in excel like the pros

Enter Total Items and Calculate Items Checked

Next, enter the total number of checkboxes in your list into the cell to the right of the Total Items cell.

Now, we’ll use a special function to calculate How to Use Excel to Calculate Between Dates How to Use Excel to Calculate Between Dates How many days are there between January 9, 2015 and June 12, 2017? Stop struggling when Excel can do the hard work for you! Read More how many checkboxes have been checked.

Enter the following text into the cell to the right of the cell labeled Items Packed (or whatever you called it) and press Enter.

=COUNTIF(C2:C8,TRUE)

This counts the number of cells in the C column (from cell C2 through C8) that have the value TRUE. Replace “C2:C8” with the column letter and row numbers corresponding to the column to the right of your checkboxes.

how to create a checklist in excel like the pros

Hide the True/False Column

We don’t need the column with the TRUE and FALSE values showing, so let’s hide it. Click on the lettered column heading to select the whole column. Then, right-click on the column heading and select Hide.

how to create a checklist in excel like the pros

The lettered column headings now skip C, but there’s a double line indicating a hidden column.

Check If All Checkboxes Are Checked

We’ll use the IF function for Am I good to go? (or whatever you called it) to determine if all the checkboxes are checked. Select the cell to the right of Am I good to go? and enter the following text.

=IF(B10=B11,"YES","NO")

This means that if the number in cell B10 is equal to the number calculated from the checked boxes in B11, YES will be automatically entered in the cell. Otherwise, NO will be entered.

how to create a checklist in excel like the pros

Apply Conditional Formatting

You can also color code the cell based on whether the values in cells B10 and B11 are equal or not. This is called Conditional Formatting. We’ll show you what to enter to turn the cell red if not all the checkboxes are checked and green if they are. See our article about Conditional Formatting Automatically Format Data in Excel Spreadsheets With Conditional Formatting Automatically Format Data in Excel Spreadsheets With Conditional Formatting Excel's conditional formatting feature lets you format individual cells in an Excel spreadsheet based on their value. We show you how to use this for various everyday tasks. Read More for information on how to create rules.

Create a rule on the Conditional Formatting Rules Manager dialog box using the Use a formula to determine which cells to format rule type. Enter the following text in the Format values where this formula is true box. Replace B10 and B11 with the cell references for your Total Items and Items Packed (or whatever you named these cells) values, if they’re not the same cells.

=$B10<>$B11

Then, click Format and select a red Fill color and click OK.

how to create a checklist in excel like the pros

Create another new rule of the same type, but enter the following text in the Format values where this formula is true box. Again, replace the cell references if needed to match your checklist.

=$B10<>$B11

Then, click Format and select a green Fill color and click OK.

how to create a checklist in excel like the pros

On the Conditional Formatting Rules Manager dialog box, enter an absolute reference for the cell you want to color green or red in the Applies to box. Enter the same cell reference for both rules. In our example, we entered =$B$13.

Click OK.

how to create a checklist in excel like the pros

Our Am I good to go? box in the B column now turns green and read YES when all the checkboxes are checked. If you uncheck any item, it will turn red and read NO.

how to create a checklist in excel like the pros

Excel Checklist Complete? Check!

A checklist is one type of list you can create in Excel. You can also create custom lists How to Create a Custom List in Microsoft Excel How to Create a Custom List in Microsoft Excel Do you often have to fill in the same sets of data into your Excel spreadsheets? Just create a custom list and use AutoFill to apply it. Read More for information you use often, like department names and people’s names.

If you’re creating forms in Excel to make data entry quicker and easier, you can create dropdown lists How to Create a Dropdown List in Excel How to Create a Dropdown List in Excel Excel supports versatile drop-down list options. Use them to control user input. We'll show you how to add dropdown lists to your Excel spreadsheets. Read More with custom items.

Next, try to set up an Excel Dashboard 3 Excel Dashboard Tips You Have to Try 3 Excel Dashboard Tips You Have to Try Use our Excel dashboard tips to organize and show off data from a complex spreadsheet. With your neat dashboard, you'll see key data points at a glance. Read More or learn about the magic of Custom Views in Excel How to Use Excel Custom Views Like a Pro How to Use Excel Custom Views Like a Pro The Excel custom views feature lets you save spreadsheet and print settings for later. Few people know this tool. We'll show you how to use custom views. Read More .

Do you use checklists in Excel? If so, for what purposes? Share your ideas and experiences with us in the comments below.

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. Marshall Nolan
    December 7, 2017 at 4:44 pm

    Good item, but handling the focus of "right clicking" to get Format Control was correct in being a little touchy. Good article.

  2. Nicolas Posada Alvarez
    November 30, 2017 at 9:24 pm

    You can do step "Create a True/False Column" before dragging the checkboxes across the list and it will save you a lot of time.