In Excel, SPILL is an error, meaning that a formula tries to fill more than one cell with its result. It usually happens using new functions like FILTER, UNIQUE, or SEQUENCE. When you type one of these formulas into a cell, it can produce an array (a list of values) that spills ove...
#SPILL! error in Excel is most commonly experienced while using a dynamic array function. A formula that cannot fill the required cells with the calculated results creates this error. After the introduction of dynamic arrays, the formulas can often return multiple values. In such cases, the resu...
The UNIQUE function checks a list of items and returns the unique values of that list. This can be an array of values (just like B2:B4 in the image above). Here Cell B2 to B4 make our spill range. If this spill range is not vacant to populate the results of the UNIQUE function, ...
Suppose, we use theUNIQUEfunction in cellF5, to get the unique values from cell rangeD5:D9and get the error because theSpill range has merged cell. We type the formula at the cell. The formula we are using is: =UNIQUE(D5:D9) Let’s see the procedure to fix the#SPILL!error: From...
=UNIQUE(B5:B10) #Spill Error in Excel Now a user will notice a#spill errorin the formula cell (here, D5) because the cell D7 of the spill range is already filled with the value X. Then deleteXfrom theD7and the formula cell will be clear of the #spill error. ...
In cellD4, enter the followingformula: =UNIQUE(B4:B12) PressENTER, and theUNIQUEfunction will return the list of unique fruit names from the specified range (B4:B12). Example 2 – Extracting Unique Numeric Values If you’re working withnumeric values, such as orderIDs, you can still use...
Error in Excel In Excel, Dynamic Array Formulas likeFILTER,UNIQUE, and SORT can return a number of values (array) at once. These results are spilled into the adjacent cells. But, when the formula cannot return all values, you’ll get #SPILL! Error. ...
The results would cause a #SPILL! error because Excel will lookup the entire column, return 1,048,576 results, and hit the end of the Excel grid. So close; we only needed one more cell. It’s generally considered poor practice to select entire columns when selecting ranges. Table Formulas...
Like other dynamic array functions, UNIQUE can only be used within a normalrange, not a table. When put withinExcel tables, it returns a #SPILL! error. How to find unique values in Excel - formula examples The below examples show some practical uses of the UNIQUE function in Excel. The ...
The ERROR.TYPE function was introduced in Excel 2003. Let's move onto its syntax. Table of Contents Syntax Important Characteristics of ERROR.TYPE Function Examples of ERROR.TYPE Function Example 1 – Simple ERROR.TYPE function Example 2 – ERROR.TYPE function with IF function Example 3 – ERRO...