The Checkbox (Tick box) Has Arrived on Sheets

After a wait of several days since being announced in the latest update the checkbox has made it to Google Sheets.  NB in the UK a checkbox is called a tick box, so in the screenshots below you will see tick box instead.

So far the implementation seems to be a solid new feature.  To insert checkboxes simply select the cell(s) where you want a checkbox, then insert them from the Insert menu.

Screen Shot 2018-04-17 at 14.43.33

When a checkbox is unticked the cell’s value becomes FALSE.  This is the default value after adding checkboxes to cells.  When the cell is checked it becomes TRUE.

Screen Shot 2018-04-17 at 14.44.26

But checkboxes are not restricted to returning TRUE/FALSE, they can return numbers or even custom strings.  In order to do that you do so via the Data Validation menu.  Make sure the cells you want these custom checkboxes inserted into are empty; i.e. do not insert checkboxes via the Insert menu first.  From the Data menu select Data Validation.  Then a pop over window appears.  In the Criteria field choose Checkbox.

This slideshow requires JavaScript.

To customise the values for checked and unchecked tick the option for “Use custom cell values”.  Then below two new text fields will appear.  For the values you could use 0 and 1 as the unchecked and checked values respectively (or the reverse if you desire).  Or they could be “Yes” and “No” or even “foo” and “bar”, etc.

This slideshow requires JavaScript.

If you insert checkboxes by this method then here is something to watch out for.  When the checkboxes are inserted they are inserted as unchecked just as they are for the method of insertion using the Insert menu.  However, unlike that method the cell values are not updated to what you set them in the data validation window.  That is, when you insert standard TRUE/FALSE checkboxes with the Insert menu all the cells get the value of FALSE.  But when you insert checkboxes with Data Validation the cell values remain blank until they are checked.  For example, if I used “No” as the value for unchecked then after I Save the data validation the cell values will not be changed to “No”.  In order to set the cell values to “No” they need to be manually checked and unchecked (Google may change this behaviour in the future).

Select a range of cells with checkboxes and toggle them with the space bar

Speaking of which, manually checking and unchecking large numbers of checkboxes, you can of course select a range of cells with checkboxes and toggle them all at once with the space bar.  So should you be using custom checkboxes via data validation you can fairly quickly set them to the custom unchecked value you set.

On mobile (iOS at least) you cannot add new checkboxes but you can interact with any you insert on desktop.  You can copy and paste existing checkboxes, so if you have a spreadsheet with checkboxes already set up and want to extend the range you can do it on mobile – this is true even of checkboxes with custom values.

In my opinion checkboxes on Sheets will prove to be a valuable feature, what are your thoughts – comment below or on Google+ or Twitter?

tick boxes on Sheets iOS app
You can toggle and copy and paste existing checkboxes on mobile apps

 

Advertisements