Excel Data Validation Source: How to Find It

To find a dropdown list’s source in Excel for Microsoft 365 on Windows, select a cell with the dropdown, open Data > Data Validation, and check the Source box on the Settings tab. The box shows whether the choices come from typed items, a worksheet range, a defined name, or a formula. You’ll need access to the workbook and, if the sheet is protected, permission to unprotect it or view its validation settings.

What You’ll Need

The Excel workbook containing the dropdown cell.
Permission to select the cell and open its Data Validation settings. Editing protection may prevent access to the settings.
If the source points to a defined name or formula, the desktop version of Excel is useful for tracing it in Name Manager or the formula bar.

Step-by-Step

Select a cell that has the dropdown. Click the cell itself, not just its label or a nearby cell. A small arrow may appear at the right edge when the cell is selected; click it to confirm the choices you expect are available. The arrow may only show while the cell is active, so its absence at other times does not prove that validation is missing. If you are not sure which cells have dropdowns, use the discovery step later in this guide.
Open the validation settings. On the Windows ribbon, choose Data > Data Validation. In some ribbon layouts, Data Validation appears in the Data Tools group. Click the main button or its dropdown and choose Data Validation if a menu is shown. The Data Validation dialog opens. If the command is unavailable, check whether the worksheet is protected or whether you selected an object instead of a worksheet cell.
Check the Allow setting. On the Settings tab, look at the Allow box. A dropdown list uses List. If it says Whole number, Decimal, Date, Time, Text length, or Custom, the rule is not an ordinary list dropdown, so there may be no list Source to inspect. When Allow is set to List, the Source field appears below it. You can read the entry without changing it; use Cancel to close the dialog if you have not made edits.
Read what appears in Source. The Source field can contain items typed directly into the rule, a cell reference, a name, or a formula. A typed list might look like Small,Medium,Large; those choices are stored in the validation rule itself rather than in worksheet cells. If the field begins with an equals sign, it generally refers to a range, a defined name, or a formula that returns a list. Do not assume the visible dropdown choices are the source address: the field is the place to inspect the rule’s actual input.
Identify a direct worksheet reference. If the values are in cells on the same worksheet, the Source box may contain a reference such as =$A$2:$A$10. This example assumes the list items occupy cells A2:A10 on the same worksheet as the dropdown; the dollar signs lock the reference when it is copied. Note the sheet name and range, then go to those cells to review the values, blanks, duplicates, or errors. If the reference contains a different address, follow that address instead of assuming the example range applies to your workbook.
Look up a defined name. If Source contains a name such as =CategoryList, the dropdown uses a defined name rather than spelling out a cell address. This example assumes the workbook has a defined name called CategoryList that refers to the list cells. Open Formulas > Name Manager, find the matching name, and read the Refers To column to see its target. Check the Scope column too: a name can apply to the whole workbook or only to one worksheet. If there are several names with similar labels, select the one whose scope matches the sheet containing the dropdown.
Follow the name’s target to the list. In Name Manager, select the name and inspect Refers To. It may show a simple cell range, a range on another sheet, or a formula. If the target is a range, navigate to it using the Name Box to the left of the formula bar: enter the address and press Enter. If the reference points to another defined name, look up that name as well. If the target includes a formula, do not replace it just to reveal the list; inspect what it references and preserve the original rule unless you intend to edit it.
Inspect a formula-based source carefully. A formula can build or return a list, including one located on another worksheet. For example, if the list entries are in Lists!A2:A10 and the dropdown is on a different sheet, a source might use =INDIRECT("Lists!A2:A10"). This example assumes a worksheet named Lists and list entries in A2:A10. If your Source box shows a function or a named formula instead, trace its references in Name Manager or the formula bar; the displayed items can change when the formula’s inputs change. Treat the formula as part of the rule, not as a static address.
Find other cells with validation when needed. To locate validated cells on the current worksheet, use Home > Find & Select > Go To Special, choose Data Validation, then choose All or Same and click OK. All selects cells with any validation rule; Same selects cells with the same validation type and settings as the active cell. This can reveal other dropdowns that share the rule. It does not show their source by itself, so select a discovered cell and open Data > Data Validation to inspect its Source field.

Common Mistakes

Checking the wrong cell. A nearby cell may look like part of the same form but have no validation, or it may use a different list. Confirm the selected cell address in the Name Box before opening the dialog.
Looking for Source when Allow is not List. A validation rule can restrict numbers, dates, text length, or a custom formula. Those rules do not use the list Source field. Confirm Allow before concluding the dropdown source is missing.
Assuming every source is a cell range. A comma-separated set of choices is saved directly in the rule. A source beginning with an equals sign may instead be a defined name or formula. Inspect the full entry before searching worksheet cells.
Ignoring name scope. Excel can have a workbook-level name and a worksheet-level name with the same label. The scope can affect which one applies. Check both the name and its scope rather than selecting the first similar result in Name Manager.
Editing while trying to inspect. Typing in Source changes the rule. If you only want to look, avoid clicking into the field and changing its contents; close the dialog with Cancel. If you make an accidental edit, Cancel discards it while the dialog is still open.
Expecting a hidden source sheet to be visible. Lists are often stored on a hidden worksheet. A hidden tab does not mean the source is gone. Check the referenced sheet name, and ask the workbook owner before unhiding or altering a sheet in a shared template.

Tips

Record the full source before changing anything. Note the cell address, Allow setting, Source entry, and any error-alert options. That gives you a reference if a later edit changes the dropdown.
Use the Name Box for quick navigation. Once you have a range address from the source or Name Manager, enter it in the Name Box and press Enter. Excel selects the referenced cells, making it easier to inspect values without scrolling through a long sheet.
Check the actual choices as well as the address. A valid source range might include blanks, outdated entries, or unexpected spaces. Compare the cells in the source range with the items shown in the dropdown before deciding that the rule is broken.
Use the right inspection tool for the source type. For typed choices, read the Source field. For a defined name, use Name Manager. For a range, navigate to its cells. For a formula, inspect its references and inputs. This avoids changing the rule while trying to understand it.
On Mac, use the equivalent ribbon controls. In Excel for Mac, select the cell and choose Data > Data Validation; the dialog’s Settings tab contains the list source. Name Manager is available from the Formulas tab in current desktop versions. Ribbon placement can vary slightly by release.
For Excel for the web, switch to desktop Excel for complex tracing. The web app can expose data-validation controls, but its available editing and name-inspection options may differ. If you cannot see the full source or investigate a defined name there, choose Open in Desktop App and follow the Windows or Mac steps above.

Troubleshooting

Data Validation is disabled or the settings cannot be opened. Check whether the sheet is protected. If you have authorization and the password, use Review > Unprotect Sheet, then reopen the settings. If you do not have permission, ask the workbook owner rather than trying to bypass protection. Also make sure the active selection is a normal worksheet cell.
The dropdown arrow is not visible. Select the cell and look for its arrow. Then open Data Validation and check Allow. A list rule can have the In-cell dropdown option cleared, which means the cell may still restrict entries without showing an arrow. If the rule is List but the option is off, decide whether enabling it is appropriate before making a change.
The Source box looks empty or unavailable. Confirm that Allow is set to List. If it is, verify that you selected the intended cell and that the dialog is showing its current rule. A cell may have no validation at all, or the rule may be a different type. Use Go To Special to select cells with validation and test another relevant cell.
The source refers to a range you cannot find. Check for a sheet name in the address and look for hidden worksheets. For a name, open Name Manager and inspect Refers To and Scope. A name may refer to a formula or another name rather than a simple range. If the sheet is very hidden or the workbook structure is protected, ask the owner to make the source traceable or provide the relevant list location.
The dropdown choices do not match the cells you expected. Reopen the cell’s Source field and compare it with the intended range or name. Check whether the source is a formula that depends on another cell, whether the name is sheet-scoped, and whether the referenced cells contain blanks or old values. After finding the cause, update the source only if you are authorized; then reopen the dropdown to confirm the intended choices appear.
The source works on one sheet but not another. Compare both cells’ validation settings and inspect any names with worksheet-level scope. Copying a cell can bring a different validation rule, and a sheet-scoped name may resolve differently on another worksheet. Select each affected cell separately and trace its own Source entry rather than assuming the rules are identical.

Frequently Asked Questions

Can I see the source without changing the dropdown?
Yes. Select the cell, open Data > Data Validation, and read the Source field. Close with Cancel if you only inspected the settings and want to avoid saving an accidental change.

How do I find the source if the list is on another sheet?
Check whether Source contains a defined name or formula. Look up a name in Formulas > Name Manager and follow its Refers To target. A hidden worksheet may hold the cells, so do not rely only on the visible sheet tabs.

Why does the Source box show a name instead of an address?
The rule uses a defined name, often to make a list easier to reuse or to point to a range that can change. Open Name Manager and check the matching name, its scope, and its Refers To entry.

Can I find every dropdown in a workbook at once?
Go To Special can select cells with data validation on the active worksheet. It is not a single workbook-wide source report. Repeat the search on other sheets, or use a workbook-specific auditing process if you need a complete inventory.

Does this work in Excel for Mac and Excel for the web?
In current desktop Excel for Mac, select the cell and open Data > Data Validation, then inspect Settings. Excel for the web has fewer or different tracing controls in some situations; use Open in Desktop App if a name or formula is difficult to inspect.

What if I only see a typed list like Small,Medium,Large?
Those choices are stored directly in the validation rule, so there may be no source range to locate. The commas separate the entries in the Source field. If an item itself needs a comma, use a cell-based list or an appropriate name instead of assuming the typed list will treat it as one item.

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.