Google Sheets is a popular spreadsheet app with several useful features. One of them is the ability to create drop down lists in any cell or range of cells. Once you’ve added a drop-down list, sharing that Google Sheets document is easy in Google Drive.
One of the reasons for adding a list could be to ask multiple choice questions to users, allowing them to choose an answer based on their preferences. This can also be used to collect feedback, for example. Whatever your use case, creating a dropdown list and changing it later is very easy both in the web browser and in the mobile version of Google Sheets.
Let’s get started.
Create a drop-down list in Google Sheets in the web app
We tried the Edge browser on a Windows machine and you can use any browser on both Windows and Mac. That said, Google recommends the Chrome browser for the best results.
Step 1: Type leaves.new in the address bar to quickly create and open a new Google Sheets document or open one from your Google Drive account.
2nd step: Select Data Validation from the Data menu.
Step 3: In the pop-up window that follows, select criteria which can be either “Item List” or “List from Range”. The first option means that users can choose an item from a predefined list of items. The second option means that users can choose a list of items from other cells on the same sheet or even other sheets in the same document. we will go with List of objects to keep it simple.
Step 4: Then enter all the items 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 cell” to display the drop-down list in the chosen cell with a down 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 is not predefined) but mark the entered item to highlight it. Select Reject entry to prohibit choosing anything that is 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 appears when you select the option to enter a note.
Step 8: Click the Save button to finish and check the drop-down list live in the chosen cell.
Here’s what it looks like when you click the down arrow.
You can now choose a predefined item from the drop-down list by clicking on the arrow and selecting an item.
In addition, you can enter (type it in) anything that is not predefined.
The cell will be immediately marked with a red symbol and a message that will appear when you move the mouse pointer over it.
Create a drop-down list in Google Sheets in the mobile app
The steps for Android and iOS versions are the same. We will be using Android in the example below.
Step 1: Open the Google Sheets app and open / create a new Sheets document. Select the cell where you want to add a drop-down list. Now tap the three-dot menu icon in the upper right corner and select Data Validation.
2nd step: Choose a criterion for the drop-down list. It can be “List of items” or “List of a range”. Then press the Add button to enter the items separated by commas that you want to appear in the drop-down list.
Step 3: Enable “Show drop-down menu in cell” to display an arrow indicating to users that the cell contains a drop-down menu. Enable “Show validation help text” to display a personalized message to help users understand what to do. When you enable this, an Edit button appears. Tap on it to add a message.
Step 4: Under the “On invalid data” heading, choose Show warning if you want other people to type something that is not predefined. Select Reject Entry if you want to force users to select one of the items in the list. Press the Save button when finished.
You will notice an arrow in the lower corner of the screen when selecting the cell containing a drop-down menu. Tap on it to choose an item from the list which will slide from bottom to top.
If you want to enter a different value instead of what’s preset, tap the keyboard icon instead and type your answer.
Just like the web app, entering a different answer than the predefined one will mark the cell for tagging.
Edit or delete drop-down lists in Google Sheets on a web app
The first steps will remain the same. Select the cell where you added the drop-down list in Sheets and go to the Data menu> Data Validation. You can now edit the criteria, selected cells, and all other options or just click the Remove Validation button to remove the list. Click the Save button to save the changes.
Note that removing the drop-down list will remove validation but not manual user input.
Edit or delete drop-down lists in Google Sheets on a mobile app
The steps for changing or removing 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 Delete Rule to delete the list or edit the list as you see fit.
Also on Guiding Tech
Dropdowns are fun
Creating and managing drop-down lists in Google Sheets is pretty straightforward. It takes less than a few minutes to create a list, but a lot will also depend on the number of items. And if you have set the criteria to “List from range”, it may take a little longer.
You can also collaborate with other users on the same Google Sheet document by allowing multiple users to edit the same document. This will save you valuable time, especially if some dropdowns are involved or too much data to analyze.
Last updated on July 20, 2021
The above article may contain affiliate links that help support Guiding Tech. However, this does not affect our editorial integrity. The content remains impartial and authentic.