How to add, copy and delete multiple checkboxes in Excel

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:
Check Box From control vs. 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:

  1. On the Developer tab, in the Controls group, click Insert, and select Check Box under Form Controls or ActiveX Controls.
  2. Click in the cell where you want to insert the checkbox, and it will immediately appear near that cell.
  3. 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.
  4. Optionally, delete or change the caption text.

Adding a checkbox in Excel

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:
    The fastest way to copy a checkbox in Excel
  • 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.
    Copy a checkbox to a specific location.
  • 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.
    Copy a checkbox down the column
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.
Changing the checkbox's Caption name

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.
Changing the checkbox name

Note. Changing the caption name does not change the actual name of the checkbox.
The check box name vs. caption name

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.

Select multiple checkboxes in Excel.

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.
Deleting all checkboxes on the sheet

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:

Select the desired Fill and Line colors for the checkbox control.

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.
    Fixing the position of a checkbox in the sheet

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.

Set the default state of the check box.

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.

You may also be interested in:

17 Responses to "How to add, copy and delete multiple checkboxes in Excel"

  1. bryan says:

    Please enjoy this

  2. Slamat Ali says:

    Thanks, informatic

  3. wm says:

    If multiple check boxes have moved upon saving is there anyway to have them revert back to their original positions.

    The document is very large and for some reason on 1 page of a workbook of 7 all check boxes on all lines moved up and to the left 4 cells.

  4. Chandra Shekhar says:

    Thank you so much for this tip. After downloading my account statement in xls file I was disappointed to see a plethora of check boxes and drop down menus placed haphazardly in various cells. Your tip helped me to delete all the unnecessary check boxes and drop down menus through 'go to special' feature. Thanks once again.

  5. David says:

    Useless. No more than the most basic information provided by Microsoft already. Why waste the time to duplicate and post it?

  6. Michael says:

    A question about inserting check boxes in multiple rows.
    Can I use a conditional formatting to have a check box be inserted in a cell if certain conditions are met in other columns?

  7. Pavel says:

    Great stuff! Thank you.

  8. Jett says:

    Many thanks! Very helpful.

  9. David says:

    Hi
    I have linked a cell to a check box so that when its ticked the linked cell goes green. The check box is positioned over on the linked cell.
    I have dragged that cell down about 100 times so the following 100 cells share the same format and cell locations, etc.
    My problem is that since ive dragged down from, say cell A1, all the following check boxes copy A1's checkbox.
    So if i tick A1 checkbox, all other check boxes go ticked.
    Is there a way I can link each check box to the cell its above without having the manually change each one in the format settings?
    Ive tried writing it as $A1 and then dragging so the numeric part isn't fixed with a dollar sign but no luck.

  10. dennis says:

    is it possible to use a checkbox as a filtering mechanism. for instance, i have several columns in which a box is checked if the criteria is met for that row. for example in this spreadsheet for political supporters, i have columns for Veterans, 2nd Amendment, Women, State Party Member, etc. I would like to put a checkbox at the top of each column, that when checked, would then filter the display to only show those rows that have been checked for that criteria. that way i can see only the veterans, or only the women, etc.

  11. Walter says:

    Amazing, thank you!

  12. Anna says:

    Hi Svetlana,

    I have a couple questions.

    Do you know how to resize checkbox that contains the check mark only and not where the text is written.

    I have a couple checkbox on my form, but I need to remove only one. I'm unable to grab checkbox and remove it. Please advise how to do it.

    Thank you.

    • Dylan Cooney says:

      Use the control button when trying to click the check box. This should allow you to select and edit rather than make the check mark appear/disappear.

  13. Lynette says:

    I am building a spreadsheet using checkboxes in a calculation. But somehow I have a series of checkboxes where when I check one, they all show a check mark. How did I do that, and how can I make it go away?

    Thank you!

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
40
Ultimate Suite 2018.5 for Excel
40
Christmas sale
Best Price of the Year. Dec. 18 – 28