Oct
3

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 might 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 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 misspelled 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.

Please pay attention, 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 misspelling.

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

How to insert a checkbox to a Google spreadsheet

Add a checkbox to your table

The most basic and simple list has two answer options - yes and no. And for that checkboxes are available in Google Sheets.

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 not really convenient 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.

Copy the symbols below into the neighboring cells of column A of your second spreadsheet:
☐, ☑

There's another way of add-in 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

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 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 - H2:H20. Then go to Data > Data validation:
Add data validation to a spreadsheet

From here, we have two ways to continue. Allow me to introduce the easiest one first.

Choose the List of items option next to Criteria. You'll see an empty field where you paste the symbols copied earlier: filled and empty checkboxes divided by comma.

Note.Don't forget to tick off the option to Show dropdown list in cell. Otherwise, you won't know which cells feature checkboxes.

Insert checkboxes to your drop-down list

When you're ready, click on the Save button.

As a result, each cell within the range will get a drop-down list and will be marked with a button with a little triangle.
Select a checkbox from the list

It's done! Click the triangle and select one of the checkboxes: empty or filled one, depending on the status of your order.

Add a custom 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 give you the drop-down list of names:
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 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 the cells with drop-down lists and paste them whenever 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 cell with a drop-down list.

Remove multiple checkboxes from a certain range in Google Sheets

To delete checkboxes and drop-down lists from any range of your spreadsheet, follow the simple steps below:

  1. Select the cells where you want to delete checkboxes (all of them at once or select some cells when pressing Ctrl on your keyboard).
  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

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

Remove multiple checkboxes from entire Google spreadsheet

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.

Add values to a drop-down list automatically

So, here's our drop-down list that have 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, and an orange notification triangle will appear at the corner of the cell saying that that only the value specified at the beginning can be used.

That is why I'd recommend you create automatically filled drop-down lists. 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 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 Google Sheets 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 drop-downs and checkboxes to your table!

Good luck!

You may also be interested in:

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite for Excel Professionals
 
 
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard