#SPILL! with Data Validation: Creating Dynamic Dropdowns

A #SPILL! error occurs when setting up dynamic dropdown lists in Excel if the dynamic array formula generating your list encounters an obstacle on the worksheet. While Data Validation allows you to reference dynamic arrays using the spill operator (#), entering a formula like =UNIQUE() or =FILTER() directly inside the Data Validation “Source” box or placing the formula on the worksheet where cells beneath it are blocked causes the formula to fail, triggering #SPILL! and breaking the dropdown menu.

Quick Fix

Do not write dynamic array formulas directly inside the Data Validation dialog. Instead, place your formula (e.g., =SORT(UNIQUE(A2:A100))) in a dedicated helper cell on the sheet, such as M2. Then, set your Data Validation Source to reference that cell followed by a hash mark: =M2#. Clear any data located below M2 so the list can spill freely.

---

How Dynamic Arrays and Data Validation Interact

Standard Excel dropdown lists require a static range reference, such as =$A$2:$A$20. However, modern Excel allows lists to expand and contract automatically as data is added or removed.

To build a dynamic dropdown correctly, two components must work together:

1. The Generator Formula: A dynamic array formula (UNIQUE, FILTER, SORT) placed in a worksheet cell that calculates the list options and spills downward.
2. The Data Validation Reference: The dropdown cell itself, which points to the starting cell of that array using the spill range operator (#).

[ Sheet Cell M2 ] ──► Contains: =SORT(UNIQUE(Employees[Department]))
                      Spills down into M3, M4, M5...

[ Cell C2 Dropdown ] ──► Data Validation Source: =M2#
                         Automatically inherits all spilled items from M2

The #SPILL! error occurs when the generator formula cannot physically output its values onto the worksheet grid.

---

Root Causes of #SPILL! in Dropdown Setups

1. Entering Array Formulas Directly Inside the Data Validation Dialog

A frequent mistake is opening Data > Data Validation, selecting List, and typing:

Source: =SORT(UNIQUE(A2:A50))   <-- Incorrect

The Data Validation input box cannot host dynamic array allocation. It cannot “spill” internally. When you enter an array-generating formula here, Excel either rejects the entry outright with an alert or returns a single #SPILL! string inside the dropdown menu itself.

The Fix:
Always separate the calculation from the validation control:

1. Pick an out-of-the-way column or a separate calculation tab.
2. In cell Z2, enter: =SORT(UNIQUE(A2:A50)).
3. In your target dropdown cell, open Data Validation.
4. Set Allow to List.
5. Set Source to =Z2#.

---

2. The Spill Range Is Blocked by Data or Formatting

When you enter =SORT(UNIQUE(A2:A50)) into helper cell Z2, Excel requires empty cells directly below it (Z3, Z4, Z5, etc.) to display the unique items.

If any cell in that downward path contains:

• An old hardcoded value.
• A spacebar entry (" ") or empty string (""), detailed in #SPILL! Error: Non-Empty cells in the spill range (The “Ghost” character).
• A merged cell, addressed in #SPILL! Error: The “Merged Cell” Blockage.
• The dropdown cell itself (placing the formula directly above or within the input cell).

The formula halts and displays #SPILL!. Because cell Z2 evaluates to #SPILL!, the downstream Data Validation rule pointing to =Z2# fails or renders a one-item dropdown containing only the text #SPILL!.

The Diagnostic Check:

1. Click cell Z2.
2. Look for the thin dashed border extending downward. This indicates the intended spill boundary.
3. Click the yellow warning icon next to Z2 and select Select Obstructing Cell.
4. Press Delete to clear the obstruction. The formula will immediately populate, and the dropdown will work.

---

3. Attempting to Generate the Array Inside an Excel Table

If your data entry form is housed inside an official Excel Table (created via Ctrl + T), placing a dynamic array formula in a Table column will immediately trigger #SPILL!, as explained in #SPILL! in Excel Tables: Why Dynamic Arrays can’t live in Tables.

Structured Tables require uniform rows and do not support dynamic array spilling. If you attempt to build your dropdown generator formula inside a Table column, the entire column breaks.

The Fix:
House your generator formulas on a separate, standard worksheet tab (e.g., named Lists or Admin), leaving your Tables strictly for data entry and records.

---

Advanced Setup: Cascading (Dependent) Dropdowns

When building dependent dropdowns—where selecting a “Region” in Cell A2 filters the “Cities” available in Cell B2—#SPILL! errors frequently occur due to circular references or syntax mistakes.

Step-by-Step Reliable Configuration:

1. Set Up the Primary Dropdown:
   • In cell M2, generate the primary list: =SORT(UNIQUE(DataTable[Region]))
   • Set Data Validation on A2: Source: =M2#
2. Set Up the Dependent Generator:
   • In cell N2, write a FILTER formula that depends on the value selected in A2: =SORT(FILTER(DataTable[City], DataTable[Region] = A2, "No Cities Found"))
3. Set Up the Dependent Dropdown:
   • Set Data Validation on cell B2: Source: =N2#

Why this breaks with #SPILL!:
If cell A2 is blank, the FILTER formula in N2 returns "No Cities Found". However, if the third argument (the fallback) is omitted and A2 is empty, FILTER returns an empty array error (#CALC!), which breaks the downstream dropdown, a failure mode covered in #N/A in Data Validation Dropdown Lists.

Ensure your fallback argument is always populated with a safe single-cell string to prevent validation breaks.

---

Summary Checklist for Dynamic Dropdowns

Step	Correct Practice	Incorrect Practice
Formula Location	Helper cell on standard sheet (e.g., M2)	Inside Data Validation dialog or Table column
Validation Syntax	=$M$2# (Includes hash symbol)	=$M$2 (Picks only 1st item) or =$M$2:$M$20
Spill Corridor	Clear, unmerged, empty cells below helper	Cells populated with notes, headers, or spaces
Dependent Logic	Dynamic FILTER pointing to active input	Volatile INDIRECT strings that break array tracking

For a broader understanding of dynamic array boundaries and troubleshooting unexpected spill behavior, see Troubleshooting #SPILL! and #CALC! Errors: The Dynamic Array Diagnostic Guide and #SPILL! vs. The Implicit Intersection Operator (@).