by Natalia Sharashova, updated on
This blog post will present a few examples of how to create checkboxes and insert tick symbols or cross marks in your Google Sheets. Whatever your history is with Google Sheets, today you may discover some new methods of doing that.
Lists help us put things in order. Stuff to buy, tasks to solve, places to visit, movies to watch, books to read, people to invite, video games to play – everything around us is practically filled with those lists. And if you use Google Sheets, chances are that it would be best to track your endeavours there.
Let's see what instruments spreadsheets offer for the task.
The quickest way to insert a Google spreadsheet tick box is using the corresponding option from the Sheets menu directly:
Tip. Alternatively, you can fill only one cell with a checkbox, then select that cell, hover your mouse over its bottom right corner until a plus icon appears, click, hold and drag it down the column to copy over:
Click once more, and the box will turn blank again.
Tip. You can tick off multiple checkboxes at once by selecting them all and hitting Space on your keyboard.
Tip. It's also possible to re-color your checkboxes. Select cells where they reside, click on the Text color tool on the standard Google Sheets toolbar:
And pick the necessary hue:
Another swift method lets you not only insert checkboxes and tick symbols but also make sure nothing else is entered to those cells. You should use Data validation for that:
Tip. To make Google Sheets remind you not to enter anything but checkmarks to the range, pick the option called Show warning for the On invalid input line. Or you can decide to Reject input whatsoever:
In case you've decided to get a warning once anything else is entered, you'll see orange triangle at the upper right corner of such cells. Hover your mouse over these cells to see the warning:
There's a way to add such a checkbox in Google Sheets that will control, tick off & uncheck all other checkboxes.
Tip. If that's what you're looking for, be ready to use both ways from the above (the standard Google Sheets tick box & Data validation) along with the IF function.
Special thanks to God of Biscuits from Ben Collins blog for this method.
A blank checkbox will appear & will control all future checkboxes:
Tip. Most likely the checkbox will copy itself to a new row as well. In this case, just select it and remove by pressing Delete or Backspace on your keyboard.
The formula should go right above your future checkboxes: B2 for me. I enter the following formula there:
=IF(B1=TRUE,{"";TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE},"")
So basically it's a simple IF formula. But why does it look so complicated?
Let's break it down to pieces:
{"";TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}
This array keeps a cell with a formula blank and adds multiple TRUE records in a column right below it. You will see them as soon you add a tick mark to that checkbox in B1:
These TRUE values are your future checkboxes.
Note. The more checkboxes you need, the more times TRUE should appear in the formula.
Tip. If you don't want to see that empty helper row with a formula, you are free to hide it.
Select the range with all TRUE records and go to Data > Data validation:
Pick Checkbox for Criteria, then select the box Use custom cell values and enter TRUE for Checked:
Once you're ready, click Save.
You'll immediately see a group of checkboxes with tick marks next to your items:
If you click on the very first tick box a few times, you'll see that it controls, checks & unchecks multiple checkboxes in this Google Sheets list:
Looks good, right?
Sadly, there's one flaw to this method. If you tick off several checkboxes in the list first and then hit that main checkbox to select them all – it just won't work. This sequence will only break your formula in B2:
While it may seem quite a nasty drawback, I believe this method of checking/unchecking multiple checkboxes in Google spreadsheets will still be useful in certain cases.
The CHAR function is the first instance that will provide you with a cross mark as well as with a Google Sheets checkmark:
The only thing it needs is the number of the symbol from the Unicode table. Here are a few examples:
=CHAR(9744)
will return an empty checkbox (a ballot box)
=CHAR(9745)
will fill cells with a tick symbol within a checkbox (ballot box with check)
=CHAR(9746)
will give back a cross mark in the checkbox (ballot box with X)
Tip. Symbols returned by the function can also be recolored:
There are different outlines of checks and crosses within ballot boxes available in spreadsheets:
Note. Cross and tick marks cannot be removed from boxes made by the CHAR formula. To get an empty checkbox, change the number of the symbol within a formula to 9744.
If you don't need those boxes and you wish to gain pure tick symbols and cross marks, the CHAR function will also help.
Below are a few codes from the Unicode table that will insert pure checkmark and cross mark in Google Sheets:
Tip. Cross mark in Google Sheets can also be represented by a multiplication X and crossing lines:
And also by various saltires:
Another not so common alternative would be to add images of Google Sheets checkmarks and cross symbols:
Once your picture is chosen, click Select.
This method lets you bring your own check and cross marks to life. The option may seem far from ideal, but it is fun. :) It can really mix up your routine work in spreadsheets with a bit of creativity:
One tool lets you draw lines, arrows, and curves. Another supplies you with different ready-made shapes. There's also a text tool and one more image tool.
Or, instead, choose the line tool, make a shape from a few lines, and edit each line individually: change their color, adjust length and width, turn them into dashed lines, and decide on their start and end points:
Tip. To adjust it, select the newly-created shape, hover your mouse over its bottom right corner till a double-headed arrow appears, press and hold the Shift key, then click and drag to resize the drawing to the size you need:
As you may know, Google Sheets supports keyboard shortcuts. And it so happened that one of them is designed to insert a checkmark in your Google Sheets. But first, you need to enable those shortcuts:
You will see a window with various key binds.
An empty box will appear in the cell, waiting for you to click on it to fill with a tick symbol:
Tip. You can copy the box to other cells the same way I mentioned a bit earlier.
If you have time to spare, you can make use of Google Docs:
The character will be immediately inserted to wherever your cursor is.
As you can see, there are different ways to make the checkmark and the cross mark in Google Sheets. Which one do you prefer? Have you had problems inserting any other characters to your spreadsheets? Let me know in the comments section below! ;)
Table of contents