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.

67 Responses to Excel Tip of the Week: Check Box

  1. bottoom as drainage is critical. Available ones inexpensive variant with a plastic-type 5 quart(18.9 actu) Frozen treats

  2. go here says:

    Hi, its nice article about media print, we all be
    familiar with media is a enormous source of facts.

  3. el paso says:

    I’ve been exploring for a little for any high quality articles or weblog posts in this sort of space . Exploring in Yahoo I ultimately stumbled upon this website. Reading this information So i’m happy to express that I’ve an incredibly just right uncanny feeling I came upon just what I needed. I such a lot certainly will make sure to don?t forget this site and provides it a look on a relentless basis.

  4. I feel that is one of the most important information for me.
    Annd i am satisfied reading your article. But wanna observation on some general issues, The
    site styyle is wonderful, the articles is truly nice :
    D. Excellent task, cheers

    Also visit mmy blog post: first time buyers

  5. Pretty component of content. I simply stumbled upon your website and in accession capital
    to assert that I acquire in fact enjoyed account your blog posts.

    Anyway I’ll be subscribing for your feeds and even I achievement you get admission to persistently quickly.

  6. Hmm it looks like your site ate my first comment (it
    was extremely long) so I guess I’ll just sum it up what
    I wrote and say, I’m thoroughly enjoying your blog.

    I too am an aspiring blog blogger but I’m still new to
    the whole thing. Do you have any points for novice blog writers?
    I’d really appreciate it.

  7. kids kamas says:

    Foam usually stands up to multiple blows without breaking easily, and is safer to hit with.

    If the cooked time is too long, the food will become hard and lose its flavor,
    color and taste. Buying EVE ISK online allows them to buy weapons and items that are required for levelling-up in the gaming
    structure.

    Feel free to surf to my web blog – kids kamas

  8. domain says:

    Good day! Do you use Twitter? I’d like to follow you if that
    would be ok. I’m undoubtedly enjoying your blog and look forward
    to new updates.

  9. Wielce odlotowy post, nietuzinkowe zapisy polecam wszystkim lekturę

    my website … producent placów zabaw

  10. Don says:

    I see you share interesting stuff here, you can earn some extra cash, your website has big
    potential, for the monetizing method, just search in google – K2 advices how to
    monetize a website

  11. Iris says:

    They had help from translator in the contest, which was held in English.
    While it is more than 1,850 miles across the country from northwest to southeast, the width varies from
    less than 135 miles at the Isthmus of Tehuantepec
    to more than 1,200 miles in the north. Trudging through the dark woods, scuffles and the odd
    running animal in the distance lets us know that
    the darkness hides more than the local flora.

  12. Tanto Fernndez como a Urribarri opleve suman any los angeles larga lista signifiant candidatos presidenciales dont existe en el seno del oficialismo, disadvantage el gobernador en el provincia signifiant Buenos Aires, Daniel Scioli, el primero durante anunciar sus intenciones tras alejarse del “ncleo duro” del kirchnerismo.
    Scarpe Tods Donna http://www.prolocobrugherio.it/

  13. It’s a shame you don’t have a donate button!
    I’d most certainly donate to thyis excellent blog!
    I suppose for now i’ll settle for book-marking and adding you RSS feed to my Google account.
    I look forward to brand new updates and will talk about this website
    with my Facebook group. Chat soon!

    myweb page – Working G-SHOCK MLTD Coupon codes

  14. gsc.tab=0 says:

    Excellent blog here! Also your site loads up very fast!

    What web host are you using? Can I get your affiliate link to your host?
    I wish my site loaded up as fast as yours lol

  15. Mohamed says:

    Is there is anyway to create a mutual checkbox, i mean if the first one checked the second or third one can’t be checked at the same time, in other word to pop up an error message that only one choice has to be checked at a time.

    • Doug Midkiff says:

      Mohamed,

      I’ve done this before using VBA. You have to setup an “OnChange” macro that will block users from making a certain selection give your criteria. I can’t think off the top of my head if there is a way to do this strictly in Excel without coding it.

      • Mohamed says:

        many thanks for your replay, actually am not an expert with the coding stuff, so i would be grateful if you can give me much guidance from your end, as i have 4 checkboxes to be mutually exclusive to each other, i just need to block 3 of them if only one was checked by the user and return an error message to him.

  16. At the 2010 rate of consumption, potential shale Air Jordan 2014 can last more than 100 years of use. However, Success accelerated the conflict of two brothers and they decided to break up finally in 1948. Points out something nice about an annoying friend. The price of Air Jordan 3s that contain Matrixyl 3000 has fallen, and if you look out for special offers you can buy enough to try the Air Jordan 3.5 without spending much.
    nike air jordan 3 http://www.maryandmarvyn.com/

  17. Excellent product, the size was exactly as described, the color is same as the picture! The bottom hole came closed, but its simple to open it with scissors! Great, will buy again soon!Nice shoes, but smaller size. I am usually 41 (EU) and I ordered 42, but they are still a bit small. Very fast delivery!

  18. cheap Thunder jerseys for sale from china

  19. cheap Grizzlies authentic jerseys Only Review

  20. wholesale Browns cheap jerseys Review With Mastercard

  21. Eli says:

    Hi, constantly i used to check web site posts here in the early hours in the daylight, since i
    like to gain knowledge of more and more.

  22. I visited multiple blogs but the auɗio feature for
    audio songs existing aat thi web site is really fabulous.

    ʟook at my homepɑge … herve leger dreѕs sale philippineѕ (http://Plusiikoto.com/Onelove/groups/how-for-the-greatest-prices-online/)

  23. I am very happy with the purchase. The bag is. Delivery is made within one month. Yes, I recommend a product to a friendExcellent Seller, Fast Postage, Highly RecommendThank you. Great bag. Great seller. Will be ordering more from him
    sale louis vuitton http://www.maggihambling.com/brand.php?Search=286&sale-louis-vuitton

  24. Good info. Lucky me I discovered your site by chance (stumbleupon).
    I have bookmarked it for later!

    My website: doterra Reviews

  25. Hey would you mind stating which blog platform you’re
    using? I’m going to start my own blog in the
    near future but I’m having a difficult time choosing
    between BlogEngine/Wordpress/B2evolution and Drupal.
    The reason I ask is because your design and style seems different then most blogs and I’m looking for something unique.
    P.S Sorry for getting off-topic but I had to ask!

    Feel free to surf to my blog post – https://youtube.com/watch?v=H94U1sKpsAs

  26. Hi therе! I simplpy want too give you a biɡ thumbs
    up for yohr great inforfmatiօn you have got right here օn this post.
    I ԝill bbe returninhg to yoսr site for more soon.

    Here is my blog; herve leger gold

  27. I needed to thank you for this good read!! I definitely enjoyed every little bit of it.
    I have got you bookmarked to look at new things you post…

    my site :: cheats to get credits on imvu

  28. Great blog here! Also your web site loads up
    very fast! What host are you using? Can I get your affiliate link to your host?
    I wish my website loaded up as fast as yours lol

    my blog post; how to write a press

  29. Shaun says:

    Greetings I am so glad I found your web site,
    I really found you by mistake, while I was searching on Digg for something else, Nonetheless I
    am here now and would just liuke to say many thanks
    for a fantastic post and a all round thrilling blog (I also
    love the theme/design), I don’t have time tto
    look over it all at the minutge buut I have book-marked it annd also added your
    RSS feeds, so when I have time I will be back tto read a great deal more, Please do
    keep up the great work.

  30. Hey would you mind letting me know which hosting company you’re utilizing?
    I’ve loaded your blog in 3 completely different web browsers and I must say this
    blog loads a lot quicker then most. Can you recommend a good
    internet hosting provider at a fair price? Many thanks, I appreciate it!

    my weblog :: best rowing machine

  31. Simply desire to say your article is as astonishing.
    The clearness for your publish is simply nice and
    that i can suppose you’re an expert in this subject.
    Fine along with your permission let me to grab your feed to keep up to date with impending post.
    Thanks one million and please continue the rewarding work.

    Feel free to surf to my web site: best car seats for infants

  32. Nicole says:

    Hi Doug,

    I’m running into a problem with my check boxes – every time I close the sheet, they keep changing size (in my case, the box itself gets smaller, taking up less cells.) Have you ever run into something like this before?

    Best,
    Nicole

    • Doug Midkiff says:

      yeah, that happens with macro buttons in my spreadsheets sometimes. I don’t really know why it happens. I’ve found that if I group all the objects together that sometimes helps.

  33. Gary says:

    Excellent article. I will be going through some of these issues
    as well..

  34. Hayley says:

    Do you mind if I quote a few of your articles as long as I provide credit and sources back to your blog?
    My blog is in the exact same niche as yours and my visitors
    would really benefit from some of the information you provide here.
    Please let me know if this okay with you. Cheers!

    Feel free to visit my webpage … webpage, Hayley,

  35. Definitely believe that which you said. Your favorite
    reason appeared to be at the web the easiest thing to be mindful of.

    I say to you, I definitely get irked at the same time as other folks think about
    worries that they just don’t realize about. You managed to hit the nail upon
    the highest as neatly as outlined out the whole thing without having side-effects , other people could take a signal.
    Will probably be back to get more. Thank you

  36. Alfie says:

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

  37. 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!

  38. 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.

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

  40. 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.

  41. 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.

  42. 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

  43. 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?

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

  45. 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.

  46. 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.

  47. 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

  48. 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.

  49. 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!

  50. Daniella says:

    Hi Doug,

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

  51. 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.

  52. 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.

  53. Michael says:

    Hi Doug,

    Much appreciated – very straightforward and made easy to implement!

  54. Doug Midkiff says:

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

  55. 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>