How to create a drop-down list in Excel
Excel, Microsoft’s spreadsheet software, helps you work with numbers and other values. You can create tables, insert formulas and display diagrams effortlessly. Many users don’t just use the program to perform a one-off calculation though, but rather draw on workbooks on a regular basis. In such cases, being able to select certain information directly from a list simplifies and speeds up the process. Here, we run through the steps for inserting drop-downs in Excel.
Excel with Microsoft 365 and IONOS!
Use Excel to create spreadsheets and organize your data - included in all Microsoft 365 package!
What are drop-down lists in Excel used for?
A drop-down menu enables you or other users to select specific values or content from an existing list. This provides a number of advantages:
- 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.
- Typos are prevented as a result of direct selection.
- Because the values are predefined, other users also can’t generate errors by entering incorrect values.
In this way, drop-down lists give you and other users a significantly greater level of convenience, while also making many forms look much more professional.
How to add a drop-down in Excel: everything you need to know
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 box 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 formatted as a 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.
The following steps are based on Excel version 2016. The details or names in the procedure may differ for other versions.
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 Data 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 either enter this directly (note that because the table is located on another worksheet, you will also have to manually enter this as well) or use the handy 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.
Make sure that the In-cell drop-down option is enabled, otherwise the list will only be regarded as a restriction on manual entry and no drop-down list will be created.
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.
Do you want to adapt or change your Excel drop-down list? In our dedicated article, you will find out how to edit drop-down lists in Excel.
Excel with Microsoft 365 and IONOS!
Use Excel to create spreadsheets and organize your data - included in all Microsoft 365 package!