Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Using Check Boxes.

Using Check Boxes

by Allen Wyatt
(last updated December 15, 2016)

11

Many different dialog boxes and forms in the Windows world utilize check boxes. They are handy if you want to provide a way for a user to choose between two options, such as true or false; yes or no. Excel allows you to use check boxes in your worksheets, if desired.

For instance, you may have developed a financial projection worksheet in which you can either account for a particular acquisition or not. In this case, you might want to place a check box at the top of the worksheet. You can then link the status of this check box to another cell, so that if the check box is selected, the value of the cell is True; if it is not selected, the value of the cell is False.

There are two types of check box controls you can insert in your worksheet: a forms control and an ActiveX control. Both do essentially the same thing; here's how you place an ActiveX check box control in your worksheet:

  1. Display the Developer tab of the ribbon.
  2. Click the Insert tool in the Controls group. Excel displays a variety of controls you can insert in your worksheet.
  3. In the ActiveX Controls area, click once on the Check Box control. The mouse pointer changes to a crosshair.
  4. In your worksheet area, click once where you want your check box to appear. Excel adds the check box to the worksheet.
  5. Use the handles that surround the check box to adjust the size of the control, if desired.
  6. Move the mouse cursor into the label area and change the label to anything desired.
  7. With the check box control you just placed still selected, click on the Properties tool in the Controls group. Excel displays the Properties dialog box for the control. (See Figure 1.)
  8. Figure 1. The Properties dialog box for the check box.

  9. Change the LinkedCell property so it reflects the address of the cell to which this check box should be linked. (When the check box changes, the contents of this cell change; when the contents of the cell are changed, the check box reflects that change—it is a bi-directional relationship.)
  10. Close the Properties dialog box.

If your worksheet will be used with older versions of Excel (those before Excel 2007) you will want to use the forms control check box. Here's how you place them in your worksheet:

  1. Display the Developer tab of the ribbon.
  2. Click the Insert tool in the Controls group. Excel displays a variety of controls you can insert in your worksheet.
  3. In the Form Controls area, click once on the Check Box control. The mouse pointer changes to a crosshair.
  4. In your worksheet area, click once where you want your check box to appear. Excel adds the check box to the worksheet.
  5. Use the handles that surround the check box to adjust the size of the control, if desired.
  6. Move the mouse cursor into the label area and change the label to anything desired.
  7. With the check box control you just placed still selected, click the Properties tool in the Controls group. Excel displays the Format Control dialog box.
  8. Make sure the Control tab is selected. (See Figure 2.)
  9. Figure 2. The Control tab of the Format Control dialog box.

  10. In the Cell Link field, specify the address of the cell to which this check box should be linked. (When the check box changes, the contents of this cell change; when the contents of the cell are changed, the check box reflects that change—it is a bi-directional relationship.)
  11. Click on OK.

As you are specifying cells for the check boxes to link to, it may be helpful to put those cells either on a different worksheet or size your check box so it completely covers the cell to which the check box is linked. That way the "True" and "False" values showing up in the linked cells won't mess up the layout design for the worksheet on which the check boxes appear.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8392) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Using Check Boxes.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Adjusting Small Caps Text

If you use small caps text in a document, you know that there are several steps involved in properly formatting the text. ...

Discover More

Turning Off Capital Corrections

If you type two capital letters at the beginning of a word, Word assumes that you made a typing error and will attempt to ...

Discover More

Reapplying Styles Seamlessly

Styles are a very powerful feature in Word, as they allow you to consistently apply complex formatting throughout a document ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Using Subtotals and Totals

You can insert subtotals and totals in your worksheets by using either a formula or specialized tools. This tip explains how ...

Discover More

Fixed-Width Settings when Converting Text to Columns

The Convert Text to Columns capabilities of Excel are very helpful when pulling apart information. When working with ...

Discover More

Non-Printing Controls

Don't want your form controls to print out with your worksheet? Here's how to make sure that Excel excludes them from the ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is three minus 2?

2017-02-03 05:00:39

Ahiko

Hi!
I would like to put a checkbox that you can only check once. Can it be done?
what do I need to do?

thanks for your help!
Ahiko


2016-05-05 19:56:05

Ian Voden

Can omeone please translate this in to terms an idiot can understand because this is one of the most confusing things I have ever read when it comes to excel.


2015-12-14 16:47:46

Patrick

I'm trying to uncheck boxes using vba excel macro. This is the code:
If CheckBox786.Value = 1 Then
CheckBox786.Value = 0
Swapping 1 and 0 for true false results in same error: object require error in procedure cell check. Not sure what this means. This applies to active worksheet.


2015-09-10 19:04:26

Jeff Rick

I have a checkbox tied to a specific cell and an adjacent cell formated based on the checkbox. When I input data into the adjacent cell (with Wrap Text on) the row size increases and I want the check box to move with the cell. In the Format Control | Properties tab, the "Move and size with cells" option is grayed out so I can't select it. Why would that be? And if I can select it, is that what I need to make the checkbox stay with the cell as the cell size adjust based on data entered?


2015-05-16 10:50:04

Barry

@Mark/John,

The following macro will change the linked cell to be 2 columns over from the cell with the top left corner of the control in it. You can, of course, change this to reference the linked cell based on your own criteria.

"Form" controls are referenced differently from "ActiveX" controls and differentiated by their "Type" property using the Select Case structure.

Sub CheckboxesLinkedCell()
Dim chk As Shape
Dim lCol As Integer

lCol = 2
For Each chk In ActiveSheet.Shapes
On Error GoTo 0
With chk
Select Case .Type
Case 8
.ControlFormat.LinkedCell = .TopLeftCell.Offset(0, lCol).Address
Debug.Print .Name, .Type, .ControlFormat.LinkedCell
Case 12
ActiveSheet.OLEObjects(.Name).LinkedCell = .TopLeftCell.Offset(0, lCol).Address
Debug.Print .Name, .Type, ActiveSheet.OLEObjects(.Name).LinkedCell
End Select
End With
Next chk

End Sub

The Debug.Print lines of code can be omitted, but serves tp provide a useful list of each checkboxes control name.


2015-05-15 05:54:39

Mark Hollingbery

Try this instead.

Insert all cell borders to make a grid.

Insert a tick sign in cells as required from Insert Symbols menu (ü in Wingdings font or use formula = char(252) and set font as Wingdings).

Now combine sumif and istext functions to analyse your data.


2015-05-15 05:25:02

Mark Hollingbery

I hope so, but I haven't found it!


2015-04-22 11:56:12

John Monahan

I quickly got it to work as described. I have a row with 8 boxes, but I want to have copy this row ~>100 times. Is there a (non-manual) way of updating the LinkedCell address for each check box cell?
I tried various copy & paste options without success.
thanks
John


2015-02-15 09:59:38

jMaxwell

Extremely Helpful. Thank you for your help and dedication to the uned.


2014-04-01 18:59:49

Craig Small

There are 2 possibilities. When using the Active-X controls, "Design Mode" (Developer tab) must be turned off before the control is "live". The other thing to be aware of is, depending on your Trust Centre settings, you may have to instruct Excel to "enable this content" if the "Security Warning" is given when you open the workbook.

Hope this helps.

Regards,
Craig


2014-03-31 09:15:00

Doug Rosequist

I am using MS Excel 2010. When I use the checkbox from the CheckBox Controls area, I can check and uncheck it from within the worksheet. However, when I use the checkbox from the ActiveX Controls area, I cannot check and uncheck the checkbox within the worksheet. I either have to change the value in the linked cell or in the properties window. Why is that?


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.