If any value of the include argument is an error (#N/A, #VALUE, etc.) or cannot be converted to a Boolean, the FILTER function will return an error.Įxcel has limited support for dynamic arrays between workbooks, and this scenario is only supported when both workbooks are open. Otherwise, a #CALC! error will result, as Excel does not currently support empty arrays. If your dataset has the potential of returning an empty value, then use the 3rd argument ( ). For more details, see this article on spilled array behavior. If your supporting data is in an Excel table, then the array will automatically resize as you add or remove data from your array range if you're using structured references. This means that Excel will dynamically create the appropriate sized array range when you press ENTER. The FILTER function will return an array, which will spill if it's the final result of a formula. In the example above, the source array for our FILTER formula is range A5:D20. An array can be thought of as a row of values, a column of values, or a combination of rows and columns of values.
