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!
Related articles
The Excel: SUMIF function explained
Get more out of Excel: SUMIF makes it easier to work with balance sheets and analyses. Add only the values you need – completely automatically. From simple calculations to complex formulas, it’s all possible with the SUMIF function in Excel. How do you use the function? And what does the syntax look like?
Faster with Excel: the VLOOKUP function explained
It can often be incredibly time-consuming to search for a specific entry in an Excel table by hand, which is where VLOOKUP comes into play. This practical function allows you to find the exact value for a specific search criterion. The VLOOKUP function is indispensable for managing price lists, members directories, and inventory catalogues. To ensure you can benefit from this practical function,…
Word Macros: How to Create Macros in Word
Anyone who writes on a computer will regularly use Word, the word processing program from the software giant Microsoft. To simplify working with this Office application, operating steps and program commands can be automated by creating and executing “Word macros”. Read on to find out everything you need to know about macros in Word.
Wrapping text in Excel: How to display the contents of your Excel cells on multiple lines
Excel cells often contain only a few numerical values or short text. But when you’re dealing with lengthy contents, the Microsoft application often hides a portion of the information. You can prevent this by wrapping text, which Excel allows you to do in two different ways. In this article, you’ll learn everything you need to know about this feature.
Freezing Rows and Columns in Excel: A Guide
The bigger an Excel table is, the harder it is to maintain an overview. The problem is that cells like the header bar become hidden without the use of special formatting. The following article shows you how to freeze certain Excel columns and rows so they stay visible when scrolling.