Excel Tip of the Week: Check Box

Today’s topic is how to create a check box.

Check boxes are technically an advanced Excel feature and somewhat hidden in Excel. But once you know how to create one, it is relatively simple to use.

Before we jump into creating check boxes, the first step is to enable the “Developer” tab in the Excel ribbon. By default this tab is hidden, and it is where you’ll find the check boxes among many other useful advanced Excel features. Here is how to enable the Developer Tab:

Windows:

  1. Click the File tab.
  2. Click Options.
  3. In the categories pane, click Customize Ribbon.
  4. In the list of main tabs, select Developer.
  5. Click OK to close the Options dialog box.

Mac:

  1. Click on the ‘Excel’ menu in the menu bar
  2. Click on Preferences
  3. Click on the Ribbon icon
  4. Select the Developer Tab
  5. Click OK

Check boxes give you the ability to toggle between two scenarios. For example, I’ve used them to toggle between a simple or detailed assumption. Or you could use it to turn things on and off. Let’s look at an example. First download this spreadsheet.

Take a look at the example already created. I have a list of products and their costs. Using the check boxes (and a simple IF formula) the spreadsheet is more dynamic and able to calculate the total costs based on your preferences. Try clicking some of the check boxes and watch how the spreadsheet changes.

One thing to note is that normally the “TRUE/FALSE” column would be hidden or out of the way, but I left it in this spreadsheet for teaching purposes. Now it is time for you to create your own check box.

  1. From the Developer Tab click on Insert and then click on the Check Box icon (Keyboard Shortcut Alt → L → I → H). For a Mac the check Box is just right there as its own icon, no need to click “Insert.”
  2. You should notice your mouse cursor change to a cross.
  3. Somewhere near cell B11 click and drag your mouse to make a box. Make it about the size you want the check box to be.
  4. You should now see a new check box that is labeled “Check Box” followed by a number.
  5. Right click on the check box and select “Format Control”
  6. Make sure you are on the “Control” tab. Using the “Cell Link” box select D11 as the cell. (You can type it or use the little button to the right of the “Cell Link” box.) Then click OK.
  7. Right click the check box again, and then double click (with the left mouse button). This will allow you to edit the text. Type in “Headphones”
  8. Give the Headphones a price of $50 in cell C11.
  9. Excel might fill in cell E11 for you, but if not just copy the formula from above.
  10. Update the formula in cell H4 to include the headphones. And you’re done. Good job. Here is a screen shot of the final product:

Check Box

I think the hardest part of using check boxes is trying to line them up in the right spot. The best tip I can give you for that is to move it close to where you want it with the mouse and then use the arrow keys to align it just right.

About Doug Midkiff

I’m really good at Excel. I’m also a Texan, which seems to be a trend among OwenBloggers these days (you can’t stop us, you can only hope to contain us). After graduating from Texas, (Hook’em) I spent four years as a financial analyst before finding my way to Owen where I’m concentrating in finance with an emphasis on real estate. I love my wife, indie coffee shops, disc golf, soccer, web comics, Google maps, urbanism, sustainability, and warm weather.
This entry was posted in Doug Midkiff '12, Excel Tip of the Week and tagged , , , . Bookmark the permalink.

26 Responses to Excel Tip of the Week: Check Box

  1. Alfie says:

    I read this article fully regarding the comparison of most recent
    and previous technologies, it’s amazing article.

  2. I just like the valuable information you supply to your articles.
    I will bookmark your weblog and check again right here regularly.
    I’m somewhat sure I will be informed many new stuff proper
    here! Best of luck for the next!

  3. I loved as much as you’ll receive carried out right here.

    The sketch is tasteful, your authored subject matter
    stylish. nonetheless, you command get bought an edginess over that you wish be delivering the following.
    unwell unquestionably come more formerly again as exactly
    the same nearly a lot often inside case you shield this hike.

  4. I every time spent my half an hour to read this blog’s content every day along with a cup of coffee.

  5. I will immediately clutch your rss as I can
    not to find your e-mail subscription link or newsletter service.
    Do you have any? Kindly allow me know in order that I
    may just subscribe. Thanks.

  6. It’s not my first time to pay a quick visit this web page, i
    am browsing this web page dailly and take fastidious data from here every day.

  7. via says:

    Hi just want to ask if theres a simple way of adding a checkbox in excel and when the box is ticked a certain text will come up?thank u really need your expertise

  8. Elliott says:

    Doug,

    Is there anyway that once the check box is clicked that I can have that be represented by a percantage in a different column? Example: Lets say I have 9 check boxes in row “A” and 3 of the 9 have been checked. The percentage of checked boxes to non checked boxes would obviously be 33%. I would like that figure to be represented in the last column in that row. How is this achieved?

  9. Article writing is also a excitement, if you know afterward you can write otherwise it is complex to write.

  10. Carmen says:

    Hi Doug,

    Can the checkbox be transformed into data?

    Using your example, the content would be transposed and applied to a staff member:
    Staff A wants: Laptop, mouse, keyboard.
    Staff B wants: Desktop, mouse, keyboard, monitor
    Staff C wants: Laptop, tablet.

    I now want to do a count of how many laptops, etc.

    I have been given a large set of data to analyze and checkboxes are heavily used. This is making it difficult for me to manipulate the data……

    Your help would be greatly appreciated!

    Carmen.

    • Doug Midkiff says:

      Carmen,

      If you set up the control cells strategically you could probably use countif. If you check out my archives you’ll find a tutorial on that function.

      Perhaps I’m not fully understanding your question though.

      Good luck.

  11. Albert says:

    Hi Doug,

    Is it possible to return in a single cell the items that we have checked. i.e. a cell that displays “laptop, desktop and tablet” when one clicks those check boxes.

  12. Lloyd Clough says:

    Hi Doug,

    I am using the checkboxes you have mentioned above – is there any way I can get rid of the black border around each box?

    Many Thanks, Lloyd

  13. Also, to provide extra benefits like insurance to its
    customers the company has tied up with New India Assurance Ltd.
    Income tax Act handles these underneath two diverse sections these kinds of as:
    sec 24(b) and part eighty(do) of the 1961 earnings tax act.

    All these are some of the best places to visit in India.

  14. Hello, I think your site might be having browser compatibility issues.
    When I look at your blog site in Ie, it looks fine but when opening in Internet
    Explorer, it has some overlapping. I just wanted to give you a quick heads up!
    Other then that, excellent blog!

  15. Daniella says:

    Hi Doug,

    This was very helpful. How can I make the TRUE/FALSE column be hidden?
    Thanks!

  16. Glenn Gray says:

    Thank you for this very helpful example.

    I would like to create a column of checkboxes, as you have done. Can you offer any tips for doing that? Using “fill” or copy and paste, copies the original object and doesn’t permit me to change the related logical linked cells. i.e. Following your example, I would click on my equivalent to your “Headphones” and all the remaining checkboxes turn TRUE. Do I have to insert each individual checkbox individually?

    • Doug Midkiff says:

      As far as I know, there is not a quick way to insert a bunch of check boxes and have them all link to different cells. It is a one-by-one thing unless you create a macro to do it for you. Good luck.

  17. Barrie says:

    Excel 2010 does not have “….categories pane, …..” So how can you click Customize Ribbon.

    • Doug Midkiff says:

      I guess it isn’t labeled as the “categories pane” but I meant the menu of options on the left side of the dialog box that pops up when you select Options from the File menu. Hope that helps.

  18. Michael says:

    Hi Doug,

    Much appreciated – very straightforward and made easy to implement!

  19. Doug Midkiff says:

    Jennifer, thanks for pointing this out. I have gone in and fixed the problem, so the spreadsheet should work now.

  20. Jennifer says:

    Hi Doug,

    Thanks for your fabulous sharing. And I am sorry that the spreadsheet in Check Box Excel Tip cannot be downloaded. Could you please upload it once again?

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>