How to create a drop down list in Excel?

Excel allows you to create a drop down list for limiting the values of a column to certain predefined ones. This is considered Data Validation. In this article, I will teach you how to do this. I am using the free version of Office 365 Excel for this example, but other versions will work just the same. I am also using the Student Information Dataset from Kaggle for this example.

Let us say that we want to limit the values in the “Department” column to the values “Physics,” “Chemistry,” “Biology,” “Mathematics” and “Computer Science.” In order to achieve this, first select the Department column. Then, go to the “Data” tab in the ribbon and select “Data Validation.”

A pop-up box will appear that lets you limit the data in this column to only specific values. In this case, choose “Allow: List” and put the values, separate by commas, in the “Source” field. We can also select to allow or disallow blank values.

Clicking on “Apply” adds this Data Validation to the column. In a case of a List, a drop down appears in each cell, allowing us to only pick one of the predefined values. Excel will not allow you to enter any other values.

Data Validation can also be used to limit cells to numerical values. Let us add Data Validation to the GPA field so only values between 0.0 and 4.0 can be input into the cells in the GPA column. To achieve this, we select the GPA column, then click on “Data Validation” again.

This time, “Allow: Decimal” values between 0.0 to 4.0.

Now, Excel will not allow you to input values into cells of the GPA column that are not in the range [0.0 – 4.0]. If you try to input a value not within this range, an error will be shown.