Checklist in Excel (Table of Contents)
Introduction to Checklist in Excel
A checklist in Excel is a type of control that is used to see whether the assigned task is completed or not. This is also used when we need to opt for multiple options among the given fields in question answers forms. To create a Checklist in Excel, find and create a situation where we can mark the condition of choosing multiple options or opting to choose the completed task. Then from the Insert option drop-down menu list, select Checkbox from the Form Controls section and draw this anywhere we want. We can also assign the macro codes using the ActiveX Form Control section.
Examples of Checklist in Excel
Allow us to see some of the Excel checklist examples and how to generate one in different ways. There are multiple ways of using a checklist; you can create Interactive Lists or charts in excel. This helps in making a quick analysis of the percentage of tasks completed or pending etc.
1. This is a Product Market survey checklist depicting the status of listed task completion. True status shows the task has been finished, whereas False shows listed work is pending or needs to work on.
2. Using the same checklist, you can apply various conditional formatting to strike out the completed task in your to-do list.
3. Interactive charts can be made using the checkbox option. Using cell reference, we can create logic to link the checkbox with chart tables.
How to Create a Checklist in Excel?
Here we will see how to build a checklist with steps in excellence.
Enable the Developer tab on the ribbon before you start creating a checklist. Go to the Excel Options menu, select Customized Ribbon.
Make sure that the developer box is checked in the Main Tabs list.
Prepare the list of activities for your assignment. Here in this example, I have used the Product market survey checklist that lists done all the activities that I will be doing for the assigned project.
Now to Insert the Checkboxes, click on cell B2, go to the Insert menu under the Developer tab ribbon, select the Check Box (Form Control) option. The checkbox will be created in cell reference B2.
Right-click on cell B2, where the checkbox is created, go to the edit text option to edit the text of the checkbox.
Now copy or drag the checkbox for all the other listed tasks as well. You can copy, apply to the group in and even set the order preference of these checkboxes as per your requirement. Using Assign Macro, you can click on the checkbox, and a macro will start running.
Now we will see how to make the checkboxes interactive with Lists or Charts:
a. Right-click on the first “Product research” checkbox and select Format Control.
b. In the Control tab option of the Format Control dialog box, select a cell in an excel sheet where you want to link this checkbox. In this example, I have selected cell “c2” to create a link.
c. Repeat the above steps to link all the other checkboxes.
d. In Status (Column C) appears to have a True/False value; checkboxes that are selected are “True”, depicting that the checkbox is selected, whereas “False” for the unselected checkbox.
How to Use Checklist in Excel?
A checklist can be used in Several Ways. To keep track of the number of tasks Completed/Pending:- Using the countif formula, you can see how many tasks have been completed, pending.
One can strike out an option from a checklist when our task gets completed. This can be done by using the conditional formatting feature in the checklist. Below is the example where task such as Product research, designing market strategy & asking expert opinion has been completed, and longer needs to be considered in the future.
Based on your preference, we can apply conditional formatting by distinguishing the list based on a different background or font color to the checked items (TRUE values) or by strike format. To apply below the strike-through option.
First, select the listed Product market survey. Go to the conditional formatting option available in the Home tab. Then go to the New Rule option. In the New Rule dialog box, in select, a rule type, select the last option, “Use a formula to determine which cells to format”.
In the Edit the Rule Description portion, enter the formula: =$D3=TRUE. And Click on the Format button.
In format cells, the dialog box selects the Strikethrough option. Note you can also choose font style & background color as per your choice.
Checkboxes or checklists can also be used along with charts or tables to make it more interactive. Here in the below example, I have shown products A, B & C comparison with respect to the profit margin.
Choose a table for which you want to create a chart.
Select the data. Selecting the Insert Chart option available in the Insert toolbar.
Choose the chart type.
Create checkboxes and make their respective cell reference. I have created 4 checkboxes for Product A, Product B, Product C, and Profit margin in this example.
Now make a copy of the table (column P to T) and write the formula =IF($C$10,$C4,NA()). This means that if Cell C10 is true, then the table will contain a C4 value, i.e. 2000 for Product A in 2015, else it will return NA. This condition will make or chart interactive. Whenever the checkboxes are checked, they will have values associated which will be reflected in a chart. When checkboxes are unchecked, the chart won’t show any values.
When all the checkboxes are checked:
When product B is unchecked, no value is seen in the graph; this shows the movement of product A&C when compared to Profit margin. Likewise, you can choose the measuring paraments using a combination of Checkboxes to make your report more interactive.
Things to Remember
- Make sure the Developer tab is enabled before you start using the checklist option.
- A checkbox will be inactive use until it has a cell reference associated with it.
- It is best to create cell reference in the adjacent cell so that it is easier to identify and hide or unhide.
- When the checkbox/checklist is selected, it returns TRUE values, else FALSE.
- Using conditional formatting in Checklist, you can incorporate a data validation, strikethrough, highlight duplicate values, distinguish between various parameters based on a font or background color in your activities.
- Using various excel formula’s one can make the dataset report more interactive.
Recommended Article
This is a guide to the Checklist in Excel. Here we discuss how to create a Checklist in Excel along with practical uses and a downloadable excel template. You can also go through our other suggested articles –
23 Online Courses | 9 Hands-on Projects | 110+ Hours | Verifiable Certificate of Completion
4.9
View Course
Related Courses
