The #SPILL! errors
As you might expect, new functionality in Excel such as dynamic array functions can result in new types of error prompts, too. In The Filter function section, I discussed the #CALC! error. In this section, I’ll discuss the #SPILL! error. The first error that we’ll look at arises when other data resides in the immediate area that a dynamic array function needs to display its results. This error can also arise when a user overwrites data that has been returned by a dynamic array function.
The #SPILL! error can appear under two different conditions:
- Cell G7 in Figure 10.24 contains the words
Obstruction blocking UNIQUE. If you subsequently enter the=UNIQUE(C3:C12)formula into cell G3, Excel will return#SPILL!instead of the list of countries you’re expecting. That’s because the contents of cell G7 fall within the range thatUNIQUEneeds to display its results. As shown, when you click on a cell that contains#SPILL!, such as...
