Drop-down lists will be very helpful to avoid data errors when you share a Google Spreadsheet with others and they can also edit the document. It can also be used whenever you want someone to select options from a pre-defined list. In this post, let’s see how to create and use a Drop-Down list in Google Sheets.
How to Insert a Drop-Down List in Google Sheets
Google Sheets has multiple options to create drop-down lists. For example, you can easily convert a set of data into a drop-down list or you can create a new one by manually entering them as well. Let’s see how to do both one by one.
Drop-Down List Using List From a Range
Let’s say that you want to convert data in certain cells in a column to a drop-down list and share the Sheet with others so that they can easily select from a list.
Open the Google Sheet on your computer and select the cell on which you need to insert a drop-down list.
Then, click the Data menu from the top of the sheet. From the list of options shown, click Data Validation.
In the Data validation window, you will see a lot of configuration options. Let’s see that one by one.
Cell range: This indicates a single cell or range of cells where you want to insert the drop-down list. In this example, we have selected a single-cell C1.
Criteria: If you click this drop-down box, you will see multiple options for data validation. Since we want to create a drop-down list from existing data, we need to select the option List from a range.
Then, you have to select the cells that contain data for creating a drop-down list. While you start selecting cells with your mouse, you will see a small pop-up menu. Once you are done with the selection click OK to confirm.
If you are not comfortable selecting cells with your mouse, you can manually enter the cell addresses (eg. A2:A8) in the box located next to List from a range.
Show drop-down list in cell: Make sure that this checkbox is selected. Otherwise, the drop-down list will not be visible.
On invalid data: This field will decide what will happen if the user tries to enter incorrect data instead of selecting from the list.
Show warning – If this option is selected, the user can enter incorrect data and only a warning message will be shown.
Reject input – The user cannot enter any data other than the values in the drop-down list. A warning message box pops up and prompts the user to enter the correct data.
Appearance: If you wish to create your own message with instructions on the data entry, then you can select the check box Show validation help text: and enter your message on the box provided.
Once you are done configuring the above options, click Save.
Now, if you see the cell you had selected earlier (eg. C1), you will see a down arrow. If you click that, you can see the drop-down list.
Drop-Down List Using List of Items
Instead of converting existing data to a drop-down list, you can create a list by manually typing them. Let’s see how to do that.
After selecting the cell, go to the Data Validation window. (As mentioned in the previous section.)
In the drop-down box for Criteria, select List of Items.
Then, enter the values for the drop-down list separated by commas in the provided box. The values can be text or numbers.
Finally, click Save.
That’s it. The drop-down list has been created successfully.
How to Delete Drop-Down List in Google Sheets
Do you wish to remove a drop-down list from the Google Sheet? At any point in time, you can easily get rid of the drop-down list with a single click.
Open the Google Sheet and select all the cells for which you want to delete the drop-down list.
Then, click the Data menu and select Data Validation from the sub-menu shown.
In the Data Validation window, click Remove validation.
Now, the drop-down list will be removed from the selected cells.
How to Edit Drop-Down List in Google Sheets
It doesn’t matter whether you have created the drop-down list using a list from a range or a list of items. You can edit both lists.
For a drop-down list created using the list from a range, you need to edit the original data which you had selected for converting to a list. Then, the changes you make will get reflected automatically in the drop-down list.
If you had created the list manually using a list of items, then you need to revisit the Data Validation screen to make the changes.
Copying Drop-Down lists in Google Sheets
By dragging the cell that contains the drop-down list, you can easily insert the drop-down list to other cells in the same row or column.
If dragging cells is not convenient for you, then right-click the cell that has the list and select Copy. Or you can use the CTRL+C keys on your keyboard.
Then, right-click on the cell where you need to paste the data and select Paste special. From the sub-menu, click Data validation only.
Now, you can see the drop-down list in the cell you had selected.
Well, drop-down lists come in handy especially if you want to share sheets with others and expect them to choose only specific values.