How to Create Drop-Down Lists from Data in Google Sheets

Google Sheets is a powerful and versatile spreadsheet application that allows you to store, manipulate, and analyze data. One of the features that Google Sheets offers is the ability to create drop-down lists in cells, which can help you control the input data, provide predefined options, and make your spreadsheet more interactive and user-friendly.

In this article, we will show you how to create drop-down lists from data in Google Sheets, using two different methods: list from a range and list of items. We will also explain how to customize your drop-down lists, use them in your formulas, and handle invalid data.

List from a Range

The first method to create a drop-down list in Google Sheets is to use a list from a range. This means that you can use the values that have been selected from other cells in the same or a different sheet as the options for your drop-down list. For example, if you want to use the values in cells B1-B9 on sheet 2, you can type Sheet2!B1:B9 to have the data contained in them appear in the drop-down list, or by selecting any of the cells from your sheet directly.

To create a drop-down list from a range, follow these steps:

Select the cell or cells where you want to create a drop-down list.
Click on the Data menu and select Data Validation.
In the Data Validation dialog box, under Criteria, choose List from a range.
In the text box next to List from a range, enter the range of cells that contains the values for your drop-down list, or click on the grid icon to select them from your sheet.
Optionally, you can customize your drop-down list by checking or unchecking the following options:
- Show drop-down list in cell: This option will display a small arrow next to the cell that indicates that there is a drop-down list available. If you uncheck this option, the user will have to double-click on the cell to see the options.
- Show warning: This option will allow the user to enter a value that is not on the list, but it will mark it with a red triangle in the corner of the cell. If you hover over the cell, you will see a warning message that says “Invalid: Input must match one of the listed items.”
- Reject input: This option will prevent the user from entering a value that is not on the list. If they try to do so, they will see an error message that says “Invalid: Input must match one of the listed items.”
- Show validation help text: This option will display a small pop-up message when the user selects the cell, giving them some instructions or information about what they can choose in the cell. You can enter your own text in the box below this option.
Click on Save to apply your changes and create your drop-down list.

List of Items

The second method to create a drop-down list in Google Sheets is to use a list of items. This means that you can enter your own values for your drop-down list, separating them by commas (and no spaces). This option does not allow you to insert data directly from other cells.

To create a drop-down list from a list of items, follow these steps:

Select the cell or cells where you want to create a drop-down list.
Click on the Data menu and select Data Validation.
In the Data Validation dialog box, under Criteria, choose List of items.
In the text box next to List of items, enter the values that you want for your drop-down list, separated by commas (and no spaces). For example, if you want to create a drop-down list with colors, you can type Red,Yellow,Blue,Green.
Optionally, you can customize your drop-down list by checking or unchecking the same options as in the previous method (Show drop-down list in cell, Show warning, Reject input, and Show validation help text).
Click on Save to apply your changes and create your drop-down list.

How to Use Your Drop-Down List

Once you have created your drop-down list in Google Sheets, you can use it in various ways:

To select an option from your drop-down list, click on the cell that contains it and then click on the arrow next to it. You will see a menu with all the available options. Click on the one that you want to enter in the cell.
To change an option from your drop-down list, click on the cell that contains it and then click on the arrow next to it. You will see a menu with all the available options. Click on the one that you want to replace the current value in the cell.
To clear an option from your drop-down list, click on the cell that contains it and press Delete or Backspace on your keyboard.
To use an option from your drop-down list in a formula, you can simply refer to the cell that contains it. For example, if you have a drop-down list with numbers in cell A1, and you want to calculate the sum of that number and another number in cell B1, you can enter the formula =A1+B1 in cell C1.
To copy or move your drop-down list to another cell or range of cells, you can use the standard copy and paste or cut and paste commands. Alternatively, you can drag the cell that contains the drop-down list to another location, or use the fill handle (the small square at the bottom right corner of the cell) to extend the drop-down list to adjacent cells.

How to Handle Invalid Data

If you have enabled the Show warning or Reject input options for your drop-down list, you may encounter some situations where the user tries to enter a value that is not on the list, or where the data in the cells that contain the drop-down list changes due to other factors (such as formulas, imports, or edits).

In these cases, Google Sheets will handle the invalid data in different ways, depending on your settings:

If you have enabled the Show warning option, Google Sheets will allow the user to enter the invalid data, but it will mark it with a red triangle in the corner of the cell. If you hover over the cell, you will see a warning message that says “Invalid: Input must match one of the listed items.” You can also see all the cells that contain invalid data by clicking on Data > Data Validation and then clicking on Find Invalid Data. To fix the invalid data, you can either change it to a valid option from the drop-down list, or remove the data validation from the cell by clicking on Remove Validation.
If you have enabled the Reject input option, Google Sheets will prevent the user from entering the invalid data. If they try to do so, they will see an error message that says “Invalid: Input must match one of the listed items.” To fix the invalid data, you can either change it to a valid option from the drop-down list, or remove the data validation from the cell by clicking on Remove Validation.

Conclusion

Creating drop-down lists from data in Google Sheets is a useful feature that can help you control and validate the input data, provide predefined options, and make your spreadsheet more interactive and user-friendly. You can create drop-down lists from a range of cells or from a list of items, and customize them according to your needs. You can also use your drop-down lists in your formulas and handle invalid data with different options.

We hope this article has helped you learn how to create drop-down lists from data in Google Sheets. For more information and tips on Google Sheets, you can check out these resources:

How to Create Drop-Down Lists from Data in Google Sheets