Why Your Data Validation Dropdown is Blank

by

Why Your Data Validation Dropdown is Blank

The procurement template was approved by Finance, audited by Legal, and deployed to forty buyers. This morning, three buyers report that the “Vendor” dropdown — which should have shown a list of 89 approved vendors — is blank. They can type freely, but no dropdown options appear. You open the template yourself: dropdown works fine. The data validation is identical for every cell. The vendor list exists on the same sheet. Yet for those three buyers, the dropdown is empty. Data validation dropdowns fail in subtly specific ways depending on what the validation source is and how it was authored.

Before You Start: The 60-Second Diagnostic

Three checks:

  • Inspect the validation source: Select a problem cell → Data → Data Validation → Settings. Note what’s in the Source field.
  • Verify source range still exists: If Source is =VendorList, open Name Manager and check.
  • Test on a fresh copy: Open the file from a fresh location — file corruption or path issues can break references.

Step-by-Step Solution

H2: Check the Source Reference

The Source field can hold:
– A direct range: =$A$2:$A$90
– A named range: =VendorList
– A literal list: Vendor1,Vendor2,Vendor3
– A formula returning a list: =OFFSET(...), =FILTER(...), etc.

For each type, the failure mode differs:

#### H3: Direct Range Failures

If the source is =$A$2:$A$90 but rows have been deleted or the sheet renamed, the reference may resolve to an empty range. Click into the source field — #REF! appearing indicates the source was destroyed.

Fix: re-select the correct range with cells now in place.

#### H3: Named Range Failures

If the source is =VendorList and Name Manager shows the name with #REF! in Refers to, the named range was broken by row/column deletion.

Fix: open Name Manager → click the broken name → fix the Refers To field to point at the correct range.

#### H3: Cross-Sheet Reference Failures

For dropdowns sourced from a different sheet, the source must include the sheet name: =Sheet2!$A$2:$A$90. If Sheet2 was renamed, the validation breaks silently.

Fix: edit the validation source with the new sheet name. Or wrap with INDIRECT for resilience:

=INDIRECT("Sheet2!$A$2:$A$90")

INDIRECT-based validation works in modern Excel; pre-Excel 2010 had limitations.

H2: Dynamic Array Source Failures

For Excel 365 sources using FILTER or other dynamic arrays:

The source might read =$A$2# (spill range reference).

If the formula in A2 returns an empty result or #CALC!, the spill range is empty — validation has nothing to show.

Diagnose: click cell A2 directly. If it shows #CALC!, the source formula has an error. Fix the source formula first.

For FILTER-based sources, ensure the FILTER has an if_empty argument:

=FILTER(VendorTable[Name], VendorTable[Status]="Active", "")

The "" fallback prevents #CALC! when no matches exist.

H2: Validation Range Mismatch

If validation was applied to a range using *fixed* references, copying the cells can break the reference:

  • Validation on B2 reads =$A$2:$A$90.
  • Copy B2 to B100. Validation on B100 still reads =$A$2:$A$90 (absolute reference) — works.
  • But if validation read =A$2:A$90 (column relative, row absolute), copying B2 to C100 changes to =B$2:B$90 — now pointing at the wrong column.

Fix: re-set validation with proper absolute/relative reference style for your intent.

H2: Cell Was Pasted From Validation-Free Source

If a user paste a value over a validated cell, the paste sometimes carries the source’s validation (which is empty), replacing your validation. Result: the dropdown disappears.

Fix:
1. Select the affected cells.
2. Data → Data Validation → Settings.
3. Re-apply the source.
4. Train users to use Paste Special → Values to avoid carrying source-cell validations.

Information Gain Box: The Hidden List-Length Limit

Here is the limit nobody surfaces clearly: Excel’s data validation List type has practical and hard limits on the source size.

  • Hard limit: 32,767 characters total for an inline comma-separated list typed into the Source field.
  • Practical limit: Lists exceeding ~1,000 items render slowly in the dropdown — users see lag when opening the dropdown.
  • Reference-source limit: No hard limit when referencing a range, but performance degrades around 10,000 rows.

For very long lists (10K+ items), the dropdown becomes unusable. Solutions:

  1. Filter by typing: enable Show input messages when cell is selected and add an instruction like “Type the first letters of the vendor name.” Modern Excel auto-suggests matches.
  2. Hierarchical dropdowns: use a category dropdown first, then a narrowed second dropdown.
  3. Replace with a typed-search lookup: use a free-text cell with a separate VLOOKUP that fetches the matching record — bypasses dropdown entirely.

These workarounds are necessary for any list above 1,000 entries.

Comparison Table: Wrong Way vs. Correct Way

Issue Wrong Way Correct Way
Cross-sheet source Direct reference INDIRECT for resilience
Long list (10K+) Single dropdown Hierarchical or typed-search alternative
Dynamic source OFFSET-based =$A$2# (spill range from FILTER)
Range deletion Hope it auto-adjusts Use Excel Table column reference
Paste over validation Lose dropdown silently Train Paste Special → Values
Diagnose blank dropdown Re-create validation First check source field for #REF!
Sheet rename Validation breaks Update reference OR use INDIRECT

Original Image Descriptions

Screenshot 1: Show a procurement template cell B2 with a clicked-but-empty dropdown. Open the Data Validation dialog showing “Source: =VendorList”. Behind, the Name Manager shows “VendorList: Refers to: =#REF!”. Draw a red circle around the #REF! and a red arrow to the empty dropdown. Add a red annotation: “Broken named range = empty dropdown without error.”

Screenshot 2: Show the Name Manager with VendorList fixed to point at the correct range, and the dropdown now showing 89 vendor names. The Data Validation Source field still reads =VendorList. Draw a red circle around the corrected Refers to field. Add a red annotation: “Fix the source once, every cell referencing it auto-recovers.”

Frequently Asked Questions

Q: Can I make the dropdown only allow values from the list (no free typing)?
A: Yes — in Data Validation → Error Alert tab, set the Style to Stop and provide a custom message. This rejects any value not in the source list. The default Style is “Stop” but some templates have it set to “Warning” or “Information”, which allow override.

Q: Why does my dropdown work in Excel desktop but show as blank in Excel Online?
A: Excel Online has historically had limited support for complex validation sources, especially INDIRECT-based or dynamic-array-based dropdowns. Test in the target environment before relying on advanced patterns. As of late 2024, Excel Online supports most validation features but spill-range sources ($A$2#) still occasionally fail to render.

Q: How can I tell which cells have data validation applied?
A: Home → Find & Select → Go To Special → Data Validation. Choose “All” or “Same” (relative to a selected cell). Excel highlights every cell with validation. Useful for auditing what’s protected and what is not.