77

What I'm looking for is a one click enable/disable or toggle in some form. The closest I've come is manually editing a cell to enter a 0 or 1, but I'm hoping there's a nicer pattern.

I know there's no checkbox functionality in a Google Sheets but is there a way to get equivalent functionality somehow?

12

Google launched the last week (April 2018) a feature to insert checkboxes on cells. Just select a cell, the click the menu Insert > Checkbox

For further details see Add & use checkboxes.

NOTES:

  • The default values for checkboxes are TRUE / FALSE but they could be customized to use any values as equivalent of MARK/UNMARKED checkbox. If your cells already have data, select the cells, then select Data > Data Validation... on the Criteria select Checkbox, then a input box will shown for the TRUE / FALSE values to be used.

  • According to Additional details on new macro recorder and formatting options in Google Sheets this could take several weeks to be available for all accounts. In my case this is available for one G Suite account but not yet for my others accounts. Now it should be available for all accounts


Related

  • I already have values in my sheet (0, 1), but if I insert the new checkbox it's always unticked. Is there a way to convert my values into properly ticked or unticked checkboxes? – WoodrowShigeru Jun 21 '18 at 9:03
  • 1
    @WoodrowShigeru see webapps.stackexchange.com/a/119309/183114 for a workaround. – Davide Fiocco Aug 2 '18 at 7:29
  • Too bad, I've done it manually by now. But that seems 1oo% like it would have worked. – WoodrowShigeru Aug 2 '18 at 13:42
73

That was way too convoluted, there's a MUCH simpler way to do it, I've added another field "quick and easy checkboxes", a quick and easy way of doing it is to:

  1. Right click cell, select "Data Validation"
  2. Under Criteria, select "List of Items"
  3. In the "List of items" field, paste this: ☐,☑
  4. Save. You should now have a drop down list of both of the above options.

Voila! No extra sheets, no coding, nothing. If you want to get fancy you can create a "Conditional Formatting" option where ☑ squares are GREEN and ☐ squares are RED.

I have included a sample in the sheet you provided.

  • 5
    ☐,☒ - these are much better looking to my liking – grandrew Nov 13 '16 at 9:56
  • 2
    This is very useful. Ty. I personally found a simple check mark most appealing. In the data validation list I simply added the check mark. This effectively leaves the cell blank and the option to add a check mark :D ✓ – Jarmerson Jan 13 '17 at 15:04
  • 4
    ☐,☑,☒. This is my choice of options. – juanmah Jan 22 '18 at 11:51
11

The best checkbox solution I've seen so far is the one used in this template (Here's a public document created by the template that you can view without logging in to Google Drive).

They have two cells in an extra sheet with a character or image of an unselected checkbox and one of a selected checkbox. Then in the cell where you want to use the checkbox, set the data validation to "Items from a list" and "Create list from range" and select the two cells with the characters/images. You then get a dropdown in that cell where you can select either option.

This doesn't provide the one click on/off toggle, but it's pretty close.

Update: I just set the document to editable by anyone. Without that you wouldn't be able to see how the selection works.

4

I used Matthijs's solution which worked for me. I had a hard time following along and understanding his answer though so here's the step-by-step solution:

  1. Use this template
  2. Copy both of the checkboxes on the "Function" sheet
  3. Return to your Drive file and add another sheet
  4. Name the new sheet "Function"
  5. Paste both checkboxes into cells A1 and A2
  6. Copy the blank checkbox from A1
  7. Return to the sheet on your Drive file where you need the checkbox
  8. Paste the blank checkbox into the cell where you want to have a checkbox
  9. Select Data > Validation
  10. In the "Criteria" dropdown, select "List from a range"
  11. In the field next to that dropdown, enter "Function!A1:A2"
  12. Click "Save". The checkbox should now have a tiny triangle-shaped validation button located at the top of the checkbox cell.
  13. Copy the checkbox & paste into all other cells where you need a checkbox
  • 1
    It's great that you've offered a template, but will your answer still be useful if that link no longer works? – ale Oct 29 '15 at 19:23
  • Thanks for letting me know, I've updated the link, now anyone should be able to open it. – MelanieSazegar Oct 30 '15 at 15:54
  • What would be ideal is for you not to rely on a link as part of your answer; rather, also include instructions on how to set up a spreadsheet like your template. That way, in case you delete your Sheet in the future or something else happens to it, users can still make full use of your answer; feel free to keep the link there, though, as having access to a template does save people some effort. :) – SightSpirit Oct 30 '15 at 16:49
  • Good idea to make a write up like this. Beware that people are going to edit your template though. Mine keeps getting used by people for some sort of webshop catalogue :D – Matthijs Wessels Nov 30 '15 at 22:20
  • @MatthijsWessels yep, you are correct, people have already begun editing the template lol. Ah well, we tried! – MelanieSazegar Dec 2 '15 at 4:30
4

As noted by Rubén Google Sheets now has real checkboxes. Standard TRUE/FALSE checkboxes can be inserted by from the Insert menu (Insert > Checkbox). But you can also insert custom values for the checked and unchecked states by using Data Validation to insert checkboxes (values can be numbers or strings).

Caveats when using the Data Validation method:

  1. Do not first insert checkboxes from the Insert menu. Start with blank cells.
  2. After adding the checkboxes by this method the cell values will not be set to the unchecked value you specify in the data validation window. You have to manually check and uncheck the cells for the value to be updated to that unchecked value.

Google's help page

My blog post about checkboxes in Sheets in which I discuss the above in a bit more detail.

  • This is a new feature. It didn't exist before. But THIS is the correct answer. – oᴉɹǝɥɔ Apr 29 '18 at 3:30
3

Yes, it is possible. http://code.google.com/googleapps/appsscript/articles/bracket_maker.html has a good sample, including how to add a button to a spreadsheet to run some Google Apps Script in Section 4. This can then be used to write the 0/1 to a hidden cell.

  • 4
    Doesn't this approach only allow you to add buttons to the menu? – Matthijs Wessels Mar 6 '13 at 12:28
1

There's another possible option: a button that marks the selected cell as "checked" or "unchecked". This requires two steps: 1) click on cell; 2) click on button to check or uncheck the cell.

  1. Insert image into your spreadsheet ("Insert">"Image...")
  2. You can move it e.g. on the first row, and then freeze this row so it is always visible
  3. Open script editor ("Tools">"Script Editor...")
  4. Copy this function:

     function toggleCheckBox() {
     var cell = SpreadsheetApp.getActiveSheet().getActiveCell();
     if(cell.getValue() != '☑')
        cell.setValue('☑');
     else
        cell.setValue('☐');
     }
    
  5. Save script, close it and return to spreadsheet.

  6. Click on the image you inserted, and choose "Assign script". Enter function name: toggleCheckBox.

  7. You may be prompted for permission when you first click the image.

protected by Community Apr 18 '18 at 12:36

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.