HOW TO CORRECT THE #SPILL ERROR IN EXCEL

Today, we will cover a common question I get: what is the #SPILL? Error, and how can I get rid of it? Sometimes it's easy to find a reason, but sometimes, it's not so obvious. So let us show you here the Spill Excel error and how to fix it—also referred to as a #spill or hash Spill Excel error.

This Excel error occurs if you reference a range too extensive for the workbook (such as referencing entire Excel columns) or when the Spill Excel range is not empty. You may also see this error if you use one of the new Excel functions, such as the FILTER function, UNIQUE functions, or SORT Excel. This article will help you understand all the causes of #SPILL Excel errors and the solutions to fix them in Excel. #Leak! A new type of Spill Excel error mainly occurs when a formula that produces multiple computational results tries to display its output in a spill range. Still, that range already contains some other data. The block data can be anything, including text value, merged cell, a regular space character, or there isn't enough room to return the results. The solution of Spill Excel is simple: clear the range of any blocking data or select an empty set of cells that do not have any data. A spill error usually occurs when calculating dynamic array formulas because active array formulas produce results in multiple cells or an array. Let's look in more detail and understand why this error occurs in Spill Excel and how to solve it.

What causes a spill error?

Since the launch of dynamic matrices in 2018, Excel formulas can handle multiple values ​​simultaneously and return results in more than one cell. Dynamic arrays are scalable arrays that allow procedures to return various effects to a range of cells in a worksheet based on a formula entered into a single cell. When a dynamic array formula returns multiple results, those results are automatically spread to neighboring cells. This behavior is called "Spill" in Excel. The range of cells in which the effects spill out is called the "spill range." The Spill's scope will automatically expand or contract based on the source values. If the formula tries to populate a spill range with multiple results but is blocked by something in that scope, a #SPILL error will occur. Excel now has nine functions that use the dynamic arrays function to solve problems. These include:

• sequence
• purifier
• transposition
• Sort
• sort by
• Randari
• Unique
• XLOOKUP
• XMATCH

Dynamic array formulas are only available in Excel 365 and are not currently supported by offline Excel programs (e.g., Microsoft Excel 2016, Microsoft Excel 2019). Spill errors are not only caused by data obstruction. There are many reasons why you can get the #Spill errors. Let's explore the different situations you might encounter #SPILL! Error and how to fix it.

The spill range isn't blank.

One of the leading causes of spill errors is that the spill scope is not empty. For example, if you are trying to return ten results, but if the file includes any cells with data in the spill area, the formula returns the #SPILL! Error. #spill range isn't blank. You can see #SPILL! error - Spill range isn't blank Example 1:   And when you click on the formula cell, you will see a dashed blue border indicating the spill area/range (C2:F2) required to display the results below. You will also notice a yellow warning sign with an exclamation point. # non-empty cells   To understand the cause of the error, click on the warning icon next to the error and see the message in the first line highlighted in gray. As you can see, it says "spill scope is not empty" here. The problem is that the cells in the spill range D2 and E2 contain text characters (not empty), hence the error. How to fix this: The solution is simple: wipe (move or delete) the data within the Spill or move the formula to another location with no obstruction. Once you delete or move the block, Excel will automatically fill the cells with the formula results. When we scan the text in D2 and E2, the formula moves the column to row as intended.   Example 2: Even though the spill scope appears empty in the example below, the formula returns the Spill! Spillr. The Spill Spillt is open and has an invisible space character in one of the cells.   It's hard to locate space characters or any other invisible character hiding in what appear to be empty cells. To find those cells containing unwanted data, click on the error float (warning sign) and select "Select obstructed cells" from the list, and it will take you to the cell containing the blocked data.   As you can see in the screenshot below, cell E2 contains two spaces. Therefore, you will get the appropriate output when you clear this data.   Sometimes the invisible character might be text formatted with the same font color as the cell fill color or a cell value formatted with the number symbol ;;;. Customizing a cell value format will hide anything in that cell, regardless of font color or color.  

The spill range contains embedded cells.

Sometimes, #SPILL range! The error occurs when the spill range contains merged cells. The dynamic array formula does not work with merged cells. To fix this, you must unmerge the cells in the spill range or move the procedure to another field that doesn't contain merged cells. Even though the spill scope is empty (C2:CC8), the formula returns a Spill range error. This is due to the merging of cells C4 and C5. #spill range contains merged cell   To ensure that the merged cells are the reason you are getting the error, click on the warning flag and check the reason - "Spill range has a merged cell."   How to fix this: To unmerge cells, select the merged cells, then on the Home tab, click the Merge & Center button and choose Unmerge Cells. #Select obstructing cells   If you have difficulty locating the merged cells in your large spreadsheet, click the "Select obstructing cells" option from the warning flag menu to go to the merged cells. #Select obstructing cells option  

Spill range in Excel Tables

Spilled array formulas are not supported in Excel tables. The dynamic array formula must be entered into only one single cell. You will face a spill error if you enter the recipe for a spilled array in a table or when the spill area falls into a table. When this happens, convert the table Tablenormal range or move the formula outside the table. Table example, entering the following spilled range formula into an Excel table will get a Spill error in every Excel table cell, not just the formula cell. That's because Excel automatically copies any formula entered in a table to every cell in the table'Table'sn.   Also, you will get a spill error when the formula tries to spill the results into a table. For example, the spill area is inside the current table screenshot below, so we got a spill error.   To confirm this error, click on the warning sign and see the reason for the error - "Spill range in the table."   How to fix this: You must return the Excel table to the range to fix the error. To do this, right-click anywhere within the table, Tablek Table, Tablethen select the Convert to Range option. Alternatively, you can left-click anywhere within the table, Tableo the Table Design tab, and choose the Convert to Range option.  

The spill range is unknown.

The spill range is unknown. If Excel cannot determine the size of the spilled array, it will cause a Spill error. For example, sometimes, the formula allows a dynamic collection Excel to change its size between each arithmetic pass. If the size of the dynamic array Excel keeps changing during the calculations and doesn't balance, it will throw a #SPILL! Error. This type of spill error is usually triggered using functions such as the RAND, RANDARRAY, RANDBETWEEN, OFFSET, and INDIRECT parts. For example, when we use the following formula in cell B3, we get a Spill error: = SEQUENCE (RAND BETWEEN (1 ، 500)) In the example, the RANDBETWEEN function returns a random integer between 1 and 500, and its output constantly changes. The SEQUENCE function does not know how multiple values to produce in a dynamic spill array. Hence, the error #SPILL. #dynamic array formula #excel formulas #dynamic array functions   You can also confirm the cause of the error by clicking on the warning sign - "Spill scope unknown."   How to fix this: To fix the error in this formula, your only option is to use a different procedure for your calculation.

The spill range is too extensive.

If the spill range is too extensive, sometimes you can implement a formula that outputs clogged content that is too large for the worksheet to handle and may extend beyond the edges of the worksheet. When that happens, you may get a #SPILL! Error. To fix this, refer to a specific range or a single cell instead of entire columns or use the '@' character to enable the implicit intersection. # full range is too big In the example below, we're trying to calculate 20% of the sales numbers in column A and return results in column B, but instead, we get a Spill error.   Over a million results (1,048,576) spill Spillcolumn B, starting with cell B3 but reaching the end of the Excel spreadsheet. There is insufficient space to display all the output ranges, so we get a #SPILL error. As you can see, this error is - "Spill range is too big."   How to fix this: To solve this, try changing the entire column with a related range or cell reference or add the @ operator to make an implicit intersection. Fix spill 1: You can refer to domains instead of entire columns. Here, we change the whole dynamic range A: A with A3:A11 in the formula, and the procedure will automatically populate the dynamic ranges with the results.   Fix spill 2: Replace the entire column with only the cell reference in the same row (A3), then copy the formula to the bottom of the range using the fill handle.   Fix 3: Add the @ operator before the reference to make an implicit intersection. However, this will only display the output in the formula cell.   Then copy the formula from cell B3 to the rest of the range.   Note: You can only edit the first cell in the spill area/range when you edit a spilled formula. You can see the procedure in other cells in the entire spill range, but it will be grayed out and cannot be updated.

Out of Memory

Executing a nested spilled array formula that causes Excel to run out of memory can trigger the #SPILL error in Excel. Under these circumstances, try to refer to a smaller matrix or scale.

Unrecognized / Fallback

You can also get a Spill error in Excel even when Excel does not recognize the cause of the error or cannot reconcile them. In such cases, double-check the formula and ensure all parameters of the functions are correct. Now, you know all the #SPILL causes and solutions! Error in Excel 365. You can use the ready-to-use templates from Simple Sheets.