If your choices are in A2:A5 and the drop-down will go in C2, select C2, choose Data > Data Validation, set Allow to List, and enter =$A$2:$A$5 in Source. Excel does not require a formula to make every drop-down: you can also type a short set of choices directly or use a named range for a list stored elsewhere.
What the drop-down list formula does
An Excel drop-down is a data validation rule, not a special cell formula. The rule tells Excel what entries to offer when someone selects a cell. Its Source box can point to a worksheet range, contain a comma-separated list, or refer to a defined name. The selected item is entered into the cell; the source formula does not calculate that selection.
This distinction matters when you search for an Excel formula for a drop-down. In the usual case, you do not type a formula into the cell that will hold the answer. Instead, you create a validation rule and put a range reference or other valid source in its Source box. You can still use worksheet formulas to prepare or update the choices, then connect the validation rule to their results.
A list can help standardize entries such as departments, order statuses, or product types. If one person enters “In progress” and another enters “In-Progress,” later sorting, filtering, and counting may treat them as different values. A drop-down reduces those avoidable variations, though it does not prevent every possible way of entering or pasting data.
Create a basic drop-down from a cell range
For a list you expect to maintain, placing its choices in worksheet cells is usually the clearest approach. With the choices in A2:A5 and the intended drop-down in C2, enter =$A$2:$A$5 in the Data Validation Source box. The dollar signs make the reference absolute, so it continues to point to the same cells if the validation rule is copied.
- Enter one allowed choice per cell in a column or row. Avoid blank cells within the list if you do not want an empty-looking option.
- Select the cell or cells that should have a drop-down. To apply the same rule to a block, select the entire block before opening the dialog.
- On the ribbon, choose Data > Data Validation. In the Settings tab, choose List from the Allow menu.
- Make sure In-cell dropdown is selected, enter the source range in Source, and select OK.
Excel for Microsoft 365 on Windows follows those ribbon steps. Excel for Mac also has Data Validation on the Data tab, although the dialog layout can differ. In Excel for the web, look for Data > Data Validation; available controls can vary as Microsoft updates the web app. If the list is important, reopen the rule and confirm its source after editing it.
Choose between a typed list and a range
A typed list is convenient for a few fixed choices. With C2 selected as the target, open Data Validation, set Allow to List, and type the choices separated by commas in Source, for example Open,In progress,Closed. That text is a list of items, not a worksheet formula. Regional settings can affect which character Excel expects as the separator, so a cell range is more reliable if the typed list is not interpreted as separate choices.
A range is a better fit when choices might change, are reused, or need descriptive labels. It is easier to review and update a source column than to edit a long string inside a validation rule. Keep the source list in a clearly labeled area or a separate worksheet, and avoid sorting or deleting its cells without checking the rule.
Excel generally does not accept a plain direct reference to a different worksheet in the list Source box. For choices stored on a sheet named Lists in A2:A20, create a defined name that points to that range, then use the name as the validation source. This also makes the rule easier to understand than a long reference.
- Use a typed list for a few short, stable choices.
- Use a range for lists that will be edited, reused, or maintained by someone else.
- Use a defined name when the source is on another worksheet or you want a descriptive reference.
Use a named range for choices on another sheet
To create a name for the choices on Lists, select Formulas > Name Manager > New. Give the range a simple name such as StatusChoices, and set Refers to to =Lists!$A$2:$A$20. This assumes the worksheet is named Lists and the choices occupy A2:A20. Avoid spaces in a name; an underscore can separate words.
Then select the target cell, choose Data > Data Validation, set Allow to List, and enter =StatusChoices in Source. In this step, StatusChoices must be the name you created. The equals sign tells Excel that the source is a defined reference rather than a set of literal items. If the sheet name contains spaces, Excel may show apostrophes around it in the Refers to field; let Excel create or adjust the reference rather than guessing at punctuation.
Defined names are also useful when multiple cells or sheets need the same choices. If you change the cells covered by the name, every validation rule using it can use the updated range. Check the name in Name Manager if the drop-down unexpectedly stops showing new entries: the name may still refer to the original, shorter selection.
Make a list update automatically
In Microsoft 365, dynamic-array functions can prepare a distinct, sorted list from source data. Suppose raw choices are in A2:A100 and you want the cleaned list to spill from D2. Enter =SORT(UNIQUE(A2:A100)) in D2. SORT orders the results, and UNIQUE keeps one instance of each value. These functions are available in Microsoft 365 and Excel 2021; older Excel versions may not recognize them.
To connect the results to a drop-down, create a name in Name Manager called SortedChoices and set Refers to to =Sheet1!$D$2#. This assumes the formula is in D2 on a worksheet named Sheet1. The # refers to the complete spill range, including however many results the formula currently returns. In the validation rule, use =SortedChoices as the Source. Using a defined name is a practical way to point a validation list to a spill range.
Keep the cells below D2 clear so the formula can spill. If something blocks the output, Excel reports a spill error and the drop-down source will not work as intended. Also consider blank cells in the original data: UNIQUE can return a blank value if the source includes blanks. If an empty choice is not wanted, clean the source data or use a formula that filters out blanks. Dynamic lists are useful for changing inventories or customer names, but they need a little more setup than a fixed range.
Create a dependent drop-down
A dependent drop-down changes its choices based on an earlier selection, such as showing product types after a user selects a department. One traditional method uses a defined name for each group and INDIRECT in the second list’s Source box. For example, if A2 contains a category whose text exactly matches a defined name, the dependent list source can be =INDIRECT($A$2). This assumes the first selection is in A2 and matching defined names have already been created for the categories.
Names cannot contain spaces, while category labels often do. You would need to make the labels and names compatible, or use a different design. INDIRECT also builds a reference from text, which can make a workbook harder to audit than a simple source range. For a workbook with many changing categories, consider a carefully designed table and a dynamic-array approach, or keep separate lists with clear names. Test the dependent rule with every category, including labels that contain punctuation or spaces.
Common problems and how to fix them
- The arrow is missing: Select the cell and open Data > Data Validation. Confirm Allow is List and In-cell dropdown is selected. The arrow may only appear while the cell is selected.
- The list is empty or incomplete: Check the Source field, the range boundaries, the defined name, and whether the source cells contain blanks. If using a spill range, check for a spill error or a blocked output cell.
- The list does not include new entries: A fixed reference such as A2:A20 does not automatically include a new choice entered in A21. Extend the reference or use a suitable dynamic source.
- Excel rejects a value that looks valid: Check for leading or trailing spaces and differences in punctuation. Also review the Error Alert tab; a Stop-style alert blocks entries that are not in the list, while other settings may warn without preventing them.
- Users can still paste other values: Data validation helps guide entry, but it is not a security boundary. Pasting or other edits can bypass or replace validation in some situations. Check important data after importing or pasting.
To show an instruction when someone selects the cell, use the Input Message tab in Data Validation. To explain why an entry was rejected, use the Error Alert tab and write a useful message. These options make the list more understandable, especially when a choice has a business meaning that is not obvious from its label.
Maintain and copy validation rules
When you copy a cell with a drop-down, the validation rule may be copied along with it. If you want to fill the same rule down a column, apply validation to the whole destination range before entering data, or copy a correctly configured cell. After copying cells from another workbook or pasting a large block, inspect the rules: a paste can overwrite existing validation.
To review a rule, select a cell that uses it and open Data > Data Validation. If the selected cells have different rules, Excel may not show one common setting. For a workbook with many lists, document the source ranges and names, and keep the source data in a dedicated area. This small maintenance habit helps prevent a list from quietly pointing to an outdated range.
Frequently Asked Questions
What formula do I put in Excel to create a drop-down list?
Usually, you do not put a formula in the target cell. Create a List rule with Data > Data Validation, then use a range reference or defined name in Source. For a short list, type the choices there instead.
Can I use a formula as the source for a drop-down?
Yes. A source can use a defined name that refers to a range or to a dynamic spill range. The list formula prepares the available choices; the validation rule still needs to point to those results.
How do I make a drop-down list that grows?
Use an expanding source design rather than a short fixed range. In Microsoft 365 or Excel 2021, a dynamic-array formula can produce a changing list, and a defined name can refer to its spill range. Check that the source formula has room to return results.
Why can’t I use a list from another sheet directly?
Excel’s validation Source box does not generally accept a plain cross-sheet range reference. Create a defined name for the source cells on that sheet, then use the name in the validation rule.
How do I remove a drop-down?
Select the cell or cells, open Data > Data Validation, and choose Clear All in the dialog. This removes the validation rule; it does not necessarily clear values already entered in those cells.
Related
- Excel Data Validation Simple List: Create and Manage It
- Excel Data Validation List: Can It Show Two Columns?
- Excel Data Validation List With a Blank Option
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Power Query Nested If: Syntax and ExamplesIn Power Query, write a nested conditional as if condition1 then result1 else if condition2 then result2 else…
- Copy Values in Excel: Keep Results, Not FormulasTo copy only the displayed results of cells in Excel, copy the source range, select the destination’s top-left…
- Excel Filter Duplicates Without Deleting Data SafelyYes. In Excel for Microsoft 365 on Windows, add a helper column that identifies repeated values, then choose…
- XLOOKUP Excel Column Match: Find a Column by HeaderTo match a value in an Excel column and return a value from another column, use XLOOKUP with…
- Excel MIN Ignore NA: Formulas That WorkYes. In Excel for Microsoft 365, use =AGGREGATE(5,6,A2:A10) to find the smallest numeric value in A2:A10 while ignoring…
- COUNTIF Examples: Formulas for Common Spreadsheet TasksUse Excel’s COUNTIF function to count cells in one range that meet one condition: =COUNTIF(range,criteria). For example, it…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.