This article will teach you how to create drop-down lists in Microsoft Excel using a feature called Data Validation. When applied, the Data Validation function serves to restrict data that can be entered in the target cell, requiring users to select a value from a list of pre-determined inputs. This function is very useful in a number of cases, from planning employee vacation schedules, to monitoring expenses, to general project management. Here's how to get started.
How to create a drop-down list in Excel?
In order to perform this operation, you will need at least two sheets open in your workbook: a working sheet and a blank sheet where you can compile your lists.
The first step is to create your list. To do this, head to your blank sheet and list the items that you'd like to include in your drop-down in Column A. The list can be any length you'd like and can include any type of information — some common list items are Yes/No (often abbreviated as Y/N), Pass/Fail, numbered lists, and dates.
Now, head back to your working sheet and click the cell or cells that you'd like to validate. Then, go to your Data tab and find the option for Data Validation under the Data Groups section:
Next, go to the Settings tab and find the Allow box. Select List from the drop-down menu:
How to fill in the drop-down menu in Excel?
Head to your list worksheet and select all of the items that you'd like included in your list. The Source field on the dialog box will automatically register the fields as you select them. Note that if ever you choose to add or delete an item from your list, Excel will automatically update the data validation:
Make sure that the in-cell dropdown box is selected so that you're sure to see the drop-down arrow next to the cell. Once you're finished, click OK.
Your drop-down list should now be shown in the cells indicated in your working sheet. To test the data validation, try entering both valid and invalid data into the cells. Valid data should register correctly, while invalid data (information not included in your list) should return an error message.
How to create a longer drop-down list in Excel?
If you would like to add items to your drop-down list but find that your space has been limited, you should try inserting cells just above the last cell rather than below it.
Need more help with Excel? Check out our forum!
- Changing values in one cell depending on drop down list values in another cell [solved] > Forum - Excel
- How To Create and Name Worksheets Based on a List via an Excel Macro
- Searching names in drop down list in excel 07 [solved] > Forum - Excel
- Create worksheets with names from a list [solved] > Forum - Excel
- Create drop down list on certain cells [solved] > Forum - Excel
- How combine IF function, SEARCH and ISERROR in Excel
- How to disable auto recover in Excel
- How to run macro when data entered in a cell
- How to copy data to another workbook: using VBA
- Insert picture in Excel: cell, shortcut, using formula
- How to show or hide formula bar in Excel: VBA, shortcut
- How to enter multiple lines in a single Excel cell
- How to automatically transfer data between sheets in Excel
- VBA Excel color codes: index number, list
- Repeat rows in Excel: based on cell value, VBA
- How to use Excel color cell if formula
- What is the VBA code to select last sheet in workbook
- How to count names in Excel: formula, using COUNTIF
- How to use an IF statement to add 1 to total
- How to display multiple columns in a validation list
- How to insert file path in excel: cell, sheet
- Convert numbers to words in Excel: without VBA, formula
- How to copy a Macro into a blank cell
- How many IF statements can you nest in Excel
- How to apply a function to multiple sheets on Excel
- How to perform a partial cell match in Excel?
- How to change author name in Excel: spreadsheet, VBA
- How to insert GIF in Excel: 365, sheet, VBA
- How to take screenshot in Excel: shortcut, sheet, cell
- How to enable VBA in Excel: Mac, Office 365
- Copy data from one excel sheet to another: automatically
- Change date format in Excel: to dd/mm/yyyy, mm/dd/yyyy
- Fix arrows keys in Excel: Scroll lock, alternative method
- Insert a hyperlink in Excel: with text, to another tab