Excel: Creating Drop-Down Menus

Create list
Create list

Drop down lists are useful for utilizing a controlled vocabulary. You are also able to create input message, indicate whether a blank is acceptable, etc.

  • Select a blank worksheet and re-label the tab as “lists”.
  • Create a list of valid entries, either in a single row or column. They should be entered in the order that they appear in the drop-down.
  • Highlight the range of cells (include an empty cell in case you need to add to the list).
  • In the “name” box to the left of the formula bar, enter a name for your list.
  • Select the cell where you want the drop-down list.

Data Validation
Data Validation
  • Windows: Go to the Data tab (on the ribbon) > the Data Tools group > Data Validation. Mac: Go to the Data menu > Validation

  • Settings tab: under “Allow” select List. Make sure “in cell drop-down” is selected, and check “ignore blank” if you wish to allow blank entries. Under “source” enter the name of your list preceded by an equal sign (=ListName).
  • Input Message tab: this is optional. Enter text if you’d like to provide instructions which will display when the cell is selected.
  • Error Alert tab: also optional. This will return an error to the user if they enter the wrong information. Be careful, because it you make it too strict, it will prevent the user from continuing.

  • You can now drag or copy/paste this cell as needed. A drop-down box will appear to the right of the cell when the cell is selected.

Managing Names:

Defined Names
Defined Names

Under the Formula’s tab, use the "Defined Names" group to manage and define names. Here, you may create, delete, modify, provide comments, and use names in formulas.