Checklist In Excel

What Is A Checklist In Excel?

The Checklist in Excel helps users track the completion status of an assigned task. In addition, it helps us maintain the log of multiple activities in a given dataset. It makes the dataset interactive, where we can select or deselect the options to get accurate results as “True” or “False”.

For example, the checkbox is added to cell A1 to check whether the task is complete or not. If the checkbox is checked/ticked, the result is TRUE, it represents the work done, but if the checkbox is not checked/ticked, the result will be FALSE, as shown in the following image.

We create the Checklist in Excel template as shown above and update it accordingly to track and avoid missing any tasks.

What Is A Checklist In Excel?

The Checklist in Excel helps us track the assigned tasks, whether they are completed or pending.
The conditional formatting method in the checklist incorporates a data validation, strikethrough, highlights duplicate values, and distinguishing between various parameters based on a font or background color in your activities.
The Checklist in Excel will always return the logical values “True” or “False”. To get any other desired output, we can always use conditional functions like IF(), COUNTIF(), etc.
Link the cell references for the output to their adjacent cells to identify and to hide/unhide.

How To Make/Create A Checklist In Excel?

We can create Checklist In Excel Using CheckBoxes with the help of the Excel ribbon as follows:

Select the “Developer” tab > go to the “Controls” group > click the “Insert” drop-down > click the “Check Box (Form Control)” option from the “Form Controls” groups.

Let us take a basic example to create Checklist In Excel for the grocery list and track the assigned tasks.

In the following table, the data is,

Column A contains the Grocery List.

The steps to create Checklist In Excel for the grocery list are as follows:

Step 1: Select the Developer tab > go to the “Controls” group > click the “Insert” drop-down > click the “Check Box (Form Control)” option from the “Form Controls” groups.

Step 2: Click on cell B2 to insert the checkbox and adjust it within the cell.

Step 3: Drag cell B2 with the inserted checkbox, using the fill handle till cell B7.

Step 4: Now, check/tick the B2 checkbox and right-click on it, to link the cell reference to see the status, click on the “Format Control…” option.

Step 5: The “Format Control” window opens. Here, go to the “Control” tab, in the “Value” group, select the “Checked” option, and in the “Cell link:” field, select the cell reference(C2), and click “OK” instantly C2 will display “True”, as shown below.

Step 6: Repeat the same process for all the checkboxes in column B i.e., link the cell references to their respective checkboxes. Now, select some random value cells in column B, here, cells B4, B5, and B6.

We will get the output as shown above. The checked/ticked checkboxes in column B display “True”, and the remaining cells display “False”, indicating that the checked items from the grocery list are purchased.

Examples

We will understand Checklist In Excel with some advanced scenarios.

Example #1 – How to Make your Checklist More Attractive?

We will insert Checklist In Excel for the list of fruits and format it to Make the Checklist More Attractive.

In the following table, the data is,

Column A contains the Fruits and Vegetables List.

The steps to insert Checklist In Excel and format it are as follows:

Step 1: Select the “Developer” tab > go to the “Controls” group > click the “Insert” drop-down > click the “Check Box (Form Control)” option from the “Form Controls” groups.

Step 2: Click on cell B2 to insert the checkbox and adjust it within the cell.

Step 3: Drag cell B2 with the inserted checkbox, using the fill handle till cell B7.

Step 4: Now, check/tick the B2 checkbox and right-click on it, to link the cell reference to see the status, click on the “Format Control…” option.

Step 5: The “Format Control” window opens. Go to the “Control” tab, in the “Value” group, select “Checked”, and in the “Cell link:” field, select the cell reference(C2), and click “OK”, instantly C2 will display “True”, as shown below.

Step 7: To format, select cell range A1:B7 > go to the “Home” tab > go to the “Styles” group > click the “Conditional Formatting” drop-down > select the “New Rule…” option, as shown below.

Step 8: The “New Formatting Rule” window opens. Here, go to the “Select a Rule Type:” group and select the “Use a formula to determine which cells to format” option, go to the “Edit a Rule Description:” group and enter the “=$C2=TRUE” formula in the “Format values where this formula is true:” field, and click the “Format…” button, as shown below.

Step 9: The “Format Cells” window opens. Here, go to the “Fill” tab > select the desired color from the “Background Color” group, here, “Green” > click “OK” to close the “Format cells” window.

Step 10: Finally, click the “OK” button again to close the “New Formatting Rule” window.

All the cells fulfilling the test formula will turn green, as shown in the output below.

Therefore, this process makes our Checklist More Attractive.

Example #2 – Strikethrough all the Completed Excel Checklist.

We will create Checklist In Excel template for the general list and Strikethrough all the Completed Excel Checklist.

In the following table, the data is,

Column A contains the List of Items.

The steps to insert Checklist In Excel and to strikethrough on the completed tasks are as follows:

Step 2: Click on cell B2 to insert the checkbox and adjust it within the cell.

Step 3: Drag cell B2 with the inserted checkbox, using the fill handle till cell B6.

Step 4: Now, check/tick the B2 checkbox and right-click on it, to link the cell reference to see the status, click on the “Format Control…” option.

Step 5: The “Format Control” window opens. Here, go to the “Control” tab, in the “Value” group, select “Checked”, and in the “Cell link:” field, select the cell reference(C2), and click “OK”, instantly C2 will display “True”, as shown below.

Step 7: To format, select cell range A1:B6 > go to the “Home” tab > go to the “Styles” group > click the “Conditional Formatting” drop-down > select the “Manage Rules…” option, as shown below.

Step 8: The “Conditional Formatting Rules Manager” window opens. Here, click the “New Rule” option.

Step 9: The “New Formatting Rule” window opens. Here, go to the “Select a Rule Type:” group and select the “Use a formula to determine which cells to format” option, go to the “Edit a Rule Description:” group and enter the “=$C2=TRUE” formula in the “Format values where this formula is true:” field, and click the “Format…” button, as shown below.

Step 10: The “Format Cells” window opens. Here,

First, go to the “Fill” tab à select the desired color from the “Background Color” group, here, “Pink”.

Next, click the “Font” tab > go to the “Effects” group > check/tick the “Strikethrough” checkbox.

Step 11: Click “OK” to close the “Format cells” window. Also, once again, click the “OK” button to close the “New Formatting Rule” window.

Step 12: The above rules are shown on the “Conditional Formatting Rules Manager” window. Click “OK” to close the “Conditional Formatting Rules Manager” window.

All the cells fulfilling the test formula will turn pink and strikethrough, as shown in the following image.

Therefore, this process makes our checklist attractive and interactive.

Important Things to Note

When the checkbox/checklist is selected, it returns only “TRUE” or “FALSE” values.
Adding checkboxes and linking to their respective cell references are time-consuming. So, we can use it for small datasets.
We can format by adding colors or fonts to make the Checklist in Excel attractive using the “Conditional Formatting” option.
We must enable the “Developer” tab before we use the checklist feature to access it from the Excel ribbon quickly.

Frequently Asked Questions

How to insert a Checklist in Excel?

We can insert Checklist in Excel using the checkbox option as follows:

Select the “Developer” tab > go to the “Controls” group > click the “Insert” drop-down > click the “Check Box (Form Control)” option from the “Form Controls” groups.

Checkbox in Excel Create - 1

How to delete the Checklist in Excel?

We can insert a checkbox in a Checklist in Excel. However, we do not have an option or a command to delete the checkbox in a Checklist in Excel. Therefore, we have to delete the checkbox manually.

Therefore, to delete the checkbox in a Checklist in Excel template, select the checkbox in the cell > right-click on it to focus it > press the “Delete” key from the keyboard.

We must do the same for all the checkboxes in the worksheet, which is time-consuming.

Why Checklist in Excel don’t work?

A few reasons why the Checklist in Excel might not work are,

1. The cell references are not linked to the right checkbox.
2. There may be some changes in the data. Some rows or columns may be inserted or deleted, changing the set cell references.

Download Template

This article must help understand the Checklist in Excel examples. You can download the template here to use it instantly.

Checklist in Excel Download