In Microsoft Excel for Microsoft 365 on Windows, the Data Validation Source is the field that tells a List rule where its permitted choices come from. Open Data > Data Validation > Data Validation, choose List under Allow, then enter a comma-separated list, a cell range, or a defined name in Source. For example, a list stored in A2:A5 on the same sheet can be referenced as =$A$2:$A$5.
What the Data Validation Source controls
Data validation lets you set rules for what people can enter in selected cells. The Source field is specific to the List option: it supplies the items shown in the cell’s drop-down menu and the values Excel treats as valid for that list rule. The source is not the destination cell. You select the cells that should have the rule first, then provide the source for their choices.
For example, a status column might allow only Draft, In review, and Approved. A source containing those three choices gives users a drop-down and helps prevent inconsistent entries such as In Review, reviewing, or approved. The same technique can be used for departments, regions, product types, or other small sets of standard values.
A drop-down is a convenient entry aid, not a complete security or data-integrity system. A user may be able to introduce an invalid value by pasting data, importing it, or changing the rule. Treat validation as a way to guide and check ordinary worksheet entry, and inspect data separately when accuracy is critical.
Where to find the Source field
On Windows, select the cells to control and go to Data > Data Validation > Data Validation. In the Data Validation dialog, open the Settings tab, choose List from the Allow menu, and look for Source. Check In-cell dropdown if you want the arrow to appear when a cell is selected. The other tabs let you add an input message or configure an error alert.
Excel for Mac has a Data Validation command on the Data tab, though the dialog layout and ribbon arrangement can differ from Windows. Excel for the web also offers data validation, but some editing options and dialog details may differ from the desktop app. If a workbook’s rule is difficult to edit in a browser, open it in the desktop version of Excel.
Four common kinds of list source
The best source depends on how often the choices change and where they are stored. These are the main options:
- Typed entries: Enter values directly in Source, separated by commas, such as
Draft,In review,Approved. This is quick for a short list that rarely changes. Regional settings can affect the separator Excel expects, so check the result in the dialog if commas do not produce separate items. - A cell range: Store one item per cell and refer to the cells, such as
=$A$2:$A$5. This is easier to maintain than retyping a long list. The dollar signs keep the reference fixed when the validation rule is applied to other cells. - A defined name: Give a range a meaningful name, such as StatusList, and use
=StatusListin Source. Names make rules easier to read and are especially useful when the list is stored on a different worksheet. - A formula that returns a reference: A formula can be used for more advanced setups, but it must be accepted by the Data Validation dialog and return an appropriate list reference. Keep the setup simple unless there is a clear need for a formula-driven list.
For the typed-entry example, the choices are part of the validation rule itself rather than stored in worksheet cells. If an item changes, you must edit the rule. For the range example, changing a value in one of the source cells changes the displayed choice. Neither approach automatically makes the list expand when you add a new item outside the referenced range.
Using a list on another worksheet
A direct range reference in a List source is generally intended to point to cells on the same worksheet as the validation rule. If the choices are on a separate sheet, define a name for the source range and use that name in Source. For example, select the choices on a sheet called Lists, create a name such as StatusList with Formulas > Define Name, and then enter =StatusList in the validation rule. This keeps the source list separate from the worksheet where people enter data.
Use a clear, workbook-unique name and check its Refers to range in Formulas > Name Manager if the drop-down is empty or shows unexpected choices. A name can refer to the wrong sheet, include a heading, or omit the last item. Names are also useful when multiple worksheets need the same list, because one source can support several validation rules.
Keeping a source list up to date
A fixed range such as A2:A5 will continue to point to those four cells. If you add a fifth choice in A6, it will not become available until the source reference is expanded. For a short list, editing the Source field may be the simplest fix. For a list that grows regularly, plan how the source will expand rather than relying on users to remember to revise the rule.
One option is to create a defined name that expands as contiguous entries are added. Suppose the worksheet is named Lists, A1 contains a heading, and A2 downward contains the choices without blank rows. In Formulas > Name Manager, create a name such as StatusList and set Refers to to =Lists!$A$2:INDEX(Lists!$A:$A,COUNTA(Lists!$A:$A)). Then enter =StatusList in the validation Source field. With a heading and four nonblank choices in A1:A5, the range covers A2:A5; adding a fifth choice in A6 extends it. This approach depends on the list being contiguous and the heading being the only non-list value above it in column A.
Another maintainable arrangement is to keep the source values in an Excel Table and manage the validation source through a defined name. Tables are convenient because their data ranges expand as rows are added, but a structured reference is not always accepted directly in the Data Validation Source box. Test the named range after adding an item, and confirm that the new item appears in the drop-down before distributing the workbook.
Related settings that affect the experience
The Source field determines the choices, but the other settings determine how users encounter the rule. In the Settings tab, In-cell dropdown controls whether users see a selection arrow. Ignore blank allows an empty cell under the rule; it does not add a blank choice to the source list. Choose based on whether the field is optional, and test the result in a cell.
The Input Message tab can display a brief instruction when a user selects a validated cell, for example, Select the current order status. The Error Alert tab controls what happens when someone enters a value that does not match the rule. A Stop alert blocks ordinary invalid entry and is appropriate when only listed choices should be accepted. Warning and Information alerts allow users to proceed after a message, so they guide rather than strictly restrict entry.
Validation also has other rule types, such as whole numbers, decimal values, dates, times, text length, and custom formulas. Those rules do not use the List Source field in the same way; they use criteria such as a minimum and maximum or a formula. If Source is missing, first check that Allow is set to List.
Common source problems and how to fix them
- The list appears as one long item: Check the separator used in a typed list. Excel’s expected list separator can depend on regional settings. A range-based source avoids separating individual values in the Source box.
- The menu is empty or missing an item: Check that the source cells contain the expected values and that the range includes every item. Also confirm that the defined name points to the right worksheet and cells.
- Choices from another worksheet are rejected: Define a name for that range and refer to the name in Source. A direct cross-sheet address may not work as expected in the List rule.
- New entries do not appear: The validation rule may refer to a fixed-size range. Expand the range or use a tested expandable named range.
- Invalid values remain in cells: Applying a rule does not automatically clean up values that were already there. Review existing entries and correct or remove any that do not match.
- Pasted data bypasses the expected check: Validation is not a substitute for checking imported or pasted data. Use filters, conditional formatting, formulas, or a separate review process to find unexpected values.
If rules seem inconsistent across a column, select a cell that works and a cell that does not, then compare their settings through Data > Data Validation > Data Validation. Rules can differ between cells even when the cells look alike. Copying a correctly validated cell can also copy its validation, but check that its source references still point to the intended list.
Choosing a source method
Use typed entries when there are only a few stable choices and the rule will be used in one place. Use a cell range when people need to maintain the options in the sheet, or when the list is long enough that editing it in a dialog would be inconvenient. Use a defined name when the source is on another sheet, reused in multiple places, or needs a readable label. Choose an expandable source only when the list genuinely changes over time, and test how it behaves after adding entries.
For shared workbooks, put source values in a clearly labeled, maintained area and explain who is allowed to change them. Hiding or protecting a worksheet can reduce accidental edits, but it does not turn validation into a security boundary. If a process requires strict enforcement across many users or systems, a database or application with enforced constraints may be more appropriate than a spreadsheet rule.
Frequently Asked Questions
What should I type in Excel Data Validation Source?
For a short list, type the choices separated by the separator your Excel installation expects. For values stored in cells, enter a range reference such as =$A$2:$A$5, or a defined name such as =StatusList.
Can the validation list come from another sheet?
Yes. Define a name that refers to the cells on the other sheet, then use that name in the Source field. This is usually more reliable than entering a direct cross-sheet address.
Why is my new list item not showing in the drop-down?
The source may use a fixed range that stops before the new item. Expand the range or change the defined name so it includes the added cell, then reopen the drop-down to check it.
Does a drop-down prevent every invalid entry?
No. It guides normal worksheet entry and can show an error alert, but pasting, imports, or changes to the rule can leave invalid data. Check important data independently.
Can I make the Data Validation list expand automatically?
Yes, with a carefully designed defined name or another supported setup. An expandable name based on a contiguous source range is one option; test it after adding an item and make sure blanks or headings do not distort the range.
Related
- Power Query Reference: What It Does and When to Use It
- Excel Data Validation: Can You Select Multiple Items?
- Protect Sheet in Excel: Allow Editing Specific Cells
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Extract Unique Values in Google Sheets: A Quick GuideTo extract a live list of distinct values in Google Sheets, enter =UNIQUE(A2:A) in an empty cell, replacing…
- How to Insert a Curved Line in Word and Adjust Its ShapeTo insert a curved line in Microsoft Word, choose Insert > Shapes, select Curve in the Lines group,…
- How to Apply a Filter in Google Sheets: Narrow DataTo filter data in Google Sheets in a web browser, select your table, choose Data > Create a…
- Google Sheets Checkbox Color: What You Can ChangeGoogle Sheets does not provide a separate color picker for the checkbox control itself. To make a checked…
- Excel Shortcut Keys to Change Sheets: Windows and MacIn Excel for Microsoft 365 on Windows, press Ctrl+Page Up to move to the previous worksheet, or Ctrl+Page…
- Insert a Personal Signature in Word Online: Steps and TipsYes. In Word Online, the most reliable way to insert a personal signature is to add it as…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.