How to create a drop down list in excel

Table of Contents

What is a drop down list in Excel?

Drop-down lists in Excel streamline selection from predefined options.
Benefits include improved time efficiency and enhanced data accuracy.
Applications include inventory management, project management, expense categorization, product categorization, property listing, and task prioritization.
Two types: static (manually entered, requires manual updates) and dynamic (linked to data range/table, updates automatically).

Prepare your list

Begin by typing the entries for your drop-down list in a new worksheet. It’s best to place these entries in an Excel table because any updates to the list will automatically update the drop-down list. To create a table, highlight the entries and press ‘Ctrl + T’ on Windows or ‘Cmd + T’ on Mac.

Choose the cell where you want your drop-down list to appear.

Select the target cell

Access data validation settings

Navigate to the Data tab, click Data Validation, and select the Setting tab.

Now choose List

Define the list source

In the Source field, input the various values for your drop-down list, separating each with a comma. For example, the illustration below includes four different payment statuses: Paid, Unpaid, Payment In Process, and Refund.

Save your drop down list

Click the OK. You will notice that your cell now contains a drop down list with the values you entered. When you click on this cell, a down arrow will appear on the right hand side.

How do you validate a drop down list in Excel?

Validation of a drop-down list involves ensuring that the data entered into a cell matches one of the listed items. This can be enhanced by:

Setting error alerts: Configure error alerts to notify users when the entered data doesn’t match the list items.
Allowing for additional items: Optionally, allow users to enter items not included in the predefined list.