To make an Excel drop-down list from cells, select the entry cell and choose Data > Data Validation > Data Validation; set Allow to List and enter a range such as =$D$2:$D$6 in Source. If you mean a list that changes according to another cell’s selection, use a named range or a helper list—the right setup depends on what the source cell contains.
What “based on a cell” can mean
Excel users use this phrase for a few different arrangements. A drop-down can draw its choices from a range of worksheet cells, from a named range, or from a list that changes when another cell’s value changes. These are related, but they are not interchangeable.
A single cell reference used as the list source gives the drop-down that cell’s value as its one choice. It does not make Excel split text such as Red, Blue, Green stored in one cell into three choices. For a normal list, put each item in its own cell and refer to the range. If you want a list to depend on a category selected elsewhere, set up a dependent list, described below.
Data validation controls what users can enter in selected cells; it does not turn a cell into a list-management system. Keeping the choices in a separate range makes them easier to review, update, and reuse.
Create a drop-down from a cell range
For example, suppose the choices are in D2:D6 and users should make a selection in B2. In Excel for Microsoft 365 on Windows:
- Enter one choice per cell in D2:D6. Avoid blank rows within the choices if you do not want blank entries in the menu.
- Select B2, or select a larger range such as B2:B100 if every cell should have the same drop-down.
- Choose Data > Data Validation > Data Validation.
- On the Settings tab, set Allow to List. Leave In-cell dropdown selected, then enter
=$D$2:$D$6in Source. - Select OK. Click the arrow that appears when a validated cell is selected to choose an item.
The dollar signs make the source reference absolute. That matters if you apply the rule to multiple cells: each drop-down continues to use D2:D6 instead of shifting the source range as the rule is applied. The destination cells can still differ; selecting B2:B100 before creating the rule applies the same list to the whole selection.
For a short, fixed list, you can type entries directly into Source separated by commas, such as North,South,West. That is convenient for a few stable choices, but it is harder to maintain than a worksheet range. Use a range when the list may grow, when other people maintain it, or when you want to see the items together.
Use a named range, especially for another worksheet
A direct source reference is simplest when the choices are on the same worksheet as the drop-down. Excel’s Data Validation dialog does not reliably accept a plain reference to a range on another worksheet in the Source box. A workbook-level defined name is a practical way to use a list stored elsewhere.
To create one, select the choice cells, then choose Formulas > Define Name. Give the range a clear name such as ProductChoices, check that the scope is Workbook if you want to use it across sheets, and confirm the reference points to the intended cells. In the validation dialog, set Allow to List and use =ProductChoices as Source. The initial equals sign tells Excel to use the defined name.
Names cannot contain spaces and must follow Excel’s naming rules, so use a name such as ProductChoices rather than Product Choices. A name also makes a source easier to understand later: a workbook owner can inspect the defined name instead of deciphering a sheet address. If a named range has worksheet scope rather than workbook scope, it may not be available from another sheet; check Scope in Formulas > Name Manager.
Keep the list current as choices are added
A fixed reference such as D2:D6 does not automatically include new items typed into D7. You can edit the validation source when the list grows, but that is easy to forget. For a list that changes regularly, consider making the source data an Excel Table: select the list and choose Insert > Table, confirming that the table has headers if it does. Tables expand when new rows are added directly below them.
Data Validation’s Source box is not a good place to type a table’s structured reference directly. Instead, create a defined name that refers to the table column, then use that name as the validation source. For example, if the table is named ChoicesTable and its column header is Item, define a workbook name such as ItemChoices with a reference to =ChoicesTable[Item]; then use =ItemChoices in Source. Check the name in Name Manager if the table or column is renamed.
Another Microsoft 365 option is a helper list that spills from a formula, useful when the drop-down should show unique, nonblank choices from a larger source. Suppose D2:D100 contains the source values and F2 is clear for the results. With that layout, enter =SORT(UNIQUE(FILTER($D$2:$D$100,$D$2:$D$100<>""))) in F2. It returns the distinct nonblank values in sorted order. In the validation Source box, use =$F$2# to refer to the spill range. FILTER, SORT, and UNIQUE are available in Microsoft 365 and Excel 2021 or later; earlier versions need a different method. Keep the cells below F2 clear so the results can spill.
Make a dependent list from another cell
A dependent list changes its choices based on a selection in another cell. A familiar example is choosing a department in A2 and then selecting a role in B2. One straightforward approach uses a defined name for each department’s role list and INDIRECT in the second validation rule.
For example, suppose A2 contains a department and the role choices are in separate ranges. Create names that correspond to the department values, replacing spaces with underscores: if A2 displays Sales Team, name its role range Sales_Team. In B2’s Data Validation settings, choose List and enter =INDIRECT(SUBSTITUTE($A2," ","_")) as Source. The formula converts spaces in the selected department to underscores and INDIRECT resolves the resulting text as a range name. The reference locks column A but leaves the row relative, which is useful if you apply the rule to B2:B100.
This method works best when every department has a matching defined name and the naming convention is consistent. It can be fragile if categories contain punctuation or if names are changed without updating the ranges. Also, changing A2 does not necessarily clear a role already selected in B2. Users should recheck the dependent selection after changing the category; a formula or separate check can flag mismatched pairs.
For more flexible category data, a helper formula can generate the relevant choices from a table, with the validation list pointing to the helper’s spill range. That design is useful when categories are maintained as data rather than as a set of separately named ranges. Keep the helper area visible or document it, so someone maintaining the workbook can understand where the choices come from.
Choose the approach that fits the workbook
- Fixed choices: type a short comma-separated list into Source when it is unlikely to change.
- Editable choices on the same sheet: use a cell range such as
=$D$2:$D$6. - Choices on another sheet: define a workbook-level name and use it as the source.
- A growing list: use a table-backed defined name or a suitable dynamic helper list.
- Choices determined by another selection: use dependent lists, with named ranges for simple category structures or helper formulas for more flexible ones.
Think about maintenance as well as initial setup. A compact workbook with a handful of stable choices may not need tables or helper formulas. A shared workbook with frequent updates benefits from a clearly labeled source sheet, descriptive names, and instructions explaining how to add new choices without breaking the validation rule.
What validation does—and does not—enforce
In the Data Validation dialog, the Error Alert tab controls what happens when someone enters a value that is not allowed. A Stop alert blocks an invalid entry through ordinary typing; Warning and Information alerts allow the user to continue after a prompt. The Input Message tab can display a short instruction when a validated cell is selected. These options help users understand the rule, but they do not make the workbook a secure data-entry system.
Users can sometimes bypass validation by pasting data into a cell, and copying cells can replace validation rules. Validation also does not automatically remove an old selection when a source list changes. For important records, use additional checks, review procedures, or other controls rather than treating a drop-down as a guarantee that every stored value is valid.
Common problems and fixes
- The arrow is missing: select the cell and reopen Data > Data Validation. Confirm Allow is List and In-cell dropdown is checked. The arrow generally appears when the cell is selected.
- The list has blanks or unexpected entries: inspect the entire source range for blank cells, stray spaces, or old values. Remove unwanted source entries or use a helper list that filters blanks.
- New items do not appear: check whether the source uses a fixed range that stops above the new entries. Expand it, use a table-backed name, or update the helper formula’s input range.
- A cross-sheet source is rejected: create a defined name that refers to the source range and use the name in the Source box.
- A dependent list is empty: confirm the selected category matches a defined name after the formula’s text conversion. Check for spaces, punctuation, misspellings, and an incorrect scope.
- Invalid values still appear: a value may have been pasted into the cell or the validation rule may have been overwritten. Reapply the rule and review existing entries separately.
Frequently Asked Questions
Can I make a drop-down from a list typed into one cell?
A cell containing comma-separated text is treated as one cell value, not automatically split into separate choices. Put each option in its own cell, or create a helper range that produces the individual options.
Can the source list be on another sheet?
Yes. Define a workbook-level name that refers to the cells on the other sheet, then enter that name, prefixed with an equals sign, in the validation Source box.
Can I make the drop-down update when I add a new option?
Yes, if the source is designed to expand. A table-backed defined name or a dynamic helper list can include new values; a fixed reference such as D2:D6 will not include an item added in D7 by itself.
Does changing the first drop-down clear the second one?
No. Excel’s dependent validation rule limits future entries in the second cell, but it does not automatically erase a selection that became inconsistent after the first cell changed. Check or clear the second selection when the category changes.
Can I prevent users from pasting invalid values?
Data validation is useful for guided entry, but it is not a security boundary. Pasting can bypass or replace validation, so inspect data and use additional controls when accuracy is critical.
Related
- Close Excel Shortcut Key for Windows and Mac
- Close Group Excel Shortcut: Close Workbooks and Rows
- Close Excel Shortcut: Windows and Mac Keys
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Box Plot Explained: Create and Read OneAn Excel box plot, also called a box-and-whisker chart, summarizes how a set of numbers is distributed: its…
- How to Make Serial Numbers in Excel: 4 Easy MethodsTo make serial numbers in Excel, enter 1 and 2 in the first two cells, select both, then…
- How to Make a Work Schedule in Google Sheets: A GuideTo make a work schedule in Google Sheets, create a table with one shift per row, add columns…
- How to Read an Excel Box and Whisker Plot Step by StepTo read an Excel box and whisker plot, match each box to its category, read the median line…
- 0.3-Inch Hanging Indent in Word: Set It Up PreciselyTo create a 0.3-inch hanging indent in Word, select the paragraph or paragraphs, open the Paragraph dialog, choose…
- Contact Number List Template for Excel: Setup GuideA contact number list template for Excel is a simple table with one row per person and separate…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.