Excel Drop Down List

If you have a list of potential values that you would like in a cell or column, you can use the data validation tools to enforce the restriction and present a drop-down list to choose from.

Step 1: Create Your List of Values

Screenshot of the List Selection sheet

Firstly, I entered the potential values for my drop down list in a separate sheet titled List Selection. You can enter the values anywhere, but a separate sheet is nice because of the ability to hide the sheet making it relatively unnoticeable.

Step 2: Apply the Rule to Cells

Screenshot showing highlighted cells to apply the rule to

In this case, I wanted to apply the rule to the entire Category column, so I selected it by clicking the column label (letter C). I then deselected the top cell containing the column label, because I don’t want the data validation to apply to that cell. I deselected by holding Command (Mac) or Ctrl (Windows) and clicking the cell. Select any cells you want the drop down to apply to and click Data Validation. Be aware once the rule is applied you will only be able to enter the values included in the drop down list.

When the Data Validation window pops up, select List from the “Allow” drop down. The select the list we made earlier in the source box. You may customize input messages and error alerts if you wish, but it isn’t necessary. Ensure In-cell dropdown is checked and click OK.

Screenshot showing completed drop down menu.

Done! At this point, you may be wondering why this is worth the effort. The drop down is nice and user friendly, but Excel auto completes values it knows anyway. The big advantage comes from the data validation aspect. By enforcing values in this way, your spreadsheet won’t allow for capitalization or spelling differences that can make further analysis of the data difficult.