How to add, edit and delete checkboxes and drop-down lists in Google Sheets

When you work with a Google spreadsheet, sooner or later you may need to use some functionality that you've never used before. Checkboxes and drop-downs can be among such features. Let's see how useful they can be in Google Sheets.

Very often we need to insert repeated values to one column of our table. For example, the employees' names who work on some orders or with various clients. Or the order statuses — sent, paid, delivered, etc. In other words, we have a list of variants and we want to select only one of them to input to a cell.

What problems may occur? Well, the most common one is the misspelling. You can type in another letter or miss the verb ending by mistake. Have you ever wondered how these tiny typos threaten your work? When it comes to counting the number of orders each employee has processed, you will see that there are more names than people you have. You will need to search for the misspelt names, correct them and count again.

What is more, it's a waste of time to enter one and the same value all over again.

That is why Google tables have an option to create lists with values: the values from which you will choose only one when filling the cell.

Have you noticed my word choice? You won't enter the value — you will choose only one from the list.

It saves time, speeds up the process of creating the table and eliminates typos.

I hope by now you do understand the advantages of such lists and ready to try and create one.

How to insert checkboxes in Google Sheets

Add a checkbox to your table

The most basic and simple list has two answer options — yes and no. And for that Google Sheets offers checkboxes.

Suppose we have a spreadsheet #1 with the chocolate orders from various regions. You can see the part of the data below:
Chocolate orders data

We need to see which order was accepted by which manager and whether the order is executed. For that, we create a spreadsheet #2 to place our reference information there.

Tip. Since your main spreadsheet can contain loads of data with hundreds of rows and columns, it can be somewhat inconvenient to add some excess information that can confuse you in the future. Thus, we advise you to create another worksheet and place your additional data there.

Select column A in your other spreadsheet and go to Insert > Checkbox in the Google Sheets menu. An empty checkbox will be added to each selected cell right away.

Tip. You can insert the checkbox in Google Sheets to one cell only, then select this cell and double-click on that little blue square to fill the entire column till the end of the table with checkboxes:
A little square that automatically copies the cell till the end of the column.

There's another way of adding checkboxes. Place the cursor into A2 and enter the following formula:

=CHAR(9744)

Press Enter, and you will get an empty checkbox.

Go down to A3 cell and enter a similar formula:

=CHAR(9745)

Press Enter, and get a filled checkbox.
Filled checkbox formula in Google Sheet

Tip. See what other types of checkboxes you can add in Google Sheets in this blog post.

Let's put our employees' surnames in the column to the right to use them later:
Two columns of additional data

Now we need to add the info regarding the order managers and the order statuses into columns H and I of the first spreadsheet.

To begin with, we add column headers. Then, since the names are stored in the list, we use Google Sheets checkboxes and a drop-down list to enter them.

Let's begin with filling in the order status information. Select the range of cells to insert checkbox in Google Sheets — H2:H20. Then go to Data > Data validation:
Add data validation to a spreadsheet

Choose the Checkbox option next to Criteria.
Insert checkboxes to your drop-down list

Tip. You can tick off the option to Use custom cell values and set the text behind each type of checkbox: checked and unchecked.

When you're ready, hit Save.

As a result, each cell within the range will be marked with a checkbox. Now you can manage these based on the status of your order.
Select a checkbox from the list

Add a custom Google Sheets drop-down list to your table

The other way to add a drop-down list to a cell is more common and offers you more options.

Select I2:I20 range to insert manager's names who process orders. Go to Data > Data validation. Make sure that Criteria option shows List from a range and select the range with the needed names:
Add a custom drop-down list

Tip. You can either enter the range manually, or click the table symbol and select the range with names from the spreadsheet 2. Then click OK:

Select the range

To finish, click Save and you'll get the range of cells with triangles that open drop-down menu of names in Google SheetsfAll the selected drop-downs are deleted comp:
Choose the name from the drop-down list

The same way we can create the list of checkboxes. Just repeat the steps above but select A2:A3 as a criteria range.

How to copy checkboxes to another range of cells

So, we started to quickly fill our table in Google Sheets with checkboxes and drop-down lists. But in time more orders have been placed so that we require additional rows in the table. What is more, there are only two managers left to process these orders.

What should we do with our table? Go over the same steps all over again? No, things aren't as hard as they look.

You can copy individual cells with checkboxes and with drop-down lists and paste them wherever you need to using Ctrl+C and Ctrl+V combinations on your keyboard.

In addition, Google makes it possible to copy and paste groups of cells:
Copy the drop-downs to other cells

Another option would be to drag and drop the bottom right corner of the selected cell with your checkbox or drop-down list.

Remove multiple Google Sheets checkboxes from a certain range

When it comes to checkboxes that reside in cells as is (that are not part of the drop-down lists), simply select these cells and press Delete on your keyboard. All checkboxes will be cleared away immediately, leaving empty cells behind.

However, if you try and do so with the drop-down lists (aka Data validation), this will only clear the selected values. The lists themselves will remain in cells.

To remove everything from cells, including drop-downs, from any range of your spreadsheet, follow the simple steps below:

  1. Select the cells where you want to delete checkboxes and drop-downs (all of them at once or select particular cells while pressing Ctrl).
  2. Go to Data > Data validation in the Google Sheets menu.
  3. Click the Remove validation button in the appeared Data validation pop-up window:
    How to remove validation
    This will get rid of all drop-downs first.
  4. Then press Delete to clear away the remaining checkboxes from the same selection.

And it's done! All selected Google Sheets drop-downs are deleted completely, while the rest of the cells remain safe and sound.

Remove multiple checkboxes and drop-down lists in Google Sheets from entire table

What if you need to delete all the checkboxes over the whole table you work with?

The procedure is the same, though you need to select every single cell with a checkbox. Ctrl+A key combination may come handy.

Select any cell of your table, press Ctrl+A on your keyboard and all the data you have will be selected. The next steps are no more different: Data > Data validation > Remove validation:
Remove data validation from entire spreadsheet

Note. The data in column H will remain since it was inserted using the drop-down lists. In other words, it is drop-down lists that are deleted rather than the inserted values (if any) in cells.

To delete checkboxes themselves as well, you will need to press Delete on the keyboard.

Add values to a drop-down list automatically

So, here's our Google Sheets drop-down that has been helpful for a while. But there've been some changes and we have a couple more employees in our midst now. Not to mention that we need to add one more parcel status, so we could see when it's "ready to dispatch". Does it mean we should create the lists from scratch?

Well, you could try and enter the new employees' names disregarding the drop-down. But since there's the Warning option ticked off for any invalid data in the settings of our list, the new name won't be saved. Instead, an orange notification triangle will appear at the corner of the cell saying that only the value specified at the beginning can be used.

That is why I'd recommend you create drop-down lists in Google Sheets that can be filled automatically. The value will be added to a list automatically right after you input it to a cell.

Let's see how we can change the content of the drop-down list without turning to any additional scripts.

We go to the spreadsheet 2 with the values for our drop-down list. Copy and paste the names into another column:
Copy the values to additional column

Now we change the drop-down list settings for the I2:I20 range: select these cells, go to Data > Data validation, and change the range for Criteria to column D spreadsheet 2. Don't forget to save the changes:
Change the reference range for Data validation

Now see how easy it is to add a name into the list:
Add values to a drop-down list automatically

All the values from column D sheet 2 automatically became a part of the list. It's very convenient, isn't it?

To sum it all up, now you know that even spreadsheet newbies can create drop-down lists even if they have never heard of the feature like this before. Just follow the steps above and you'll bring those Google Sheets drop-downs and checkboxes to your table!

Good luck!

6 responses to "How to add, edit and delete checkboxes and drop-down lists in Google Sheets"

  1. Susan says:

    I would love to find a way to select multiple checkboxes within a dropdown menu in Google Sheets. For instance, I upload designs to be printed on various products. Right now I need a separate column to check off for each product with a yes/no option to fill that field. I would LOVE to be able to do multiple selections within the field.
    Like this, where it is a dropdown, and some items are selected:
    (X) T-shirt - short sleeve
    (X) T-shirt - premium
    (X) T-shirt - longsleeve
    ( ) Sweatshirt
    ( ) Hoodie
    (X) Mug - 11 oz
    ( ) Mug - 15 oz
    (X) Tote - canvas flat
    ( ) Tote - gusseted
    ( ) Baseball Cap

    Checkboxes are good, radio buttons would be fine - ANYthing where there is the ability to select multiple items.
    Is this possible with Google Sheets?
    Thanks In advance.

  2. KM says:

    Hi--

    I accidentally misspelled one of the drop-down options. Is there a way to globally edit that so that anything already entered is corrected, along with future entries for that option?

    Thanks,
    KM

  3. Jack says:

    In section "Remove multiple checkboxes in Google Sheets from entire table", it only talks about removing Data Validation, not checkboxes. Same in the previous section.

    After trying what you outlined, I see that it does remove the checkboxes. The graphic only shows the data validation going away, not the checkboxes. Very confusing.

Post a comment



Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)