How to Create a Google Sheets Drop-Down List

Google Sheets drop-down list
Screenshot from Windows

When creating a spreadsheet with Google Sheets you may come across the need to create one or more drop-down lists within its cells. These easy-to-navigate menus can prompt a user to choose from a list of items and instantly modify output in other cells based on their selection. Perhaps you'd like to simulate a shopping cart experience, displaying different prices for each item in a list. Maybe you're a teacher looking to calculate final grades for each student in your class, populating their names in a handy drop-down list rather than having an unwieldy design where every one takes up their own row.

No matter the need, creating and modifying drop-down lists in Google Sheets is a fairly straightforward process once you get the hang of it. Follow the instructions below and you'll be an expert in no time.

Google Sheets drop-down lists can be created on Android, Chrome OS, Linux, macOS or Windows operating systems. While they can be viewed and utilized on iOS (iPad, iPhone, iPod touch) devices, they cannot be created or manipulated.

Creating a Drop-Down List on a Computer

Google Sheets dropdown on computer

A drop-down list or other data validation method can be created and applied to one or more cells within a Sheets spreadsheet via most major desktop/laptop web browsers, although Google's own Chrome application is recommended for optimal performance.

  1. Open a new or existing spreadsheet.
  2. Select the cell or group of cells where you'd like to place a drop-down list.
  3. Click on Data in the Sheets menu, located towards the top of the screen.
  4. When the drop-down menu appears, select Data validation.
  5. The Data validation interface should now be displayed, containing several configurable options. The first, labeled Cell range, specifies the exact location where your drop-down list will reside.
  6. The Criteria setting contains its own drop-down menu which offers the following options, each one dictating specific data requirements that the spreadsheet's user must follow when entering or selecting an item in or from the aforementioned cell range.
    List from a range: When selected, a drop-down list containing values retrieved from a specific cell range (from the active sheet or another one within the current workbook) will be presented to the user
    List of items: When selected, a drop-down list containing text values entered in the accompanying edit field will be presented to the user; each item entered should be separated by a comma
    Number: This does not present a drop-down list to the user, but rather validates that their entry falls within a specific numeric range
    Text: This also does not present a drop down list -- instead validating that their entry contains or does not contain a particular string of text, is a valid email address or is a properly constructed URL
    Date: While this option doesn't create a drop-down list, it does validate whether a date entered in the chosen cell(s) is valid or falls within a certain range
    Custom formula is: This allows data validation in the selected cell(s) to utilize a user-specified formula
  7. If the value chosen in the Criteria section causes a drop-down list to be created, a check box labeled Show dropdown list in cell will also be provided. Enabled by default, removing the check mark will dictate that the specified data validation will take place in the selected cell(s) but no drop-down list will be shown. This can be useful if you want the spreadsheet's end user to only have the option of entering data manually, as opposed to choosing it from a list.
  8. The next section, labeled On invalid data, controls what happens when the spreadsheet's user enters something into the selected cell(s) that fails the aforementioned data validation. The two available options, each accompanied by a radio button, allow you to show a warning message in this instance or to reject the user's input altogether.
  9. The final setting, Appearance, dictates whether or not text is shown to give the end user a better idea of the type(s) of values that will be accepted within the respective cell range. To activate this virtual helping hand, place a check mark next to the Show validation help text option by clicking on the box once. Next, enter or modify the exact text that you wish to display.
  10. Once satisfied with your selections, click on the Save button.

Creating a Drop-Down List on an Android Smartphone or Tablet

Creating a Drop-Down List on an Android Smartphone or Tablet

A drop-down list or other data validation method can be created and applied to one or more cells within a spreadsheet on your Android phone or tablet via the Sheets app.

  1. Open a new or existing spreadsheet in the app.
  2. Select the cell or group of cells where you'd like to place a drop-down list.
  3. Tap the main menu button, represented by three vertically-aligned dots and located in the upper right-hand corner of your screen. When the drop-down menu appears, select Data validation.
  4. The Data validation interface should now be displayed, containing several configurable options. The first, labeled Cell range, specifies the exact location where your drop-down list will reside.
  5. The Criteria setting contains its own drop-down menu which offers the following options, each one dictating specific data requirements that the spreadsheet's user must follow when entering or selecting an item in or from the aforementioned cell range.
    List of items: When selected, a drop-down list containing text values entered in the accompanying field will be presented to the user; Tap the Add button before entering each list item
    List from a range: When selected, a drop-down list containing values retrieved from a specific cell range (from the active sheet or another one within the current workbook) will be presented to the user; an example range would be Sheet1!A2:D5
    Text contains: Ensures that the text entered by the spreadsheet's user contains a specific string
    Text does not contain: The opposite of the above option, confirms that the text entered does not contain a particular string
    Text exactly: Validates that the text entered by the user matches a specific string
    Is valid email: Validates that the user has entered a correctly formatted email address
    Is valid URL: Ensures that a fully-formed web address is entered
    Date is valid: Confirms that an actual date in the correct format has been provided
    Date is: The entry must match a specific day/month/year
    Date is between: The entry must be a date which falls within a particular range
    Date is not between: The entry must be a date which does not fall within a particular range
    Date is on or before: The entry must match a specific date or be any day prior to it
    Date is on or after: The entry must match a specific date or be any day following it
    Date is after: The entry must be sometime after a specified date
    Greater than: The entry must be a number greater than the one specified
    Greater than or equal to: The entry must be a number greater than or matching the one specified
    Less than: The entry must be a number less than the one specified
    Less than or equal to: The entry must be a number less than or matching the one specified
    Equal to: The entry must be a number that matches the one specified
    Is not equal to: The entry must be a number that does not match the one specified
    Between: The entry must be a number within the specified range
    Is not between: The entry must be a number that does not fall within the specified range
    Custom formula: This allows data validation in the selected cell(s) to utilize a user-specified formula
  6. If the value chosen in the Criteria section causes a drop-down list to be created, an option labeled Show dropdown list in cell will also be provided. Enabled by default, disabling this option by selecting its accompanying slider (turning it from blue to gray) will dictate that the specified data validation will take place in the selected cell(s) but no drop-down list will be shown. This can be useful if you want the spreadsheet's end user to only have the option of entering data manually, as opposed to choosing it from a list.
  7. The Appearance setting determines whether or not text is shown to give the end user a better idea of the type(s) of values that will be accepted within the respective cell range. To activate this virtual helping hand, tap the slider next to the Show validation help text option so that its color turns from gray to blue. Next, select EDIT and enter or modify the exact text that you wish to display.
  8. The bottom section, labeled On invalid data, controls what happens when the spreadsheet's user enters something into the selected cell(s) that fails the aforementioned data validation checks. The two available options, each accompanied by a radio button, allow you to show a warning message in this instance or to reject the user's input altogether.
  9. Once satisfied with your selections, tap the SAVE button.

Modifying or Removing a Drop-Down List

Google Sheets remove drop-down list
Getty Images (Chunumunu #530919180)

Take the following steps to modify or remove a drop-down list or other data validation criteria from a particular cell range.

Desktop/laptop: Web browser

  1. Select the cell(s) containing the drop-down list or other data validation criteria that you wish to modify or remove.
  2. Click on Data in the Sheets menu, located towards the top of the screen.
  3. When the drop-down menu appears, select Data validation.
  4. The Data validation interface should now be displayed, containing several configurable options. If you'd like to modify cell behavior, make the desired changes and click on the Save button to apply them instantly. If you'd like to remove the drop-down list or other validation criteria completely, click on the Remove validation button.

Android: Google Sheets app

  1. Select the cell(s) containing the drop-down list or other data validation criteria that you wish to modify or remove.
  2. Tap the main menu button, represented by three vertically-aligned dots and located in the upper right-hand corner of your screen. When the drop-down menu appears, select Data validation.
  3. The Data validation interface should now be displayed, containing several configurable options. If you'd like to modify cell behavior, make the desired changes and tap the SAVE button to apply them instantly. If you'd like to remove the drop-down list or other validation criteria completely, tap the REMOVE RULE button.