For example, if you want your drop-down list to include “New York,” “Boston,” and “Los Angeles,” you can type “New York” in cell A1, “Boston” in cell A2, and “Los Angeles” in cell A3. You can place these items in an existing worksheet, or a new one. They can then be referenced in any worksheet in the workbook. For formatting tips, check out our guide on formatting an Excel spreadsheet.
Drop-downs are helpful for information you want to enter consistently and repeatedly. For example, if you’re making a bill tracker, you could have a drop-down with bill types.
If the drop-down you’re creating is a mandatory field, make sure not to check this box.
Click the upward arrow button to minimize the Data Validation window, showing only the cell range text box.
For example, if you have “New York,” “Boston,” and “Los Angeles” in cells A1, A2, and A3, make sure to select the cell range from A1 to A3. Alternatively, you can manually type your drop-down list values into the “Source” box here. In this case, make sure to separate each entry with a comma.
If you don’t want to show a pop-up message, just leave the box unchecked.
The title and input message you enter here will show up on a small, yellow pop-up sticky note next to the drop-down when the cell is selected.
If you don’t want an error message to pop-up, leave the box unchecked.
The Stop option will show a pop-up error window with your error message, and stop users from entering data that isn’t in the drop-down list. The Warning and Information options will not stop users from entering invalid data, but show an error message with the yellow “!” or blue “i” icon.
You can leave these fields empty. In this case, the error title and message will default to Microsoft Excel’s generic error template. The default error template is titled “Microsoft Excel,” and the message reads “The value you entered is not valid. A user has restricted values that can be entered into this cell. “[1] X Research source
Now users can click the drop-down button (downward triangle) next to the cell to select an item. Make changes to the data validation drop-down list by selecting the cell with the drop-down and clicking the data validation button. The data validation window will reopen and you can edit any of the options. You can copy (ctrl/cmd + c) the cell with the drop-down and paste (ctrl/cmd + v) in other cells to duplicate the drop-down.
