Creating Dynamic Dropdowns in Excel with Spill Arrays (The # Operator Trick)

The most annoying thing about Excel dropdowns is that they don't grow. You add a new employee or product to your list, but your dropdown ignores it. Here is the fix.

In the "Old Excel," creating a dynamic dropdown required complex OFFSET formulas or formatting everything as a Table. It was fragile and hard to teach.

In modern Excel (Office 365 and 2021+), we have Dynamic Arrays. These arrays "spill" results into neighbor cells. But most people don't know you can feed a Spill Array directly into a Data Validation list using a secret character: the Hashtag (#).

The Secret: Adding a # after a cell reference (e.g., =A1#) tells Excel to select "Cell A1 and everything that spills from it."

Step 1: Create Your Source List

First, we need a clean, sorted list of options. Let's say you have raw data in Column A with duplicates.

Go to a new sheet (e.g., a "Settings" sheet) and type this formula in cell D1:

=SORT(UNIQUE(A2:A100))

This will instantly create a sorted, unique list of items that spills down from D1 to D10 (or however many items you have).

Step 2: The Data Validation Trick

Now, go to the cell where you want your Dropdown Menu.

1. Click Data > Data Validation.
2. Under "Allow", select List.
3. In the "Source" box, do not select the range manually (don't select D1:D10).
4. Instead, type: =$D$1#

That little hash symbol is the magic. It creates a dynamic link to the spill range.

Why This Changes Everything

Go back to your original Column A. Add a new item, like "Zebra."

1. Your UNIQUE formula in D1 automatically expands to include "Zebra."
2. Because your Data Validation points to $D$1# (the spill range), your dropdown automatically includes "Zebra".

No updating references. No clicking "Name Manager." It just works.

Bonus: Dependent Dropdowns (Cascading)

You can use this same logic for dependent lists (e.g., Select "Fruit" -> Show "Apple, Banana").

Use the =FILTER() function to create a spill range based on the first selection, and point your second dropdown to that new spill range with a hashtag. It is infinitely cleaner than the old INDIRECT method.

Conclusion

Dynamic Arrays are not just for calculation; they are for user interface design. By mastering the # operator, you build spreadsheets that maintain themselves.

Stop fixing broken dropdowns. Let the arrays do the work.

Download January Skills: Dynamic Dropdown Practice File