How do i create a drop down list with multiple checkboxes 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.

What is a drop-down list in Google Sheets and why you may need one

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:

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:

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.

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:

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:

Choose the Checkbox option next to Criteria.

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.

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:

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:

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:

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

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:

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:
   
   
   
   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:

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:

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:

Now see how easy it is to add a name into the list:

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!

You may also be interested in