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

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.

Watch Video – Create an Interactive Checklist in Excel

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

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.

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.

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.

Read More: How to Make a Checklist in Excel

STEP 2: Insert a Checkbox

In the following step, create a new column named Status.
In the Status column, we will put our 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.

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.

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.

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.

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 E column.
We will show how the Availability column can be controlled by the Checkbox.

Now, select the cells from B5 to B14.
After that, select Conditional Formatting from the Ribbons.

After that, select New Rule.

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.

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

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.

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.

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.

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.

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.

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.

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.

Now, find out the total number of items by using the following formula of the COUNTA function in the C18 cell:

=COUNTA(B5:B14)

After that, find out the total number of available items using the COUNTIF function in C19 cell:

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

Download Practice Workbook

To practice by yourself, download the following workbook.

Creating Interactive Checklist.xlsx

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. Lastly, please use the comment section below to post any questions or make any suggestions you might have.

Get FREE Advanced Excel Exercises with Solutions!