How to Add a Drop-Down List in Excel: Step-by-Step Guide

To add a drop-down list in Excel for Microsoft 365 on Windows, select the cell or cells, choose Data > Data Validation > Data Validation, set Allow to List, enter a source range or list of choices, and select OK. The drop-down lets people choose a permitted entry instead of typing one, which helps keep data consistent in forms, trackers, and shared workbooks.

Before you begin, decide which cells should have the list and prepare the choices you want users to see. You can type a short set of choices directly into the validation settings, or keep the choices in worksheet cells and point the list to that range. A range is easier to maintain when you expect the choices to change.

What You’ll Need

An Excel workbook where you can edit the cells and data validation settings.
The destination cell or range that should contain the drop-down.
Your list of choices, either typed in the settings or entered in worksheet cells. For example, you might use Draft, In progress, and Done as task statuses.
If the choices are on another worksheet, a defined name for the source range. The steps below explain how to create one.

Step-by-Step

Prepare the list choices. For a short list, type each choice into a separate cell in a spare area of the worksheet, such as H2:H4. Put one choice in each cell, without blank rows between them. Keep the labels concise and check spelling, capitalization, and spacing; Excel presents these entries as written. You can also type a short comma-separated list directly in the validation dialog, but a cell range is more convenient if you may need to edit or reuse the choices.
Select the cells that need the drop-down. Click one cell, or drag across a contiguous range such as B2:B50. If you want the same list in more than one separate range, hold Ctrl while selecting additional ranges on Windows. Apply validation before users enter data where possible. Adding a list does not automatically remove or correct values that were already in the selected cells.
Open Data Validation. On the ribbon, select Data > Data Validation > Data Validation. In the Data Validation dialog, use the Settings tab. If the ribbon is narrow, the Data Validation command may appear in a smaller Data Tools group or under a collapsed group, but the command name is the same. Do not choose Remove Duplicates or another Data Tools command by mistake.
Choose a list as the allowed value. In the Settings tab, open the Allow menu and select List. Leave Ignore blank selected if an empty cell should be allowed. Make sure In-cell dropdown is selected; this is what displays the arrow when a validated cell is active. If you clear that checkbox, the rule may still restrict entries, but people will not have the visible list arrow to choose from.
Enter the source range or choices. For a list stored on the same worksheet, enter a reference in Source. With the choices Draft, In progress, and Done in H2:H4 on the same sheet as the destination cells, use =$H$2:$H$4. The resulting drop-down contains those three entries, in that order. The dollar signs keep the source range fixed if the validation is copied. Alternatively, type a short list such as Draft,In progress,Done directly into Source. Use the separator your Excel regional settings expect; if the choices appear as one item or Excel rejects the entry, use a worksheet range instead.
Set helpful guidance and error behavior. On the Input Message tab, you can turn on a message and enter a short title and instruction, such as Select a task status. Excel shows the note when someone selects the cell. On the Error Alert tab, keep Show error alert enabled if users should be stopped from entering values outside the list. Choose Stop for strict enforcement, then add a clear message explaining that they should select an available option. Warning and Information alerts allow users to proceed after a prompt, so they are less restrictive.
Save the rule. Select OK to apply the validation. Click one of the destination cells and look for the arrow at its right edge. Select the arrow, then choose an entry. You can also type a valid list value into the cell; the rule does not require choosing with the mouse. Test an unlisted entry as well if you set a Stop alert, and confirm that Excel rejects it.
Use a source list on another worksheet, if needed. Excel’s list validation source is simplest when it refers to cells on the same sheet. For a source on a different sheet, create a defined name first: select Formulas > Define Name, enter a short name such as StatusOptions, and set Refers to to the source cells. With choices in Lists!A2:A4, set the defined name’s reference to =Lists!$A$2:$A$4. Select OK. Then select the destination cells, open Data Validation, choose List, and enter =StatusOptions in Source. The drop-down will show the entries in the named range. Use a name without spaces; underscores are acceptable.
Apply the list to more cells or change it later. To reuse a rule, copy a validated cell and paste it into the destination cells, or select the full destination range before creating the rule. Copying a cell with validation can also copy its formatting and contents, so check those after pasting. To edit the rule, select a cell that has it and return to Data > Data Validation > Data Validation. Update Source or the alert settings and select OK. To remove the rule, open the dialog, choose Clear All, and select OK; this removes validation, not necessarily the cell’s existing value.

In Excel for Mac, the feature is also called Data Validation and is available from the Data tab; dialog labels and ribbon arrangement can differ slightly. In Excel for the web, use the Data tab and Data Validation options where available. If a command or setting is missing in your version, open the workbook in the desktop app to use the full dialog. Once configured, the list is saved with the workbook.

Common Mistakes

Leaving In-cell dropdown unchecked. A validation rule can be active without the visible arrow. Return to the Settings tab and select the checkbox if users need to browse the choices.
Using a source range with blank cells. Blank entries can show up as empty choices or make the list confusing. Keep source choices together without gaps, and remove accidental spaces or duplicates.
Expecting the rule to fix existing data. Validation mainly governs new or edited entries. If destination cells already contain values that are not on the list, review and correct those separately.
Typing a cross-sheet address directly into Source. A direct reference to another worksheet may be rejected. Define a name for the source range and use that name in the validation source instead.
Assuming the drop-down is a search box. A standard validation list presents choices; it does not automatically provide a searchable picker. For long lists, consider organizing choices clearly or using a different data-entry design.
Using inconsistent text in the source. Trailing spaces and near-duplicate labels such as In progress and In Progress can create confusing options. Clean the source cells before applying the rule.

Tips

Store reusable choices in a dedicated sheet. A worksheet named Lists keeps source values out of the way of the main form. You can hide that sheet if appropriate, but hiding it does not secure the values or prevent an editor from changing them.
Use one source range for repeated rules. A defined name makes it easier to reuse a list across worksheets and gives the Source field a readable reference. If you update the values within the named range, the drop-down reflects those updated entries.
Make the destination range broad enough. If a form will grow, apply validation to the likely input area in advance. For a formal Excel table, test how new rows behave in your workbook rather than assuming every layout copies validation identically.
Keep labels understandable. Use options that make sense to the person filling in the sheet. If extra explanation is needed, add an Input Message or a note nearby rather than making each list entry long.
Protect the source from accidental edits. If several people use the workbook, consider protecting the worksheet or keeping the list on a controlled sheet. Check protection settings carefully so users can still select and edit the intended input cells.
Consider how blanks should work. Leave Ignore blank selected if users may not know an answer yet. Clear it only when an entry is required, and remember that data validation is not a substitute for checking that a completed form has no empty required fields.

Troubleshooting

The drop-down arrow is not visible. Click the validated cell; Excel normally shows the arrow only while that cell is active. If it still does not appear, open Data Validation and check that Allow is List and In-cell dropdown is selected. Also confirm you selected the cell that actually has the rule, rather than a neighboring cell.

Excel says the source is invalid. Check that the source range exists and that the reference includes the equals sign. For a same-sheet source, a reference such as =$H$2:$H$4 should point to the intended cells. If the source is on a different sheet, create a defined name and use that name instead. If you typed entries directly, replace them with a cell range; commas or other list separators can vary with regional settings, and direct lists have a character limit.

The list is empty or missing an option. Inspect the source cells for blanks, spelling differences, or an incorrect end row in the range. Confirm the defined name points to the right cells by checking Formulas > Name Manager. If you added a choice beyond the original source range, expand the range or update the name so it includes that cell.

People can still enter values that are not listed. Reopen the Error Alert tab and make sure Show error alert after invalid data is entered is selected and the alert style is Stop. Warning or Information allows users to continue after a message. Also note that pasting data into cells can bypass some validation behavior, so review imported or pasted values when accuracy matters.

The rule appears on some cells but not others. Select a cell that works and inspect its Data Validation settings, then compare it with a cell that does not. Reapply the rule to the full intended range. If you copied cells, verify the destination range and check whether the paste operation replaced validation with a different rule.

Frequently Asked Questions

Can I add a drop-down list without putting the choices in cells?
Yes. For a short list, select List in Data Validation and type the choices into Source separated by the separator your Excel settings use. A source range is generally easier to edit and avoids separator issues.

Can the list choices be on a different worksheet?
Yes. Create a defined name that refers to the choices on the other sheet, then enter that name with a leading equals sign in the Data Validation Source box. This avoids relying on a direct cross-sheet reference there.

Can I allow someone to type a value that is not in the list?
Yes. On the Error Alert tab, clear Show error alert after invalid data is entered, or choose a less restrictive alert style. That makes the list a guide rather than a strict restriction, so entries may differ from the listed choices.

How do I change or delete a drop-down?
Select a cell with the rule and open Data > Data Validation > Data Validation. Edit the source or settings and select OK. To remove validation, choose Clear All in the dialog and confirm; the current cell value is not automatically erased.

Will the drop-down update when I change its source list?
It updates when you edit entries inside the referenced range. If you add a choice outside that range, expand the source reference or update the defined name so the new cell is included.

Does a drop-down list work in Excel for Mac and Excel for the web?
Data validation lists are available in current Excel for Mac and Excel for the web, although ribbon placement and dialog details can vary. If you cannot find a needed setting in the web version, open the workbook in desktop Excel.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.