Create a Google Sheets Drop-Down List

Make your own drop-down menu

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 the output in other cells based on their selection. 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.

Create a Drop-Down List on a 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. However, Google's 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.

    Selected cell in Google Sheets
  3. Select Data in the Sheets menu, located towards the top of the screen.

    The Data heading in Google Sheets
  4. When the drop-down menu appears, select Data Validation.

    The Data Validation command
  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.

    The Cell Range section
  6. The Criteria setting contains a drop-down menu that offers the following options, each dictating specific data requirements that the spreadsheet's user must follow when entering or selecting an item in or from the 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 appear.
    • List of items: When selected, a drop-down list containing text values entered in the accompanying edit field will appear 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 instead validates that their entry falls within a specific numeric range.
    • Text: This also does not present a drop-down list. Instead, it validates that the 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 specific range.
    • Custom formula is: This allows data validation in the selected cell(s) to utilize a user-specified formula.
    The Criteria heading and options
  7. If the value chosen in the Criteria section creates a drop-down list, a checkbox labeled Show drop-down list in cell will also appear. Enabled by default, removing the checkmark will dictate that the specified data validation will take place in the selected cell(s), but no drop-down list will appear. This option can be useful if you want the spreadsheet's end-user only to have the option of entering data manually, as opposed to choosing it from a list.

    The "Show dropdown list in cell" option
  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 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.

    The "On Invalid Data" section
  9. The final setting, Appearance, dictates whether or not text appears to give the end-user a better idea of the type(s) of values accepted within the respective cell range. To activate this virtual helping hand, place a checkmark 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.

    Appearance options
  10. Once satisfied with your selections, select the Save button.

    The Save button

Modify or Remove a Drop-Down List in a Web Browser

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

  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.

    The Data heading in Google Sheets
  3. When the drop-down menu appears, select Data Validation.

    The Data Validation command
  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 altogether, click on the Remove validation button.

    The Save button

Create a Drop-Down List on an Android Device

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 a drop-down menu that offers the following options, each dictating specific data requirements that the spreadsheet's user must follow when entering or selecting an item in or from the cell range.

    • List of items: When selected, a drop-down list containing text values entered in the accompanying field will appear 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 appear to the user; an example range would be Sheet1!A2:D5
    • Text contains: Ensures that the text entered by the spreadsheet's user includes 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 the user enters a fully-formed web address.
    • Date is valid: Confirms that the user enters an actual date in the correct format.
    • Date is: The entry must match a specific day/month/year.
    • Date is between: The entry must be a date that falls within a particular range.
    • Date is not between: The entry must be a date that does not fall within a particular range.
    • Date is on or before: The entry must match a specific date or be any day before 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 creates a drop-down list, an option labeled Show drop-down 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 appear. This option can be useful if you want the spreadsheet's end-user only to 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 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.

Modify or Remove a Drop-Down List on an Android Device

  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 altogether, tap the REMOVE RULE button.