How to limit the contents of a cell to a dropdown list of items.

Sometimes you want to limit what the user can input to a certain list of items. Sometimes it is merely convenient for the user, other times you want to force the user to avoid mispellings so that you can use things like SUMIF or COUNTIF in a different part of the spreadsheet. Regardless, it is easy to do, as I have shown below:

1. First, make a list of the items that you want to be in the dropdown list. (I usually do this on a separate worksheet, but for the purposes of showing how, I will do the entire thing on one sheet here.)

Make List

 

2. Under "Formulas," select the "Name Manager."

Name Manager

 

3. When the Name Manager appears, choose "New." Then Type in a name for your list (I used "Weekdays" below), and select what data it will refer to. Hit "OK," then close the Name Manager.

Select Data

 

 

4. Select the cell(s) for which you want to limit the options. Under "Data", choose Data Validation.

Data Validation

 

5. Choose "List" from the "Allow:" menu.

Choose List

 

6. Enter the name of the list you created above, preceded by an equals sign, in the "Source:" box. Hit "OK."

Enter Name

 

7. Notice that, now when you click on the cell(s) that you applied Data Validation to, an arrow appears next to it, which allows you to choose from the cells you referenced in the Name Manager.

See List

 

If you try to enter a value which is not in the list, Excel will give you an error and prevent that value from being entered.

Now it Works

 

 

Back to Resources page.