Create A Google Sheets Drop Down List

When creating a spreadsheet with Google Sheets, you may need to create one or more drop-down lists within its cells. These easy-to-navigate menus prompt a user to choose from a list of items and modify the output in other cells based on their selection. Here’s how to create and modify a drop-down menu in Google Sheets.

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 in a Sheets spreadsheet on most major desktop and laptop web browsers. However, Google Chrome is recommended for optimal performance.

• Open a new or existing spreadsheet.
• Select the cell or group of cells where you want to place a drop-down list.
• Go to the Data tab.
• Select Data Validation.
• The Data validation dialog box displays, containing several configurable options. The first, labeled Cell range, specifies the location where the drop-down list will reside.
• The Criteria setting contains a drop-down menu that offers the following options. Each option dictates 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 sheet in the current workbook) appears.
• List of items: When selected, a drop-down list containing text values entered in the accompanying edit field appears. Each item entered should be separated by a comma.
• Number: This does not present a drop-down list to the user. Instead, it validates that their entry falls within a specific numeric range.
• Text: This 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: This option doesn’t create a drop-down list. It validates whether a date entered in the chosen cells is valid or falls within a specific range.
• Custom formula is: This allows data validation in the selected cells to use a user-specified formula.
• If the value chosen in the Criteria section creates a drop-down list, the Show drop-down list in cell check box also appears. Enabled by default, removing the check mark dictates that the specified data validation will occur in the selected cells, 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 On invalid data section controls what happens when the spreadsheet’s user enters something into the selected cells that fails the data validation. The two available options allow you to show a warning message in this instance or reject the user’s input.
• The Appearance setting dictates whether text appears to give the end-user a better idea of the types of values accepted within the respective cell range. To activate this virtual helping hand, select the Show validation help text check box. Next, enter or modify the text that you wish to display.
• When you’re satisfied with your selections, select Save.

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.

Open a new or existing spreadsheet.

Select the cell or group of cells where you want to place a drop-down list.

Go to the Data tab.

Select Data Validation.

The Data validation dialog box displays, containing several configurable options. The first, labeled Cell range, specifies the location where the drop-down list will reside.

The Criteria setting contains a drop-down menu that offers the following options. Each option dictates 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 sheet in the current workbook) appears.
• List of items: When selected, a drop-down list containing text values entered in the accompanying edit field appears. Each item entered should be separated by a comma.
• Number: This does not present a drop-down list to the user. Instead, it validates that their entry falls within a specific numeric range.
• Text: This 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: This option doesn’t create a drop-down list. It validates whether a date entered in the chosen cells is valid or falls within a specific range.
• Custom formula is: This allows data validation in the selected cells to use a user-specified formula.

If the value chosen in the Criteria section creates a drop-down list, the Show drop-down list in cell check box also appears. Enabled by default, removing the check mark dictates that the specified data validation will occur in the selected cells, 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 On invalid data section controls what happens when the spreadsheet’s user enters something into the selected cells that fails the data validation. The two available options allow you to show a warning message in this instance or reject the user’s input.

The Appearance setting dictates whether text appears to give the end-user a better idea of the types of values accepted within the respective cell range. To activate this virtual helping hand, select the Show validation help text check box. Next, enter or modify the text that you wish to display.

When you’re satisfied with your selections, select Save.

• Select the cells containing the drop-down list or other data validation criteria that you wish to modify or remove.
• Go to the Data tab.
• Select Data Validation.
• The Data validation dialog box displays, containing several configurable options. To modify cell behavior, make the desired changes, then select Save to apply the changes instantly. To remove the drop-down list or other validation criteria, select Remove validation.

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 using the Sheets app.

Select the cells containing the drop-down list or other data validation criteria that you wish to modify or remove.

The Data validation dialog box displays, containing several configurable options. To modify cell behavior, make the desired changes, then select Save to apply the changes instantly. To remove the drop-down list or other validation criteria, select Remove validation.

• Open a new or existing spreadsheet in the app.
• Select the cell or group of cells where you want to place a drop-down list.
• Tap the main menu button, represented by three vertically-aligned dots, and located in the upper-right corner of the screen. When the drop-down menu appears, select Data Validation.
• The Data validation interface displays and contains several configurable options. The first, labeled Cell range, specifies the exact location where the drop-down list will reside.
• The Criteria setting contains a drop-down menu that offers the following options. Each option dictates 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 appears to the user. Tap Add 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 sheet in the current workbook) appears to the user. An example range is 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. It 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 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 entered 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 cells to utilize a user-specified formula.
• If the value chosen in the Criteria section creates a drop-down list, an option labeled Show drop-down list in cell is also provided. This option is enabled by default. Disabling this option by selecting its accompanying slider (turning it from blue to gray) dictates that the specified data validation will take place in the selected cells, 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 Appearance setting determines whether text is shown to give the end-user a better idea of the types of values that are accepted in the respective cell range. To activate this virtual helping hand, turn on the Show validation help text toggle switch. Next, select Edit and enter or modify the text that you wish to display.
• The On invalid data section controls what happens when the spreadsheet’s user enters something into the selected cells that fails the data validation checks. The two available options allow you to show a warning message in this instance or to reject the user’s input.
• When you’re satisfied with your selections, tap Save.

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

• Select the cells that contain the drop-down list or other data validation criteria that you wish to modify or remove.
• Tap the main menu button, represented by three vertically-aligned dots, and located in the upper-right corner of the screen. When the drop-down menu appears, select Data Validation.
• The Data validation interface displays and contains several configurable options. To modify cell behavior, make the desired changes, and tap the Save button to apply the change. To remove the drop-down list or other validation criteria, tap Remove Rule.

Open a new or existing spreadsheet in the app.

Tap the main menu button, represented by three vertically-aligned dots, and located in the upper-right corner of the screen. When the drop-down menu appears, select Data Validation.

The Data validation interface displays and contains several configurable options. The first, labeled Cell range, specifies the exact location where the drop-down list will reside.

• List of items: When selected, a drop-down list containing text values entered in the accompanying field appears to the user. Tap Add 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 sheet in the current workbook) appears to the user. An example range is 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. It 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 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 entered 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 cells to utilize a user-specified formula.

If the value chosen in the Criteria section creates a drop-down list, an option labeled Show drop-down list in cell is also provided. This option is enabled by default. Disabling this option by selecting its accompanying slider (turning it from blue to gray) dictates that the specified data validation will take place in the selected cells, 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 Appearance setting determines whether text is shown to give the end-user a better idea of the types of values that are accepted in the respective cell range. To activate this virtual helping hand, turn on the Show validation help text toggle switch. Next, select Edit and enter or modify the text that you wish to display.

The On invalid data section controls what happens when the spreadsheet’s user enters something into the selected cells that fails the data validation checks. The two available options allow you to show a warning message in this instance or to reject the user’s input.

When you’re satisfied with your selections, tap Save.

Select the cells that contain the drop-down list or other data validation criteria that you wish to modify or remove.

The Data validation interface displays and contains several configurable options. To modify cell behavior, make the desired changes, and tap the Save button to apply the change. To remove the drop-down list or other validation criteria, tap Remove Rule.

Get the Latest Tech News Delivered Every Day