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.
- How to create a drop down list in excel
- How to create drop down list in excel
- How to display multiple columns in a validation list > Guide
- Changing values in one cell depending on drop down list values in another cell [solved] > Forum - Excel
- Searching names in drop down list in excel 07 [solved] > Forum - Excel
- Dropdown lists in excel 2007 [solved] > Forum - Excel
- Create new sheet based on cell value: Excel, macro > Guide
- Change date format in Excel: to dd/mm/yyyy, mm/dd/yyyy
- Copy data from one excel sheet to another: automatically
- How to take screenshot in Excel: shortcut, sheet, cell
- Fix arrows keys in Excel: Scroll lock, alternative method
- How to apply a function to multiple sheets on Excel
- Convert numbers to words in Excel: without VBA, formula
- How to change author name in Excel: spreadsheet, VBA
- How to enable VBA in Excel: Mac, Office 365
- How many IF statements can you nest in Excel
- How to use Excel color cell if formula
- Check if a value exists in an array VBA
- How to use an IF statement to add 1 to total
- How to perform a partial cell match in Excel?
- VBA select case like: operator, string, statement
- Search and find using VBA in Excel
- How to run macro when data entered in a cell
- What is the VBA code to select last sheet in workbook
- How to insert file path in excel: cell, sheet
- Open and convert an Excel file in Notepad
- How to disable auto recover in Excel
- How to insert GIF in Excel: 365, sheet, VBA
- VBA Excel color codes: index number, list
- How to count names in Excel: formula, using COUNTIF
- How to enter multiple lines in a single Excel cell
- How combine IF function, SEARCH and ISERROR in Excel
- Run macro on opening: worksheet, workbook
- Recalculate Excel workbook before saving
- How to change a column to numeric in Excel?
- Repeat rows in Excel: based on cell value, VBA
- Insert a hyperlink in Excel: with text, to another tab
- Insert picture in Excel: cell, shortcut, using formula
- How to manipulate data in Excel: VBA
- How to automatically transfer data between sheets in Excel
- Select the default number of processors in Excel
- How to create a cascading combo box: Excel, VBA
- Most useful Excel formulas: for data analysis
- How to transfer data from one Excel sheet to another?
- How to add a number of days to a date in Excel
- How to add sheet to workbook: VBA, Excel
- Recover Excel file: previous version
- Mark sheet grade formula in Excel: template
- How to calculate VAT in Excel: formula
- Excel functions in French
- How to copy a Macro into a blank cell
- How to generate email notifications for Excel updates
- How to fill multiple Excel sheets from master sheet
- How to apply if function in Excel: with dates, with text
- Excel send value to another cell
- How to create calculator in Excel VBA
- Split a workbook into individual files in Excel
- Unlock password protected Excel file: Macro
- Select empty cell in Excel: VBA
- Excel export data: from one sheet to another
- Using VBA to find last non empty row: in column, in table
- How to create UserForm: in Excel, VBA
- How to connect VB 6.0 with MS Access
- Copy data from one Excel workbook to another
- Excel VBA add command button programatically
- How to use Excel auto numbering formula
- How to remove leading apostrophe in Excel
- How to change enter key function in Excel
- Compare two Excel sheets: and combine data
- How to insert a transparent image into an Excel document
- How to count occurrences of characters and numbers in Excel
- How to change Excel date format
- Conditional formatting with dates: in Excel
- How to copy data to another workbook: using VBA
- Delete duplicates in Excel: column, formula
- How to show or hide formula bar in Excel: VBA, shortcut
- Transfer Excel data from one sheet to another: VBA
- Send email with attachment Excel: VBA, macros
- Create a new workbook and copy data in Excel via a Macro
- How to change row color in Excel VBA based on value
- Auto generate serial number in Excel: VBA, formula
- Transfer a worksheet to another Excel workbook: without VBA
- Insert an image into an Excel comment box
- Shortcuts to insert in Excel
- Run Macros in Excel: online, shortcuts, VBA
- How to clear formatting in Excel
- How to create a timer in Excel VBA
- How to update dates in Excel spreadsheet: formula
- How to use conditional formatting in Excel
- How to split text in columns in Excel
- How to copy data to multiple worksheets in Excel
- How to insert multiple rows in Excel with a macro?
- Excel IF, AND, OR, and NOT functions
- What is the credit summation formula?
- How to insert blank rows using macros in Excel
- How to use spreadsheets: introduction, formulas, functions