Microsoft Excel is equipped with so many great features that we don’t know about. You can easily add check boxes, options buttons, toggle buttons and many things which we don’t even know. Check boxes can be used to indicate an opposite and clear-cut choice. You can check more than one check box at a time in a data sheet to show the options.
In order to add check boxes in your data sheets, follow the simple steps below.
- Launch Microsoft Excel 2007. First of all, we have to add the Developer tab appear in Excel 2007. If it’s not enabled, click on the Office button.
- Then, go to Excel Options.
- In the Popular tab, check Show Developer tab in Ribbon checkbox and click OK.
- Now, it’s time to add the check boxes. Go to the Developer tab, click on Insert and under Form Controls, you will see a checkbox icon.
- Once you click the check box icon, you will see a plus sign pointer. Click where you want to add the checkbox.
- Now, right click on the control and go to Format Control
- Under Value, select the initial state of the check box. Whether you want to be checked, unchecked or mixed.
- In Cell Link, select the cell in which you want to show the status of checkbox and click Ok.
- Note one thing, when the checkbox is selected, the linked cell shows a TRUE value. On the other hand, if it’s cleared, it will show a FALSE value.
- Right click on the checkbox and click Edit Text to change the text of the check box.
- And you are done.
If you link this guide, you may like our guides on how to import Access Database in Excel and how to embed Excel worksheet in Word.
If you have any tips on Excel or Office tools do share with us through your comments.
6 Comments
nice trick hammad…. very nicely explained.. 🙂
thnx..really helpfull keep it up
Those checkboxes are cool but incredibly hard to work with on a spreadsheet. What most people are looking for are checkboxes that can be double-clicked, contain a value, and (most importantly) ARE the cell vs. sitting on top of it. Reason being, when you use these macro check boxes and you sort the columns or rows the checkboxes don’t move or hide as well. Plus there’s the whole issue of aligning them, assigning a cell to each and every one of them (horrid), etc., etc. To me, this is painful – and useful only if you know your spreadsheet will never change. You can very easily make the checkboxes be the cells themselves with a little VB trick I found on the web (I’m not the author). Decide on the cell or range of cells you want the checkboxes to be in (e.g., A:A or A1:A25, etc.). Right click on the spreadsheet tab you’re working in and select “view code”. In the VB window you will see a panel with two dropdown boxes. Select “worksheet” from the one on the left, and “beforedoubleclick” from the one on the right. You should see this auto-generated for you…
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
End Sub
Now (assuming A1:A25 was your desired checkbox range) paste the following between the first and last lines…
Set t = Target
Set a = Range(“c3:c26”)
If Intersect(t, a) Is Nothing Then Exit Sub
Cancel = True
If t.Value = “” Then
t.Value = “a”
t.Font.Name = “Marlett”
Else
t.Value = “”
End If
The result should be that you now have a cell embedded doubleclickable check box that, when checked, has a cell value of “a”, which you can then use for true/false, if/then, conditional, or any number of computations.
This was from someone called Gary”s Student – gsnu200827 posted at http://www.eggheadcafe.com/software/aspnet/33825051/creating-a-check-box-within-a-cell-.aspx
Cheers,
D
Thanks for this!!!! So helpful!
Good, but how to I quickly add a checkbox to each row of my woork book?
Hi, I would like to know how can I create a drop-down checkbox list? I want to use a cell where I will have drop-down options (list of software to purchase) and then checkbox against them, where I can select which softwwares I want to buy and then based on the software’s I select a total $ amount will be printed on a different cell. How can I do that?
Appreciate your help in solving this.
Thanks,