by The tutorial will teach you how to quickly add multiple checkboxes in Excel, change the check box name and formatting, as well as delete one, several or all checkboxes on a sheet.
In last week's tutorial, we stared to discuss Excel Check Box and showed a few examples of using checkboxes in Excel to create a beautiful checklist, conditionally formatted To-Do list, interactive report and a dynamic chart responding to the checkbox state.
Today, we will be focusing mostly on technicalities and how-to things. Of course, this information is not as exciting to learn as practical examples, but it will help you create and manage your Excel checkboxes in the most efficient manner.
Check Box Form control vs. Check Box ActiveX control
Microsoft Excel provides two types of controls - Check Box Form control and Check Box ActiveX control:
Form controls are much simpler than ActiveX, and you will want to use them in most cases. If you decide to go with Check Box ActiveX controls, here's a list of the most essential differences for you to consider:
- ActiveX controls provide more formatting options, you may want to use them when you look for a sophisticated and flexible design.
- Whereas Form controls are built into Excel, ActiveX controls are loaded separately and therefore they may occasionally freeze or "misbehave".
- Many computers don't trust ActiveX by default, as the result your Check Box ActiveX controls may be disabled until you have enabled them manually via the Trust Center.
- Unlike Form controls, Check Box ActiveX controls can be accessed programmatically via the VBA editor.
- ActiveX is solely the Windows option, Mac OS doesn't support it.
How to add a checkbox in Excel
To insert a checkbox in Excel, do the following:
- On the Developer tab, in the Controls group, click Insert, and select Check Box under Form Controls or ActiveX Controls.
- Click in the cell where you want to insert the checkbox, and it will immediately appear near that cell.
- To properly position the check box, hover your mouse over it and as soon as the cursor changes to a four-pointed arrow, drag the checkbox to the desired position.
- Optionally, delete or change the caption text.
Note. If you don't have the Developer tab on your Excel ribbon, right click anywhere on the ribbon, then click Customize the Ribbon… The Excel Options dialog window will appear, and you check the Developer box in the right hand column.
How to insert multiple checkboxes in Excel (copy checkboxes)
To quickly insert multiple check boxes in Excel, add one checkbox as described above, and then copy it using one of the following techniques:
- The fastest way to copy a checkbox in Excel is this - select one or several checkboxes, and press Ctrl + D to copy and paste it. This will produce the following result:
- To copy a checkbox to a specific location, select the checkbox, press Ctrl + C to copy it, right-click the destination cell, and then select Paste in the pop-up menu.
- To copy a checkbox into adjacent cells, use the arrow keys to select the cell containing the checkbox (not the checkbox itself!), and then drag the fill handle (a small square at the lower right-hand corner of the cell) down or to the right.
Notes:
- The caption names of all the copied checkboxes are the same, but the backend names are different since each Excel object has a unique name.
- If the original checkbox is linked to a cell, all of the copied checkboxes will be linked to the same cell. You will have to change the linked cell for each checkbox individually.
How to change the checkbox name and caption text
When using checkboxes in Excel, you should distinguish between the check box name and caption name.
The caption name is the text you see in a newly added checkbox such as Check Box 1. To change the caption name, right click the checkbox, select Edit Text in the context menu, and type the name you want.
The checkbox name is the name you see in the Name box when the checkbox is selected. To change it, select the check box, and type the desired name in the Name box.
Note. Changing the caption name does not change the actual name of the checkbox.
How to select a checkbox in Excel
You can select a single checkbox in 2 ways:
- Right click the checkbox, and then click anywhere within it.
- Click on the checkbox while holding the Ctrl key.
To select multiple checkboxes in Excel, do one of the following:
- Press and hold the Ctrl key, and then click on the checkboxes you want to select.
- On the Home tab, in the Editing group, click Find & Select > Selection Pane. This will open a pane in the right-hand side of your worksheet that lists all of the sheet's objects including checkboxes, charts, shapes, etc. To select multiple checkboxes, just click their names on the pane holding the Ctrl key.
Note. The names displayed on the Selection pane are the checkboxes names, not caption names.
How to delete a checkbox in Excel
Deleting an individual checkbox is easy - select it and press the Delete key on your keyboard.
To delete multiple checkboxes, select them using any of the methods described above, and hit Delete.
To delete all checkboxes at a time, go to the Home tab > Editing group > Find & Select > Go To Special, select the Objects radio button, and click OK. This will select all the check boxes on the active sheet, and you simply press the Delete key to remove them.
Note. Please be careful when using the last method because it will delete all the objects in the active sheet, including checkboxes, buttons, shapes, charts, etc.
How to format checkboxes in Excel
The Check Box Form control type does not allow many customizations, but certain adjustments can still be made. To access the formatting options, right-click the checkbox, click Format Control, and then do any of the following.
On the Color and Lines tab, you can select the desired Fill and Line:
No other changes are allowed for a Check Box Form control in terms of formatting. If you need more options, e.g. setting your own font type, font size, or font style, use a Check Box ActiveX control.
The Size tab, as its name suggests, allows changing the size of the checkbox.
The Protection tab allows locking and unlocking checkboxes. For the locking to take effect, you need to protect the sheet.
The Properties tab lets you position a checkbox in a sheet. The default setting - Move but don't size with cells - ties the check box to the cell where you've placed it.
- If you want to fix the position of a checkbox in the sheet, for instance at the very top of the sheet, select the Don't move or size with cells option. Now, now matter how many cells, rows or columns you add or delete, the checkbox will stay where you put it.
- If you want the checkbox to be printed when you print out a worksheet, make sure the Print object box is selected.
On the Alt Text tab, you can specify the Alternative text for the checkbox. By default, it is the same as the checkbox's caption name.
On the Control tab, you can set the initial state(default state) for the check box such as:
- Checked - displays a check box filled with a checkmark.
- Unchecked - displays the check box without a check symbol.
- Mixed - displays a check box filled with shading that indicates a combination of selected and cleared states. It can be useful, for example, when creating nested checkboxes using VBA.
To give a slightly different look to the check box, turn on 3-D shading.
To link a checkbox to a certain cell, enter the cell address in the Cell link box. You can find more about linked cells and what advantages this gives to you here: How to link checkbox to cell.
This is how you can add, change or delete a checkbox in Excel. If you are looking for real-life examples of using checkboxes in Excel, please check out the following resources.
63 comments
I copied rows of data from QuickBooks and pasted it into Excel 365. Every row has a checkbox. I've tried going to "Special" but nothing happens...no dialog box with a list of special items, so I cannot delete them. I've also tried Find & Select > Selected Objects, but it doesn't select any of them. Any ideas what I can try? Thanks
I saved the file, closed it, and then reopened it. Then the Select & Find > Go to Special dialog box appeared and the selection of objects worked perfectly. Thanks.
I feel like it might be worth mentioning that if you reference the cell the checkbox is in and resize the box to cover the word FALSE, you can hide the TRUE/FALSE text on your sheet. Keeps it a little cleaner and doesn't take up extra cells if your sheet is already getting crowded.
I have an existing list. My question is how do I add one more checkbox to my existing list. I am only seeing how to delete a checkbox.
How do I edit by adding an additional checkbox
Hi!
The article above describes how to add or copy a checkbox. If that's not what you want, please explain in more detail.
Hello,
I would like to know if there is a way to have 2 sets of checkbox and if I select the 1st, the 2nd will be automatically grayed out? or does it need to have VBA script?
All the best!
Gail
or use radio buttons instead...
Hi!
Yes, this is possible with VBA.
I would like to make a sheet with 5 options next to each employee. How does one separate or group the selections so that the each employee's selection is independent, and doesn't change the next employees selection?
Hi!
I’m sorry but your task is not entirely clear to me.
Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better.
I think Shaun wants to have a list of employees and 5 check boxes next to each employee. The issue I think he is having is that when a checkbox is selected for Employee #1, it disappears when a box is checked for Employee #2.
How can you have multiple groupings of check boxes or radio buttons that pertain to different questions on the spreadsheet so that you may choose more than 1 button.
I want to have 5 radio buttons for the type of building (house, garage, Medical building, etc.) and then have a yes or no question for whether it is ADA compliant or not.
If I choose a structure and then click on "yes" the structure choice disappears and I'm left with only "yes".
How do I ungroup the groups of radio buttons?
Thanks!
Searching in the WEB for answers, I am coming across your unique tutorials - in this case, the only one explaining how to delete a checkbox. Thank you, Svetlana!
In Excel 2016,when I install a check box and try to copy it down 10 cells using the fill button it does not copy the check box. Is there something in Options that prevents fill copying the check box?
Thanks! That helped me delete the check boxes so fast. :)
Is there a way to hide the checkboxes in case I have to hide a section of rows? Is there a type of checkbox or property I have to check for each checkbox that let's it know to hide if the row is hidden too?
When using the Sort/Filter feature, unwanted lines are removed by the filter value, but the check boxes remain, stacked on top of each other. Is there a way to remove the check boxes as well so that only the desired lines and check boxes remain after using sort/filter?
When I attempt to copy my checkbox the message I get after copying is that "The image cannot be displayed".... any suggestions?
Hi,
To delete one check box, go to HOME TAB, click on Find& Select list, then click on SELECT OBJECTS, then click on the check box that you want to remove, hit Delete. That way you only delete one at a time, if you wish to keep the rest of check boxes:)
so, so helpful!!!! Saved me hours of work!
This does not seem to have a practical use for me.
When I click and drag the cell, it does copy the checkbox, but if the checkbox has a reference cell, then all the checkboxes remain the same - i.e. when you select one of them, then all of them get selected.
This is what I'm having trouble with too. It seems that when you copy formatting of one object, the linked cell remains the same across all of them with no way to do a series fill in regard to the linked cell. I have racked my brain trying to find a way to make this an efficient action when working with a large table of check boxes, but I just can't seem to find a solution other than creating a cell link for each one individually
Hello and thank you for your post and time.
Can we have multiple check-boxes in the same cell??
Hi Svetlana,
Looks like this question has been asked many times and I was wondering if you coould give us a little direction, please. I am copyingthe questiond from a previous user as it is consise:
We add a checkbox and link it to cell C1.
We drag it down until cell C100.
Unfortunately all the checkboxes are linked to cell C1.
Is there a way to drag the checkboxes until cell C100 and each one to have a different link?
We'd really appreciate your feedback.
Thank you.
Art
Hi Art,
To my best knowledge, this cannot be done automatically. How can Excel know what cells you wish the copied checkboxed to be linked to? Of course, nothing prevents you from changing the linked cell for each individual checkbox after copying.
In my view, Excel should be smart enough to allows you to drag and drop checkboxes and populate the linked cell to the same relative cell so if the check box is place in the position of cell D2 and linked to cell c2, if you have 1000 rows that need a check box, you should be able to drag the check box down 1000 rows and have it automatically populate the links with corresponding adjacent row C values (C3, C4, ... C1001). Of course with thousands of programmers such conveniences are below them. Forgive my sarcasm but with the average of size of data more and more frequently exceeding a million rows and a 5000 columns, Excel has not matured substantially in decades.
Dear Svetlana, if we have so many checkboxes it is very time-consuming to do it step by step... You are saying that there is no way to multiple format cells (like said above - in the same row -if B1 is linked to C1, then automatically link B2 to C2, B3 to C3...? Thanks in advance, Ivana
Dear Ivana,
I totally agree with you - it's very inconvenient and time-consuming to edit each individual checkbox. Unfortunately, I don't know a way to do this automatically. Even if you change an absolute cell reference to relative, the copied checkboxes are still linked to the same cell. If you manage to find a way, please do share your finding here!
please share when you find a solution. Ive been searching for this for weeks!
Hi Svetlana,
when I insert multiple checkboxes in one cell and copy them to the next the first box gets a bit higher up, overwriting the cell on top. No matter how I adjust the hight of the rows..
Thanks!
What is the method to make tick box like in MCQs? .only one option can be mark at a time?
Is there a way to populate a whole column with checkboxes so that each box is linked to its own cell?
I have deleted the "embedded" activex formula from the checkbox while attempting to change the text box name. I cannot close the sheet, delete the box or do anything else until i fix the invalid reference error.