Normally an Excel function or formula is expected to return a single value into the cell where it is used. But all these new functions listed above are capable of returning an array of values instead of a single value and therefore they are called Dynamic Array Functions.
This particular behavior of returning multiple values is called ‘spilling‘ and results appear in a ‘spill range‘. If this spill range contains any data, the function will return a #SPILL! error.
Read more Dynamic Array Functions…
Watch the video on DYNAMIC ARRAY FUNCTIONS in Excel