Excel # Spill Operator Examples
Let’s say I want to create dependent data validation lists for the Category and Products like this:
The data validation list requires a reference to cells, or a formula that returns a cell reference (remember dynamic array formulas return an array):
Therefore, I can’t use a dynamic array formula directly inside the data validation dialog box to return the list. Instead, I need a table containing the
different products in lists by category. I can use TRANSPOSE with UNIQUE for the column headers containing the categories, and FILTER for the product lists:
Note: the filter formula in cell G18 is copied to columns H:N to allow for growth in the number of categories.
Now that I have the table for each category and its products, I can reference this in my data validation lists.
Referencing Spilled Arrays in Data Validation
Because the table above contains spilled arrays, I can use the # operator to reference them ensuring I always pick up any changes to the table.
Category Data Validation List: To set up the data validation list for the Categories I can reference cell G17# in
the data validation list dialog box:
If
I add any more categories to the table, the data validation list will automatically include them.
I’ve inserted the data validation list in cell C17:
Now I need the dependent data validation list for the Products.
Referencing Spilled Arrays with Dynamic Named Ranges
Product Data Validation List: The dependent data
validation list needs to lookup the category selected in cell C17 and return the relevant list of products. I’ll use the XLOOKUP function to return the reference to the product list.
The trick here is to reference the first row of the FILTER results in row 18 and append the spill operator to the end of the formula:
=XLOOKUP(C17,$G$17:$N$17,$G$18:$N$18)#
I can use this formula in a cell, and it spills the results:
Or, because XLOOKUP returns a reference, I can alternatively use this formula in my data validation list source:
This returns the products that spill from the FILTER formulas in row 18.
Note: You might be wondering why in the XLOOKUP formula I haven’t referenced the spilled array
in G17 like this:
=XLOOKUP(C17,$G$17#,$G$18:$N$18)#
And that’s because if the size of the list of categories changes the return array will be the wrong size and the formula will return an
error.
Instead, I’m referencing G17:N17 which also allows me to lookup out to column N allowing for growth in the list of categories. Obviously, you can extend this further than column N to allow for even more growth.
Cool Trick with Defined Names
Another way we can use the # spill operator is by appending it to
a defined name. For example, we can define a name for the Products using an XLOOKUP formula with or without the #. I’ll define it without the #:
If we look at this name in a formula you can see it returns the first item in the spilled array:
But when we append the spill operator to the name, we get the spilled array:
Therefore, in the data validation list we can also access the spilled arrays by appending # to the defined name:
The purpose of this tutorial is to illustrate the different ways we can use the spill operator. There’s no benefits either way,
it’s really what you’re most comfortable working with.
More Dynamic Arrays
If you'd like to get up to speed with dynamic arrays, please consider my Advanced Formulas Course.
Learn With Us
