Do you know how to insert checkbox in excel?
I bet you know.
But do you know how to use it up to its best?
If no, I’ll tell you.
In this post, I will show you how to insert checkbox in excel.
But, along with that, I will show you some tips to customize it & some examples to inspire you to use the checkbox in excel.
Table of Content
- Insert a CheckBox.
- Link a Checkbox to a Cell
- Deleting a Checkbox
- Printing a Checkbox
- Resizing a Checkbox
- Copy a Checkbox in Multiple Cells
- Renaming a Checkbox
- Fixing the Position
- Hide or Unhide a Checkbox
- Inspiring Examples
Steps To Insert CheckBox In Excel
I have listed two different methods here to insert a checkbox in excel.
You can use the method which you think is convenient for you.
Here are the steps to insert a checkbox in excel.
- Go to Developer Tab & if you are unable to see developer tab in your ribbon, you can use these simple steps to enable it.
- In Developer Tab, go to Controls → Form Controls → Select Checkbox.
- After selecting check box click on the place on your worksheet where you want to add the checkbox.
Using VBA Code
This is another method to insert a check box in excel. You can use following VBA code to insert a checkbox in your spreadsheet.
ActiveSheet.CheckBoxes.Add(left, Right, Height, Width).Select ActiveSheet.CheckBoxes.Add(80, 40, 72, 72).Select
Using above method is only helpful when you exactly know about the place to insert & size of the checkbox. Learn more about this method from here.
Link a Checkbox with a Cell
Once you insert a checkbox in your sheet. The next thing you have to do is to link that check box with a cell.
Follow these simple steps to do that.
- Right click on the check box & select Format Control.
- Now, you’ll get a format control dialog box.
- Go to Control Tab & in cell link input bar enter cell address of the cell which you want to link with the checkbox.
- Click OK.
- In the cell which you have linked with your checkbox, you’ll get TRUE when you tick the checkbox & FALSE when you untick the checkbox.
Deleting a Checkbox
You can delete a checkbox by using two ways.
- The first way is to select a checkbox & press delete. This is a simple & fast method to do that.
- And if you have more than one checkbox in your worksheet.
- Select all the checkboxes by holding control key.
- And press delete to delete them all.
- A second way is to use selection pane to delete them.
- Go to Home Tab → Editing → Find & Select → Selection Pane.
- In selection pane, you will get the list of all the checkboxes you have used in your worksheet.
- You can select each of them one by one or you can select more than one by using control key.
- Once you select them, press delete.
Printing a Checkbox
Most of the time users avoid printing checkbox while taking printouts. But if you want to do that you can enable this option by using the following way.
- Right-click on the checkbox, select Format Control.
- Go to Properties Tab.
- Tick mark “Print Object”
This will allow you to print checkboxes & if you don’t want to print them make sure to untick this option.
Resizing a Checkbox
- If you want to resize checkbox you can simply expand its size by using dots from the border of the check box.
- And, If you want to resize it using particular height & width, you can do it by using following steps.
- Right-click on the checkbox. Select Format Control.
- Go to Size → Tab Size & Rotate.
- Enter the height & width which you want to fix for the checkbox.
- Click Ok.
Quick Tip: To lock the aspect ratio for the size of the checkbox tick mark “Lock aspect ratio”.
Copy a Checkbox in Multiple Cells
You can use three different ways to copy a checkbox from one place to another
- Simple Copy Paste Method
- Select a checkbox.
- Right-click on it & copy.
- Go to the cell where you want to paste that checkbox.
- Right-click & paste.
- Use Control Key
- Select a checkbox.
- Press control key.
- Now, with your mouse drag that checkbox to the another place where you want to paste it.
- Using Fill Handle
- Select the cell on which you have your checkbox.
- Use Fill Handle & drag it up to the cell on which you want to copy checkbox.
Renaming a Checkbox
While renaming a check box two things you have to keep in mind that there are two names associate with a checkbox, one is Caption Name & other is the actual name of a checkbox.
- Change Caption Name
- Right-Click & select edit text.
- Delete the default text & enter new text.
- Change Actual Name
- Right-Click on the check box.
- Go to address bar & edit the name.
- Change to the name you want.
- Press enter.
Fixing the Position
One thing you have to take care about when you are working with checkboxes that fixing it position.
By default when you insert a checkbox in excel it will change its position & shape when you expand the cell on which it inserted.
But you can easily fix its position using these steps.
- Right, click on the checkbox, go to Format Control → Properties Tab.
- In object positioning, select “don’t move or size with cell”.
- Once you select this option, the checkbox will not move from its position by expanding column or row.
Hide or Unhide a Checkbox
You can also hide or unhide a check box from your worksheet. Follow these simple steps to do that.
- Open selection pane by using the shortcut key Alt + F10.
- On the right side of the name of every checkbox, there is a small icon of an eye.
- Click on that icon to hide a checkbox & same icon again to unhide it.
- If you want to hide all the checkboxes you use to hide all button for that & show all button to show all the checkboxes.
Here I have list three most common & useful idea to use a checkbox in your spreadsheet.
Creating a Checklist
In above example, I have used a checkbox to create a checklist. I have used formulas in conditional formatting to create this checklist.
Here are the steps to create it.
- Insert a check box & link it to a cell.
- Now, Select the cell in which you have task name & Go to Home Tab Styles Conditional Formatting New Rule.
- Click on “Use a formula to determine which cell to format” & enter below formula into it.
- =IF(B1=TRUE,TRUE,FALSE) here B1 is the cell which you have linked with you check box.
- Apply formatting for strike through.
- Click OK.
Now whenever you tick the checkbox it will change the value in the link cell to TRUE & cell in which you have applied conditional formatting will format to strikethrough.
Use Check Box in Dynamic Charts
In above example, I have created a dynamic chart.
Whenever I tick my checkbox it will add a line bar for profit in the chart & here are the steps.
- Create a table with profit values & link it to an another table using below formula.
- I have linked $I$17 with the checkbox.
- Whenever I tick that checkbox, above formula will use VLOOKUP Function to get profit percentages. And, If a checkbox is unticked I’ll get a #N/A.
Use Checkbox to Run a Macro
While working for an Invoice Template I got this idea.
The Idea is, If you want to insert shipping address same as billing address you just have to tick the checkbox & address will be copied to it.
And, on the other side when you untick that checkbox content will be cleared.
Here are the steps to create it.
- Go to Developer Tab → Code Visual Basic or you can also use the shortcut key to open the visual basic editor.
- Add below VBA code to the sheet in which you have inserted your checkbox.
Sub Ship_To_XL() If Range("D15") = True Then Range("D17:D21") = Range("C17:C21") ElseIf Range("D15") = False Then Range("D17:D21").ClearContents Else: MsgBox ("Error!") End If End Sub
Please note you have to insert this code into the code window of the same worksheet in which you want to use it.
- In above code, D17:D21 is the range for shipping address & C17:C21 is the range for the billing address. In cell D15, I have linked the checkbox.
Over To You
I am sure that now you are confident about how to insert checkbox in excel. And, you also know everything abut customizing a checkbox.
I hope you are inspired by my examples.
So, what do you think now about using checkbox?
Do you have any idea to use a checkbox? Please share with me.
Go and hit comment box.
And, do you know anyone who doesn’t know how to insert checkbox in excel?
Please share this guide with him.
What is Next?
Hey, I just want to share with you, my VBA ON/OFF Switch. I hope you can enjoy it. It can work just like checkbox. Have a look.