Home > Validation > Drop Downs > Create Make Excel Drop Down List in Worksheet CellCreate a drop down list of items in a cell, with Excel's Data Validation feature. This will make data entry easier, and reduce input errors and typing mistakes. Video tutorials and step by step instructions for data validation dropdown lists are below |
What is Data Validation?In Microsoft Excel, the data validation feature helps you control what can be entered in your worksheet. For example, you can:
In this tutorial, you'll see how to create a drop down list of choices in a cell, like the list of employee names shown below. |
How to Create a Drop Down ListWith Data Validation, you can create a dropdown list of options in a cell. There are 3 easy steps: 1. Create a Table of Items OR Create a List Note: Data validation is not foolproof. It can be circumvented by pasting data into the cell, or by choosing Clear > Clear All, on the Ribbon's Home tab. 1. Create a Table of ItemsThe easiest way to create and maintain the list of options, is to type them on a worksheet. You can do this on the sheet that will contain the drop down lists, or on a different sheet. In this example, the list items will be stored on a spreadsheet named Lists. NOTE: A data validation list can show up to show 32,767 items from a list on the worksheet.
The table is now a Named Excel Table. |
2. Name the ListNext, you will create a named range that does not include the heading cell in the table. This named range will be dynamic -- it will adjust automatically if new items are added to the list, or existing items are deleted from the list. You can use the Name Manager, as shown in the video, or use the Name Box. Both methods are described below. The Name Box method is quicker, but you can't add a comment. Use the Name ManagerThis is the method shown in the video. You can set a name, scope and comments.
Use the Name BoxThis is a quick way to name a range of cells.
|
Excel Drop Downs From List on Different SheetIf the list of items for your Excel drop-down list are NOT in a named Excel table, you can use the following steps to create the data validation drop downs. Create a Named RangeThe first step is to name the range of cells where your list of items is located. Here’s the quickest way to name the list of items:
Create the Drop Down ListsAfter you’ve named the source list, you can add the drop down lists, by following these instructions:
|
Allow Entries Not in Drop Down ListWhen you add data validation to a cell, the Error Alert feature is automatically turned on. It stops the users from entering invalid data in the cell. In some worksheets, where there is a drop-down list of valid items, you might want to allow other items to be entered too. For example:
Turn Off Error AlertThe quickest way to allow users to type items that are not in the list, is to change the data validation error alert setting. To change that setting, follow these steps:
Other Data Validation Error Alert OptionsThere are other Error Alert options too, if you don't want to turn that setting off completely. For details on setting up the error alerts, and other data validation messages, go to the Show Data Validation Messages page. For example, you could show a warning message, and ask if they want to continue with the entry. Or, just show an information message, that reminds them the item was not in the drop-down list of valid entries. |
FAQ: How do I make drop down list in Excel?
|
Get the Sample FileYou can download the sample Drop Down Lists file here: Drop
Down List Workbook. The zipped file is in xlsx format, and does not contain any macros. |
Create Dependent Drop Down Lists
Dependent Drop Down Lists Video
Last updated: July 24, 2023 3:38 PM