When a dynamic array formula outputs multiple values, it is said to “spill” these values onto the worksheet. For example, if I use the UNIQUE function on this list of colors, UNIQUE spills 3 values - red, blue, and green. The rectangle that encloses these values is called the “spill range”. Notice when I select any cell in a spill range, the rectangle will appear with special highlighting. The spill range for a given formula is dynamic, and may expand or contract as source data changes. For example, if I change a color in the list to purple, purple is added to the spill range. You can refer to a spill range in another formula by adding the hash(#) or pound(*) character to the first cell in the range. For example, in this case, I can refer to the spill range with “equals D5 and a hash character. You can use this reference any way you like. For example, I can use the COUNTA function to count the number of colors in the spill range. Like all dynamic array formulas, if the source data changes, the results will update. When you set up a formula that spills results onto the worksheet, you may see a #SPILL error when there isn’t enough space for all values. For example, if I add an “x” to the cell below the spill range, and then change red to purple again, we get a #SPILL error. However, if I delete the “x”, the error disappears and the formula spills properly again. This can also happen if I overwrite part of an existing spill range. Finally, notice that the formula inside a spill range is identical in all cells, and the text in the first cell is black, while text in other cells is gray. You’ll sometimes hear this behavior referred to as “one formula, many results”. This is a unique feature of dynamic array formulas and a huge simplification that makes it much easier to solve traditionally hard problems in Excel.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
