How to create a drop-down list in Excel

Many users don’t use Excel to perform a one-time calculation, but instead use workbooks on a regular basis. In such cases, being able to select certain information directly from a list simplifies and speeds up the process of working with spreadsheets. Here, we run through the steps for how to create a drop-down list in Excel.

Quick guide for creating an Excel drop-down list

The following quick guide shows you how to create drop-down menus in Excel quickly and easily.

1. Fill in a table with the values that you want to appear in the drop-down list. It’s best to create this table on a separate sheet.
2. Select the cell where you want to create the list. Click on the Data tab. In the tools section, click Data Validation.
3. In the Allow field, select List and in the Source field, select the table elements you created earlier. Click OK.

Now your Excel drop-down list is ready. Click on the gray box to select an element from the drop-down list.

What are drop-down lists in Excel used for?

A drop-down menu enables you and other users to select specific values or information from a list. This provides a number of advantages:

1. The different values can be selected with just one click, meaning that you don’t have to type the entire item or number every time.
2. Typos are prevented by having users select options from a list.
3. Because the values are predefined, other users can’t generate errors by entering incorrect values.

Drop-down lists give you and other users a significantly greater level of convenience, while also making many forms look much more professional.

What you need to know about adding a drop-down list in Excel

A drop-down list is always located in a cell. For this reason, the corresponding cell must be formatted in order to use a drop-down list in Excel. At the same time though, you also need a table with values, which will then be displayed in the drop-down list. It is a good idea to create this on a separate worksheet in the same workbook so that the entries are available at all times but can also be neatly hidden. This list should also be created as an Excel table because Excel can process it better that way, and any changes made to the table are reflected immediately in the linked drop-down list.

In the actual worksheet, select the cell in which you want the drop-down list to appear. Then go to the Data tab in the ribbon. Here you will see the Data Validation item in the tools category. When you click this, a new menu containing the Validation criteria will open. This area gives you the option to allow only whole numbers or text of a specific length, for example.

To add the drop-down list, List needs to be selected. Now specify where the table with the values is located. You can enter this directly, however, because the table is located on another worksheet, you will also have to manually enter this as well. Alternatively, you can use the selection function where you simply highlight the area with the relevant values using the mouse. The information will subsequently appear in the drop-down list.

You can now integrate the information from the Excel drop-down list into various functions. For example, the IF function can be used to trigger another calculation or event depending on the selection made.

To display this video, third-party cookies are required. You can access and change your cookie settings here.