How to insert checkbox in Excel – Create Excel Checklist & To-do lists
In this article we’ll learn how to add checkbox in excel, use excel checkbox to create interactive checklist in excel, building to-do list and much more.
Checkbox is essentially an interactive tool which we use to select or deselect an option. It’s widely used in all online survey forms. Excel checkbox is essentially an object meaning when we delete the cell the checkbox may not delete. It’s not fixed to any cell so we can drag and drop it anywhere in the sheet.
We can use excel checkbox to create interactive dashboards, charts, excel checklists and much more.
Do remember check mark in excel (✓) and a check box is very different. If you’re looking to learn to work with check mark then read how to insert a check mark in excel.
Table of Contents
Enable Developer tab first to insert checkbox
The first step is to make sure that the developer tab is visible on the ribbon. If you already have developer tab enabled skip this step and move to the next one.
- Right click on the existing Tabs and select Customize the Ribbon.
- Under Main Tabs, select the Developer check box and hit OK
- Now the developer tab is now visible on the ribbon
How to insert checkbox in Excel
- Go to Developer tab, click Insert select Check box.
- Now click anywhere in the sheet, it’ll insert checkbox.
- First drag the checkbox to the respective cell to a specific position. Right click on the checkbox and select Format Control.
- In Format Control dialog box go to Control tab and do the following:
- Under Value, make sure the Checked checkbox is checked. Generally this is checked by default.
- Under Cell Link enter the cell which you want to link to the checkbox. You go on the worksheet and select the cell or you can just type yourself.
- Under Value, make sure the Checked checkbox is checked. Generally this is checked by default.
- Click OK. Now your checkbox is inserted and linked to a cell. Now if you go and check the checkbox, it’ll display TRUE in cell D4 and if you uncheck it’ll show FALSE.
- If you see closely the added checkbox in Excel has a caption i.e. Check Box 1. Now you may want to change or completely remove this. Just right click select ‘Edit Text’ And now you can change the caption or remove it.
Fix position of Excel checkbox in cell
Now the problem with objects is they are not fixed in any cell. So after adding a checkbox in excel, if you resize/hide/delete rows & columns the object/checkbox will not be fixed in any cell and would react independently. See the image below to understand how it’ll look if we just resized few rows::
To stop this and to fix a checkbox in a given cell:
- Right click on the excel check box and select Format Control.
- In the Format Control dialog box, under Properties select ‘Don’t move or size with cells.’
- Click OK. Now you try resizing/hiding/deleting columns to see how it affects checkbox
Inserting multiple checkboxes in Excel
Now let’s say you want to add multiple checkboxes in excel, there are few ways to achieve this let’s see:
- How to add checkbox in Excel – Using Copy Paste
Select an existing checkbox, copy & paste it. You can also just press CTRL + D. Now these new copied checkboxes are linked to the same cell as original checkbox, we need to now change the linked cell manually for each new checkbox. The caption names will also be the same so you would to individually change each one. - How to make a checkbox in Excel – Dragging & filling cells.
This is a much faster way you just have to select the checkbox in excel and simply drag it down, this will make multiple checkboxes in excel.Do Note: The captions would be the same plus the linked cell would be the same, you would have to manually change the link for each one.
How to make checklist in Excel
Let’s say you want to create checklist for excel which will have all the tasks which you need to perform. And you want it to work just like a To-do list.
We just need to use a few formulas and conditional formatting to build an interactive checklist in excel.
We have the following data set which we want to use a checklist for excel. This is what the end to-do list look like:
- Create the list of activities in column A. Insert checkboxes in column B and link them with cells in column E. Make sure you link all checkboxes.
- Now in cell C2 type =IF(E2,”Completed”,”Yet to start”) and drag this formula to all cells below.
- Select cells from C2 to C8, then go to Home tab select Conditional Formatting and select New Rule.
- In New Formatting Rule dialog box, select Use a formula to determine which cells to format. Under Format values type =$E2=TRUE where E2 is the first linked cell. Click on Format.
- Now select the desired color and under Effects you can select Strikethrough. Now click OK.
- Now in cell H4 type =COUNTIF($E$2:$E$8,TRUE). This will only count the tasks which have been completed.
- In cell H5 type =COUNTIF($E$2:$E$8,TRUE)/(COUNTIF(E2:E8,FALSE)+COUNTIF(E2:E8,TRUE))This is show percentage task finished.
Voila. This is how you create a checklist in excel.
Create Dynamic Chart based on Excel Check box
We can use checkboxes to build a dynamic chart just like this:
So how does this work?
The checkboxes are linked to cells B8 & B9. As soon as the value of these cells change the values of sales are either shown or hidden. So when we check the box for 2018 the value in B8 becomes TRUE. The chart is created using data from A11 to E14.
So if B8 is TRUE then the values in B12 to E12 becomes visible otherwise if it’s FALSE the values stay hidden.
- First create the data to be used for building charts and create check boxes. Then link the checkboxes to cell B8 & B9 (or as you wish)
- Now in cell B12 type =IF($B$8=TRUE, B2,NA()). Meaning if 2018 checkbox is selected then value in B8 will be TRUE only then B12 will show the sales value otherwise will show #NA.
- Repeat the same step 2 for C12-E12 and B13 – E13.
- Now select the range. Go to Insert tab, select Charts and select Line with Markers. This will insert the line chart for 2018 & 2019
- Voila chart is now created you can check the checkboxes to see if they’re working or not.
Deleting Checkbox in Excel
So there are two main ways to delete excel checkbox.
- Just select the checkbox or hold CTRL key to select multiple checkboxes and press Delete key on your keyboard. This works best if you only have a few checkboxes in excel.
- If you have multiple checkboxes all scattered at different parts then, go to Home tab select Find & Select and choose Selection Pane.
It’ll open Selection Pane, here just hold CTRL key and select all the checkboxes you wish to delete. Press the Delete key.
Do note: That the names here are backend names and not the caption names of excel check box.
Conclusion
That was all about how to insert checkbox in excel.
I hope this article was helpful to you and explained you all about creating checkbox in excel, building checklist for excel, building dynamic charts using checkboxes and much more.
If you have any questions or tips, I’d love to hear them in the comment section below.
Advanced Excel Course
Learn Vlookup, Pivot tables, What IF, Macro Automation, Power Query and much more.
- 10+Hrs of Learning Content
- 50+ Practical Exercises
- Certification
- Doubt Resolution