How to Create an Interactive Checklist in Excel (With Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will learn how to create an interactive checklist in Excel. It is mainly used for selecting or deselecting any option. But we can make the checklist interactive and do some useful tasks in Excel. So, without any delay, let’s start the discussion.


Download Practice Workbook

To practice by yourself, download the following workbook.


Watch Video – Create an Interactive Checklist in Excel


Step-by-Step Procedures to Create an Interactive Checklist in Excel

To demonstrate the steps, we will use the dataset of the Home Office Checklist with answers. In Column B, we have information about a list of products necessary for the home office. In Column C, we have information about the product’s availability. Let’s follow the steps to learn how we can create an interactive checklist in Excel.

how to create an interactive checklist in excel


STEP 1: Enable Developer Tab

  • Firstly, we need to enable the Developer tab. If the Developer tab is already visible in the ribbon, then you can skip this step.
  • To enable the Developer tab, select the File tab.

Enable Developer Tab

  • Now, a new window will open up and from the leftmost bottom corner, select Options.

  • After that, Excel Options window will appear on the screen.
  • Now, we have to select Customize Ribbon section and mark Developer.
  • Press OK.

Enable Developer Tab


STEP 2: Insert a Checkbox

  • In the following step, create a new column named Status.
  • In the Status column, we will put our checkbox.

Insert a Checkbox

  • Now, select Insert from the Developer tab.

  • After that, find the icon of the Checkbox in the Form Controls section and click on that icon.

  • After that, you can see a (+) sign.
  • Now, click on the D5 cell to insert the checkbox.

Insert a Checkbox

  • Now, the checkbox is visible.
  • You can give the name of the checkbox as per your wish.
  • So, right-click on the box and a dialog box will open up.
  • Click on the Edit Text to change the name of the checkbox.
  • We have given the name Available.


STEP 3: Add Multiple Checkboxes in Excel

  • In the following step, use the Fill Handle by dragging down the cursor while holding it at the right-bottom corner of Cell D5.

Add Multiple Checkboxes in Excel


STEP 4: Link Cells with Checkboxes

  • Therefore, we will get the checkbox for every piece of information.
  • Now, we will right-click on the box of the D5 cell.
  • Select Format Control from the menu.

Link Cells with Checkboxes

  • After that, a new window named Format Object will open up.
  • In the Format Object window, we have to select the Control tab.
  • Now, give a tick on Unchecked.
  • Then, in Cell Link, select the cell you want to link.
  • We want to link the checkbox with the E5 cell.
  • So, we will type $E$5.
  • Click OK.

  • Now, for ticking the checkbox in the D5 cell, in E5 cell will show TRUE.

  • Now, for ticking off the checkbox in the D5 cell, in E5 cell will show FALSE.
  • You can change the pattern of TRUE and FALSE by changing the value Mixed in the Format Object window.
  • Then, ticking on will show FALSE and vice-versa.

Link Cells with Checkboxes

  • Now, you have to manually do the same for the E6 to E14 cells of the Linked Cell columns.
  • So, the checkboxes of the D column and E column are now linked.
  • Changing the checkboxes will make changes in the Linked Cells column.


STEP 5: Make Interaction with Checklist

  • In the following step, we will make an interaction.
  • Now, delete the elements in the Availability column and move the Availability column to the column.
  • We will show how the Availability column can be controlled by the Checkbox.

Make Interaction with Checklist

  • After that, select New Rule.

Make Interaction with Checklist

  • Now, the New Formatting Rule window will appear.
  • In that window, select Use a formula to determine which cells to format.
  • After that, put the following formula in there.
=$D5=TRUE
  • Now, click on Format to change the format in the Home Office Checklist column.

  • Next, a new window named Format Cells will open up.
  • You can select the format that you want to show for TRUE values.
  • We will select Font Style Bold.
  • Also, we can change the Font color.
  • We will put the color Red.

Make Interaction with Checklist

  • After that, move to the Fill tab on the same window.
  • You can select the background color from here.
  • After selection, press OK.

Make Interaction with Checklist

  • Now, in the New Formatting Rule window, you can see the preview.
  • Then, press OK.

  • Now, In the dataset, we can observe the change.
  • When the checkbox is ticked, column B shows the modified format.
  • You can check by ticking on any Status bar.

Make Interaction with Checklist

  • Suppose we have bought a File Cabinet.
  • So, we will put a tick on File Cabinet.
  • Linked Cell and Home Office Checklist columns have changed as the checklist is interlinked.

Read More: How to Make a Daily Checklist in Excel (2 Simple Methods)


STEP 6: Link Checklist with Availability

  • Now, in the E column, we want to make some changes.
  • We want to show Available or Not Available as per Status.
  • So, write the following formula on the E5 cell.
=IF(D5=TRUE,"Available","Not Available")
  • The E5 cell is showing the result Not Available.

Link Checklist with Availability

  • In the following step, paste the formula for all cells of the Availability column
  • Now, you can use the Fill Handle by dragging down the cursor while holding it at the right-bottom corner of Cell E5.

  • Now, the formula is applied to the Availability column.
  • So, the output is now visible.


STEP 7: Format Fonts of Availability Column

  • In the following step, select the cell from E5 to E14.
  • Now, we will do some modifications to the Availability column.

Format Fonts of Availability Column

  • Similarly, from Conditioning Formatting Command Button, go to the New Formatting rule window.
  • Select Use a formula to determine which cells to format.
  • After that, write the following formula:
=$D5=FALSE
  • Now, click on Format.

  • Similarly, select the formatting you want to do.
  • In the Font tab, we have selected the Bold Font Style and Strikethrough effect.
  • Now, press OK.

Format Fonts of Availability Column

  • Now, the preview is showing on the preview box.
  • So, press OK.

  • Now, the modification is visible.
  • In the availability column, Not Available is showing with Strikethrough.
  • This modification is for simplifying our understanding of the dataset.

Format Fonts of Availability Column


STEP 8: Insert Summary Based on Checklist

  • Now, give a tick on Computer and/or Laptop.
  • After that, observe the change.
  • In the following step, we will add Summary.
  • In the summary, there is information about Total Items, Available Items and Availability percentage.

Make Summary Based on Checklist

  • Now, find out the total number of items by using the following formula of the COUNTA function in C18 cell:
=COUNTA(B5:B14)
=COUNTIF(D5:D14,TRUE)
  • To find out the Availability percentage in C20 cell, use the following formula and convert the format into Percentage:
=C19/C20
  • You can change the format to Percentage by pressing Ctrl + 1.
  • Now, in Summary, there is showing information about Total Items, Available Items and Availability Percentage.


Final Output

  • Finally, tick on Comfortable Chair and observe the change.
  • As a result, the format of the Home Office Checklist column is changed.
  • The Availability column is also showing that the item is available.
  • In the summary, Available items and Availability Percentage are also increased.
  • Lastly, it can be said that our Checklist is interactive as it is interacting with other data.

Final Output


Conclusion

In this article, we have demonstrated how to create an interactive checklist in Excel. There is a practice workbook at the beginning of the article. So, go ahead and give it a try. To read similar articles, check out the ExcelDemy website. Lastly, please use the comment section below to post any questions or make any suggestions you might have.


Related Articles

Sudipta Chandra Sarker

Sudipta Chandra Sarker

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel. Here I will be posting articles related to this. My educational degree is BSc in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology, Bangladesh. I have a great interest in research and development. I always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo