In Excel for Microsoft 365 on Windows, a data validation list source is the set of entries that appears in a cell’s drop-down list. To set one, select the cells, choose Data > Data Validation, set Allow to List, and enter a cell range, a defined name, or a comma-separated set of items in Source.
What the list source controls
Data validation lets a workbook limit what users can enter in selected cells. With Allow set to List, Excel presents values from the source as choices in an in-cell drop-down. For example, a Status column might offer Draft, In progress, and Complete, helping people use consistent wording instead of entering variations such as Done or Finished.
The source defines the available choices; it does not automatically fill the cell or guarantee that every value in the workbook is valid. The user chooses an entry, or may type one if the validation settings permit it. The list can come from values typed directly into the Source box or from cells on a worksheet.
The drop-down arrow normally appears when a validated cell is selected. In the Data Validation dialog, the In-cell dropdown option controls whether that arrow is shown. The setting does not change the source list itself.
Set up a list from cells
A cell range is usually the clearest source when the list may change or when it contains more than a few choices. For example, put category names in A2:A8 on the same worksheet, then set the source to =$A$2:$A$8. The drop-down will show the contents of those seven cells.
- Select the cell or range that should have the drop-down.
- On the ribbon, choose Data > Data Validation.
- On the Settings tab, set Allow to List.
- Enter the source range in Source, or use the range-selection button to select the cells.
- Make sure In-cell dropdown is selected, then choose OK.
To apply the same list to multiple cells, select all the target cells before opening Data Validation. If you are editing an existing rule, Excel may offer to apply the changes to other cells with the same settings; review that choice rather than assuming every similar cell should change.
Keep the source cells free of headings unless you want the heading to appear as a choice. A range that includes a blank cell can create a blank choice in the drop-down. Hide the source list on a separate worksheet if that makes the workbook easier to use, but keep the source easy for maintainers to find.
Choose between typed items, ranges, and names
There are three common ways to supply list entries. The best choice depends on how often the values change and whether the source is reused.
- Typed items: Enter values such as
Low,Medium,Highdirectly in Source. This suits a short, fixed list. The list is embedded in the validation rule, so changing an item means editing the rule. Excel’s regional settings can affect which character separates typed items; if commas are treated unexpectedly, use a cell range instead. - Cell range: Point Source at cells containing the choices. This is easy to inspect and update, and it avoids maintaining a long string inside the dialog. A fixed range will not automatically include entries typed below its last cell.
- Defined name: Give the source range a memorable name, such as StatusOptions, and enter
=StatusOptionsin Source. A name is useful when the same list is used in several places or the source is on another worksheet.
For a named range, select the source cells and use Formulas > Define Name, or open Formulas > Name Manager to create or edit a name. For example, with status choices in Lists!A2:A4, define StatusOptions to refer to =Lists!$A$2:$A$4, then use =StatusOptions as the validation source. The list displayed is the content of those three cells. Names must be valid Excel names; spaces are not allowed, so use an underscore or combine words.
Use a list stored on another worksheet
A direct reference to a range on a different worksheet is often rejected in the Data Validation Source box. A defined name is the dependable approach: define a workbook-level name that refers to the cells on the other sheet, then use that name with a leading equals sign as the source. This also makes the rule easier to read than a sheet reference embedded in every validation setting.
For a sheet whose name contains spaces, Excel’s reference syntax encloses the sheet name in single quotation marks. The Name Manager can build the reference when you select the cells, reducing the chance of a typing error. Check that the name’s scope is Workbook if it needs to work from multiple sheets; a name scoped only to one worksheet may not be available elsewhere under the same unqualified name.
In Excel for Mac, the Data Validation command is also available from the Data tab, though ribbon layout can differ by release. Excel for the web can work with many validation lists, but some workbook-management tasks and interface details differ from desktop Excel. If a rule is difficult to edit in the browser, open the workbook in the desktop app.
Make a list easier to maintain
A fixed address such as A2:A8 is simple, but it does not grow when new entries are added in A9. For a changing list, consider storing the choices in an Excel table. Tables expand as rows are added, which makes them useful as maintained source data. A practical pattern is to define a name that refers to the table’s relevant column, then use the name in the validation Source box. This separates the user-facing rule from the location and size of the list.
Microsoft 365 also supports dynamic-array formulas, which can produce a changing list of results. One approach is to put the formula in a worksheet cell and define a name referring to its spill range, then use that name as the validation source. This can be useful when the choices should be generated from other data. Because the formula and name are separate workbook components, check both when troubleshooting; the validation rule alone may not reveal why the generated choices are missing or outdated.
Avoid putting the only copy of an important list in a hidden or obscure place without documenting it. If another person needs to maintain the workbook, use a descriptive sheet name and a clear defined name. Also consider whether the list should include duplicates: duplicate source entries can appear as repeated choices, so clean the source if that would confuse users.
Understand prompts and error alerts
The Input Message tab lets you show guidance when a user selects a validated cell, such as Enter a status for this order. This is separate from the list source: it explains what to do but does not add an item to the drop-down.
The Error Alert tab controls what Excel does when someone enters a value that does not match the rule. A Stop alert blocks the invalid entry. Warning and Information alerts allow the user to proceed after a message, so they are less strict. If the goal is consistent data, use Stop and make the permitted choices clear. These settings are not a security boundary: pasting, filling, importing, or using code can sometimes place values in cells without the normal selection process. Validation should not be treated as protection for confidential or critical data.
Fix common list-source problems
- The list is empty or incomplete: Reopen Data > Data Validation and inspect Source. Confirm the range includes the intended cells and that those cells actually contain values. A fixed range may exclude newly added rows.
- Excel rejects a reference: If the list is on another sheet, use a defined name. Check for misspelled names, incorrect scope, or a reference that points to a deleted or moved range.
- The drop-down arrow is missing: Select the cell and check that In-cell dropdown is enabled in the rule. The arrow is normally visible only while the cell is selected.
- Typed choices split incorrectly: Your regional list separator may not be a comma. Use a range source to avoid separator ambiguity.
- Invalid text is already present: Changing the rule does not necessarily repair existing entries. Review the cell contents and correct them. Excel’s Data Validation tools include Circle Invalid Data for identifying entries that do not meet a rule.
- New source entries do not appear: Check whether the validation rule points to a fixed-size range. Extend the reference or switch to a maintained table-based or named source.
When copying cells, remember that validation settings can be copied along with other cell contents and formatting. If a cell unexpectedly has the wrong list, inspect its Data Validation settings rather than assuming its appearance tells you which source it uses.
Frequently Asked Questions
Where do I find the source for an existing drop-down?
Select a cell with the list, choose Data > Data Validation, and read the Source box on the Settings tab. If the source is a defined name, use Formulas > Name Manager to see which cells that name refers to.
Can the list source be on a different sheet?
Yes. Define a name for the source range on the other worksheet, then enter that name in the validation Source box with a leading equals sign. This avoids the common problem of a direct cross-sheet reference being rejected.
Why does adding an item to my source range not update the drop-down?
The rule may point to a fixed range that does not include the new cell. Expand the range, or use a source that grows with the list, such as a table-backed named range.
Can I use a formula as the list source?
Data Validation expects a list reference, a defined name, or typed entries; it does not accept every formula as a direct Source value. For a generated list, place the formula results in worksheet cells and use a suitable named reference to those results.
Does a drop-down force users to choose one of its entries?
Not in every situation. A Stop error alert can reject an invalid typed entry through normal editing, but pasted or programmatically inserted values may bypass validation. Check and clean important data separately when accuracy matters.
Related
- Excel Conditional Formatting Number Formats Explained
- Excel Conditional Formatting With Dates: A Practical Guide
- Excel Conditional Formatting for Date Ranges Explained
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Merge Cells in Excel When the Merge Option Is Greyed OutTo enable Merge & Center in Excel, select a normal, contiguous range of at least two cells, exit…
- Outlook Calendar Template: Create and Reuse EventsTo create a reusable calendar template, make an appointment in classic Outlook for Windows, then choose File >…
- Can You Hide a Column in Excel From View?Yes. In Excel for Microsoft 365 on Windows, select a column or columns, right-click a selected column heading,…
- Excel Payment Log: How to Track Payments and BalancesTo make an Excel payment log, create an invoice register for amounts due and a separate transactions sheet…
- Excel Cell Show Current Date? TODAY and NOW ExplainedTo show the current date in an Excel cell, enter =TODAY() and press Enter. To show both the…
- How to Save an Excel File in Read-Only Mode: StepsTo make an Excel workbook open with a read-only prompt, use File > Save As > Browse, then…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.