Why and how do formulas spill?

I used this post by Meganathan Elumalai to explore the transition from traditional formulas to spill formulas (formula results spill into multiple cells).

Read more: Why and how do formulas spill?

Meganathan’s requirements are clear:

Meganathan used a table in column B for the source items. In this post I’ll use a non table range (my Excel file includes both).

Will it spill v2Download Excel File

Formula1:

Cell D5 formula, in the picture above, is also listed below:

=IF(ISERR(SEARCH(D$4,$B5)),"",D$4)

Will it spill? No. This formula won’t spill into other cells.

Why doesn’t it spill?

SEARCH function’s default use is to look for a single cell value within another single cell like we have here: SEARCH(D$4,$B5)

Cell D5 Formula Description

Look for cell D4 search word (Apple) within cell B5. If doing this causes an error (Apple isn’t within cell B5), then display a blank (“”). If it is found then display cell D4 search word (Apple).

This is the traditional method to solve this. The formula is then dragged down and right as needed. There’s nothing wrong with this but now we have more options: a formula can spill answers into other cells without being dragged or copied!

Before looking at Formula2, I’ll explain two things in my Excel file:

Conditional Formatting

Cells with formulas are highlighted in green (see picture below) if the check box is checked:

  • Formula1 in cell D5 was dragged down to row 10 and across to column I so all cells are green
  • Formula2 in cell D17 automatically spills down to row 22! I dragged cell D17 formula to the right to cell I17. Each formula spills down to row 22 (without being dragged down!)

Search Words (fruit)

  • In range D4 to I4, search words (fruit) are typed in
  • In cell D16, an array constant formula spills fruit names to the right until cell I16. The only formula in range D16 to I16 is in cell D16

Formula2:

Cell D17 formula, in the picture above, is also listed below:

=IF(ISERR(SEARCH(D$16,$B$5:$B$10)),"",D$16)

Will it spill? Yes. Row 17 formulas spill down, not right (cell D17 formula is dragged right to cell I17).

How does it spill down?

As we saw earlier, SEARCH is typically used to look for a single cell value inside of another single cell (exception: array formulas) like this: SEARCH(D$4,$B5)

But, Formula2 SEARCH(D$16,$B$5:$B$10) searches for Apple in a range of cells: $B$5 to $B$10 creating multiple answers. Cell D17 formula spills down to cell B22 as range $B$5:$B$10 has 6 cells.

  • D17 formula: cell D16 search word Apple is in cell B5 so display Apple
  • D17 formula spills to cell D18: cell D16 search word Apple isn’t in cell B6 so blank
  • D17 formula spills to cell D19: cell D16 search word Apple is in cell B7 so display Apple
  • D17 formula spills to cell D20: cell D16 search word Apple isn’t in cell B8 so blank
  • D17 formula spills to cell D21: cell D16 search word Apple is in cell B9 so display Apple
  • D17 formula spills to cell D22: cell D16 search word Apple isn’t in cell B10 so blank

Cell D17 formula is then dragged right until cell I17.

Remember: only cells with green background have a formula. Other cells receive spill formula results or have typed in text values.

Formula3:

The formula in cell D29 is listed below:

=IF(ISERR(SEARCH(D28#,$B$5:$B$10)),"",D28#)

Will it spill? Yes! Cell D29 formula spills down AND to the right!

How does it spill down AND right?

Three factors allow cell D29 formula to spill down AND right:

  1. cell D28 has an array constant formula that spills search words right to cell I28
  2. cell D29 has # after both D28 cell references (D28#) telling it to spill right as wide as the spill range of the array constant above
  3. cell D29 SEARCH function has a multi cell within_text argument range ($B$5:$B$10) causing it to spill down to row 34

Let’s validate this by changing cell D29 formula.

Remove the ability to spill right by changing D29 formula to this:

=IF(ISERR(SEARCH(D28,$B$5:$B$10)),"",D28)

    Remove the ability to spill down by changing D29 formula to this:

    =IF(ISERR(SEARCH(D28#,$B5)),"",D28#)

    Formula4:

    The formula in cell N4 is listed below:

    =LET(
header,{"Apple","Banana","Grape","Kiwi","Orange","Pear"},
formula,IF(ISERR(SEARCH(header,$B$5:$B$10)),"",header),
VSTACK(header,formula))

    Will it spill? Yes! Cell N4 formula spills search words and then spills answers down AND to the right.

    How does it spill search words AND formula results?

    • LET function lets us define a name for a calculation (or array constant)
    • the name header has this formula: {“Apple”,”Banana”,”Grape”,”Kiwi”,”Orange”,”Pear”}
    • the name formula has this formula: IF(ISERR(SEARCH(header,$B$5:$B$10)),””,header)
    • VSTACK function vertically stacks these two spill pieces together

    Formula5:

    The formula in cell N16 is listed below:

    =LET(
header,Fruit,
formula,IF(ISERR(SEARCH(header,$B$5:$B$10)),"",header),
VSTACK(header,formula))

    Will it spill? Yes! Cell N16 formula spills just like Formula4 except that name header refers to a named range called Fruit that has an array constant.

    Formula6:

    The formula in cell N29 is listed below:

    =DROP(REDUCE(0,B5:B10,LAMBDA(a,i,VSTACK(a,MAP(D4:I4,LAMBDA(x,IF(ISNUMBER(FIND(x,i)),x,"")))))),1)

    Will it spill?

    Yes…but I can’t explain this formula yet. It was created by Excel MVP Rick Rothstein. It combines a lot of dynamic array functions so I definitely want to study it!

    About Me

    My name is Kevin Lehrbass. Iā€™m a data analyst.

    Formulas that spill results into other cells are very powerful and have changed the way we build Excel models. They require special attention as they can also cause issues if not designed and tested carefully.

    Blendtec company founder Tom Dickson experiments to see what his blender can chop up. Microsoft Excel formulas are like that too…we have to study and experiment to see what they can do!