Drop down lists allow Excel developers to restrict user's cell input to a list of options. The full list remains hidden until the cell is selected at which time a 'drop down' list of options is displayed.. If an invalid entry is attempted a customizable error message will display and the entry will be blocked.

Simple Drop down List: Step-by-step.

1. Create a list of allowable entries somewhere in your workbook. Vertical lists are most common but horizontal lists will also work.
2. Select the cell to contain the drop down list
3. Click on Data - Validation at the top of the page and a pop-up dialog box will appear
4. Click on the Settings tab and select Allow:"List"
5. In the Source: box enter the range or range name of the source list
6. ensure that the In cell Dropdown checkbox is checked
7. Close the dialog box.

Alternate to Source Range on worksheet

• The list of user choices can be entered as a list,separated by commas, directly into the Source box. This avoids listing the options directly on the worksheet and helps hide the source list from users.
• Note: the list cannot exceed 256 characters. Use this approach for short lists that fit into the display window as it can be frustrating trying to enter, edit and maintain a longer list.

Limitations

• The main frustrations with in cell dropdown lists are 1) only a maximum of eight lines can be displayed and 2) the font size is too small.
• The 8 line limit and the font size cannot be altered.
• A workaround is to use a Combobox that pops up when the dropdown cell is selected. This approach requires VBA. Drop Down Combobox method

Use Range Names

• The source range should be located out of view and protected if users are to be prevented from altering or corrupting the list. Lists are commonly located at the bottom of the page or on a different sheet.
• If the source range is located on a different sheet then it must be given a range name. Data validation cannot reference a source range on another sheet unless it has a range name. A range name would be entered as =MyRangeName in the Source box.

Ignore blanks checkbox

• The Ignore blank checkbox only applies if a defined range name is used in the Source box and a blank line appears in the source range.
• If Ignore blank is checked then any value can be entered into the Data Validation cell.
• If the Ignore blank cell is unchecked then only values in the Source range can be entered.

Customize Input and Error Messages

• Click on the Input Message tab to create a custom input message that will appear when the user clicks on the Data Validation cell. (see example below)
• Click on the Error Alert tab to create a custom error message if an invalid entry is made to the Data Validation cell. (see example below)

!!!Remove blank rows from the Dropdown list

• If the source range contains blank rows then the Data Validation dropdown list will also contain blanks. This not uncommon especially if the source list can have a variable number of entries.
• If blank rows appear at the end of the dropdown list they can be removed by entering an offset formula into the Source box. Instead of =MyRangeName in the Source box enter this instead: =offset(MyRangeName,0,0,counta(myrangeName),1)

!!!Remove Previously Used Items from Dropdown List

Drop Down Lists - Remove Previously Used Items