Google Sheets is a popular spreadsheet app with several useful features. One of them is the ability to create drop-down lists inside any cell or range of cells. Once you have added a drop-down list, sharing that Google Sheets document is easy in Google Drive.
One reason to add a list could be to ask multiple-choice questions to the users, allowing them to choose an answer based on their preference. This can also be used to collect feedback, for example. Whatever your use case, creating a drop-down list and editing it later is super easy in both web browser and mobile version of Google Sheets.
Create Drop-Down List in Google Sheets in Web App
We tried the Edge browser on a Windows machine and you can use this any browser on both Windows and Mac. That said, Google recommends Chrome browser for best results.
Step 1: Type sheets.new in the address bar to create and open a new Google Sheets document quickly or open one from your Google Drive account.
Step 2: Select Data validation under the Data menu.
Step 3: In the pop-up that follows, select criteria which can be either ‘List of items’ or ‘List from a range’. The first option means users can choose an item from a predefined list of items. The second option means users can choose a list of items from other cells in the same sheet or even other sheets within the same document. We will go with List of items to keep it simple.
Step 4: Next, enter all the items that you want to appear in the list separated only by a comma in the second box next to Criteria.
Step 5: Enable ‘Show drop-down list in a cell’ to display the drop-down list in the chosen cell with a downward-facing arrow making it easier for users to notice that there is a drop-down list in the marked cell.
Step 6: Select Show warning next to ‘On invalid data’ if you want to allow users to enter invalid data (something that’s not predefined) but will mark the typed item to highlight it. Select Reject input to disallow choosing anything that’s not predefined.
Step 7: Check ‘Show validation help text’ if you want to add a custom note to help users understand what they are supposed to do with the drop-down list. A text box will appear when you select the option to enter a note.
Step 8: Click on the Save button to finish and check the drop-down list live in the chosen cell.
Here is how it looks when you click on the downward-facing arrow.
You can now choose a predefined item from the drop-down list by clicking on the arrow and selecting an item.
Also, you can enter (type it out) something that’s not predefined.
The cell will be immediately flagged with a red symbol and a message that will appear when you move the mouse’s pointer above it.
Create Drop-Down List in Google Sheets in Mobile App
The steps for both Android and iOS versions are the same. We will be using Android in the example below.
Step 1: Open Google Sheets app and open/create a new Sheets document. Select the cell where you want to add a drop-down list. Now tap on the three-dot menu icon on the upper-right corner and select Data validation.
Step 2: Choose a Criteria for the drop-down list. It can be either ‘List of items’ or ‘List from a range’. Then tap on the Add button to input items separated by comma that you want to appear in the drop-down list.
Step 3: Enable ‘Show drop-down menu in cell’ to display an arrow to let the users know that the cell contains a drop-down menu. Enable ‘Show validation help text’ to display a custom message to help users understand what they need to do. When you enable that, an Edit button will appear. Tap on that to add a message.
Step 4: Under the ‘On invalid data’ heading, choose Show warning if you want others to input something that’s not predefined. Select Reject input if you want to force users to select one of the items from the list. Tap on Save button when you are done.
You will notice an arrow at the bottom corner of the screen upon selecting the cell that contains a drop-down menu. Tap on it to choose an item from the list that will slide from the bottom up.
If you wish to enter a different value instead of what’s predefined, tap on the keyboard icon instead and type your answer.
Just like the web app, entering a different answer than predefined will flag the cell to mark it.
Edit or Delete Drop-Down Lists in Google Sheets on Web App
The first few steps will remain the same. Select the cell where you have added the drop-down list in Sheets and go to Data > Data validation menu. You can now edit the criteria, selected cells, and all other options or simply click on Remove validation button to delete the list. Click on the Save button to save changes.
Note that deleting the drop-down list will remove validation but not the user’s input manually.
Edit or Delete Drop-Down Lists in Google Sheets on Mobile App
The steps for editing or deleting the drop-down lists in Google Sheets’ mobile apps also remain the same. Select the cell containing the drop-down list and from the three-dot menu, select Data validation. Tap on Remove rule to delete the list or edit the list as you see fit.
Drop-Down Lists are Fun
Creating and managing drop-down lists in Google Sheets is pretty easy. It takes less than a few minutes to create a list, but a lot would also depend on the number of items. And if you have set the criteria to ‘List from a range’, that may require some more time.
You can also collaborate with other users on the same Google Sheet document by allowing multiple users to edit the same document. This shall save you some valuable time, especially if some drop-down lists are involved or too much data to parse through.
Last updated on 07 February, 2022
The above article may contain affiliate links which help support Guiding Tech. However, it does not affect our editorial integrity. The content remains unbiased and authentic.