Format cell as a checkbox (TRUE, FALSE)
Offer a format that displays the boolean value of a cell as a checked box if TRUE, unchecked box if FALSE, grayed box if text or other nonbool.
Bonus: Toggle value OnClick would be intuitive.
Lots of uses for this. Current options are awkward for users. Yes/No dropdown is not as good as a checkbox for touch/mobile.
Anonymous
shared this idea
20 comments
-
Anonymous
commented
I would really like to see this. I have partial files in Google Sheets just for this feature as well as a simple date picker just using data validation. We are pushing hard to use MS Teams with Excel online built into some of the tabs and this missing feature is really lacking. It's always one step forward them two back to meet our needs. We finally got Sheet Views this month but now I need check boxes and a date picker easily implemented in Excel Online.
-
Craig Beuthin
commented
Brilliant - I already have thought of many situations where that would work for me.
-
Jon Wittwer
commented
I'm finding that this feature alone is why I now use Google Sheets for ALL of my to do lists, instead of Excel. Yes, even though I can press ALT+Down to open a drop-down and select a check mark (my make-shift method for checkboxes in Excel), there is just something awesome and fun about clicking on a checkbox that makes the Google Sheets implementation better than work arounds with Excel.
Microsoft, don't forget to pay attention to the little things! You don't have a monopoly on spreadsheets any more, so let competition drive you to become even better.
-
G
commented
@NB: Your excel-hack link couldn't be a better illustration of the need for this. Perfect. Thanks!
-
Samaan
commented
Convert True False To Checkbox Controls With VBA
This section will introduce a VBA macro to convert multiple true and false to checkbox controls in Excel. Please do as follows:1. Select the cells equal to “TRUE” and “FALSE” you will convert to checkboxes.
2. Press Alt + F11 keys together to open the Microsoft Visual Basic for Applications window.
3. Click Insert > Module, and then paste below VBA code into the new Module window.
VBA: Convert TRUE and FALSE to checkbox controls in Excel
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Sub ConvertTrueFalseToCheckbox()
Dim xCB As CheckBox
Dim xRg, xCell As Range
Application.ScreenUpdating = False
On Error Resume Next
Set xRg = Selection
For Each xCB In ActiveSheet.CheckBoxes
xCB.Delete
Next
For Each xCell In xRg
If (UCase(xCell.Value) = "TRUE") Or (UCase(xCell.Value) = "FALSE") Then
Set xCB = ActiveSheet.CheckBoxes.Add(xCell.Left, xCell.Top, cDblCheckboxWidth, xCell.Height)
xCB.Value = xCell.Value
xCB.LinkedCell = xCell.Address
xCB.Text = ""
End If
Next
Application.ScreenUpdating = True
End Sub
4. Press F5 key or click the Run button to run this VBA.Then you will see all selected TRUE and FALSE are converted to checkbox controls. TRUE is converted to a ticked checkbox, and FALSE is converted to an unticked checkbox.
-
NB
commented
Not as easy as Google sheets but this page shows how you can do it all within Excel
-
Allan R Veler
commented
This is one of the features I use the most in Google Sheets, it would be great if it was in Excel as well.
-
TJ
commented
How is this not an option yet? This should've been implemented in the 90s. Cmon!
-
Todd O'Connell
commented
I agree with Tom Christopher. This should be simple. It would make an intuitive cell feature. It is simple for a programmer but difficult for the average user. And the competitor already does it. Will it sell more licenses? I don't know. Will it make the average user happy? Absolutely! Will it make the mobile device version easier to use? You bet! Also, what I know is that, once this feature is added, I double the number of spreadsheets I make in Excel. Imagine if my grocery list/travel list/to-do list is in OneDrive using Excel and I have a checkbox next to each item as I am shopping/traveling.etc. That is an average user creating a list in Excel and the feature that would enable it is just is not there. Disappointing to see a company ignore such an obvious feature.
-
Tom Christopher
commented
yea, look how old the comments are. This has been an issue for years. And Not only mobile but for Mac as well. I used a bunch of code and the Dropdown for an autofill list that only works half the time. Shame on you MSFT. Just spent hours doing a golf scoring sheet using the forms controls only to find it won't work on my phone.
-
Wesley Kenis
commented
this certainly is a pain when working with tables. This proves onces more that nobody as MS ever uses your own products, otherwise YOUR employees would be creating most of the tickets instead of users.
-
Jon Wittwer
commented
One more comment about the Google Sheets implementation ... I really like that you can change the color of the checkbox by simply changing the font color of the cell.
-
Jon Wittwer
commented
Google Sheets has beat Microsoft to this one! See below:
https://support.google.com/docs/answer/7684717
Google Sheets has made adding a checkbox to a cell as easy as going to Insert > Checkbox. The checkbox is actually a data validation option (so Insert > Checkbox is a shortcut). I really like that you can customize what values are used for the checkbox. The default is TRUE/FALSE, but via data validation you can change that to 1/0 or whatever.
-
Kevin Jones
commented
I have implemented this using shapes (and a lot of VBA code) because 1) I need it, 2) forms controls are too large and overlap other rows, 3) ActiveX controls are the work of the devil. This is a much needed feature for any automation work where the user is required to "select" one or more rows of data before proceeding to the next step. Here is a sample: www.zorvek.com/images/FAST-Screen-2.jpg
-
Jon Wittwer
commented
I've been using a drop-down list with the Unicode checkmark symbols lately (✓, ✔) because Unicode characters appear within the drop-down list (as opposed to the wingdings approach). This works great for touch/mobile, but it still requires multiple touches/clicks to select the check from the drop-down. The down side is that the value isn't stored as TRUE/FALSE. You have to use an IF statement or something.
I would like to see a checkbox data validation option done the way it is in the Numbers app, where the value is displayed as a check (or better yet, give the user a way to change what symbol is displayed for TRUE and FALSE), and the value is stored as TRUE or FALSE.
-
james miller
commented
to add to this, you can also use custom cell formatting to get most of the way there.
change the font of the cell to wingdings and change the cell number format to this: [=1]"ü";"û"
then if the cell = 1 you'll get a tick and if its 0 you'll get a cross.
-
Anonymous
commented
Good idea. I think it would be even better if there was a boolean cell formatting. Cells having this formatting could automatically display as checkboxes.
-
Corey Becker
commented
You can link Form/ActiveX checkbox controls to a specific cell.
-
Jason Daniels
commented
Using Excel as a task list or for enabling/disabling assumptions is really common and Form/ActiveX controls are clunky and not easily formatted/resized (not to mention the security headaches). To generalize this idea further, having a "Toggle" option instead of a "Yes/No" would allow the user to set "Value When Checked" and "Value When Unchecked" so that there is no question about what values to test for in other cell formulas (i.e. IF(ToggleVal="True", 1, 0) or IF(ToggleVal=1, 1, 0)). For added bonus points, give the ability to create our own on/off images for maximum design flexibility.
-
bill
commented
rather than a drop down yes/no ability to have a check box that toggles on click. Using the VBA controls are just painful to use and place. I've mimicked this using hyperlinks with windings font as number format and tiny bit of vba code. The fun bit is that once created you just copy/paste it to a new cell no need to try and size/position the thing. Makes check list in no time
