In web forms, surveys, and polls, it can be very useful to limit the choices for a selection with a simple drop-down list. This is also possible in an Excel spreadsheet, but the process isn't very.
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.
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 misspelling. You can type in another letter or miss the verb ending by mistake.
![How To Edit A Drop Down List In Excel For Mac How To Edit A Drop Down List In Excel For Mac](https://s10629.pcdn.co/wp-content/pictures/2009/06/list-source.png)
![Drop Drop](/uploads/1/2/5/3/125353724/788177110.png)
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: 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. 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 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: 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. 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. 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. 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 give you the drop-down list of names: 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: 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:. Select the cells where you want to delete checkboxes (all of them at once or select some cells when pressing Ctrl on your keyboard). Go to Data Data validation in the Google Sheets menu.
Click the Remove validation button in the appeared Data validation pop-up window: And it's done! All the selected drop-downs are deleted completely, while the rest of the cells remain safe and sound. 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. 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: 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 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!
You may also be interested in:. 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.