#MORSEN! Fout in Excel-handleiding - Oorzaken en oplossingen

This error occurs when a formula fails to populate multiple cells with calculated results. But what exactly does #SPILL mean?

This article will delve into the causes behind the #SPILL! error and provide you with effective solutions. Whether it's insufficient cells selected or compatibility issues with the latest Excel updates, we'll help you identify the triggers.

By the end, you'll have the knowledge to resolve the #SPILL! error and get your formulas working flawlessly once again.

What does #SPILL mean in Excel?

In Excel, the #SPILL! error occurs when a formula generates multiple results but cannot display them all on the sheet. This error is associated with the dynamic array feature introduced in Excel 365, where formulas automatically spill their results into adjacent cells.

When a formula spills, it populates a range of cells called a spill range. If something prevents the spill range from being filled completely, the #SPILL! error occurs. While some cases of the error are straightforward to resolve by clearing adjacent data or adjusting the formula, others can be more complex.

Here are a few situations that can cause the #SPILL! error:

Non-printing characters: Invisible characters, such as spaces or non-printing characters, within the spill range can block the spill.
Blocked spill range: If a formula is copied across an entire column, it can block the spill range and result in the #SPILL! error.
Incompatible features: Some Excel features, such as array formulas, do not support dynamic arrays and can cause the #SPILL! error.

To fix the #SPILL! error, you need to investigate the specific cause in your spreadsheet. Check for hidden characters, adjust cell ranges, or review the formulas' compatibility. Resolving the error may require deleting unwanted data, adjusting the formula syntax, or finding alternative approaches that work with dynamic arrays.

How to fix #SPILL! error in Excel

To fix the #SPILL! error in Excel, follow these steps:

Spill range isn't blank

If you encounter a #SPILL! error in Excel because the spill range is not blank, it means that the area intended to be populated with formula results contains non-empty cells. Here's how to fix it:

Clear the spill range: Click on the formula cell to see the dashed border indicating the spill range boundaries. Any existing data within this range obstructs the spill. Clear the spill range by removing the data or moving the formula to a different location without obstructions.
Detect invisible characters or empty strings: In some cases, the spill range may appear blank, but it may contain invisible characters or formulas that return empty strings. Click on the warning sign next to the #SPILL! error to see the explanation. Underneath, you'll find options. Click "Select Obstructing Cells" to identify the cells preventing the spill.
Clear the obstructing cells: Excel will highlight the cells causing the obstruction. For example, if cell A6 contains an empty string ("") returned by a formula, it will be shown as the obstructing cell. To clear these blocking cells, go to the Home tab, find the Editing group, click "Clear," and choose "Clear All."

Spill range contains merged cells

If you encounter a #SPILL! error in Excel because the spill range contains merged cells, you need to unmerge the cells or move the formula to a location without merged cells. Here's how to fix it:

Unmerge cells in the spill range: Merging cells prevents the spill feature from working properly. To resolve the error, unmerge any merged cells within the spill range. Select the merged cells, right-click, choose "Format Cells," go to the Alignment tab, and click "Unmerge Cells."
Move the formula to a location without merged cells: If unmerging the cells is not feasible or desired, you can move the formula to a different location where there are no merged cells. Ensure that the spill range is free of any merged cells so the formula can spill its results correctly.

If you have difficulty visually identifying the merged cells, use the "Select Obstructing Cells" option. Click on the warning sign next to the #SPILL! error, and Excel will highlight the cells causing the obstruction, making locating and addressing the merged cells easier.

Spill range in table

If you encounter a #SPILL! error in Excel because the spill range is within a table, it means that dynamic arrays are not supported within Excel tables. To fix this issue, you have two options:

Convert the table to a normal range: Right-click anywhere within the table and select "Table" from the context menu. Then, click "Convert to Range." This will convert the table into a normal range, allowing the formula to spill its results correctly.
Place the formula outside the table: If you prefer to keep the table structure intact, you can move the formula outside the table boundaries. By placing the formula in a cell outside the table, you can ensure that the spill range is not within the table, allowing the formula to work without the #SPILL! error.

When you click on the warning icon with an exclamation mark, you will see the message "Spill range in table" confirming that the table is causing the issue.

Spill range is unknown

If you encounter a #SPILL! error in Excel because the spill range is unknown, it means that Excel is unable to determine the size of the spilt array. This situation often arises when using volatile functions such as RANDARRAY, RAND, or RANDBETWEEN in combination with dynamic array functions.

The error message you will see is "Spill range is unknown." It occurs because the output of a volatile function, which constantly changes, is used as input for a dynamic array function that needs to determine the size of the array.

To resolve this issue, you may need to consider an alternative approach or formula for your task. It's important to understand that the size of the spilled array cannot be determined due to the volatile nature of the function involved.

For example, if you have a formula like =SEQUENCE(RANDBETWEEN(1,100)), it will result in a #SPILL! error because the RANDBETWEEN function generates a different value with each recalculation, and the SEQUENCE function cannot determine how many values to generate.

Spill range is too big

If you encounter a #SPILL! error in Excel because the spill range is too big, it means that the calculated spilt array extends beyond the edges of the spreadsheet. To resolve this issue, you can adjust your formulas using one of the following approaches:

Reference a used range: Instead of referencing an entire column, modify your formula to reference a specific range that includes the actual data. For example, if your data is in cells B2 to B100, use the range B2:B100 in your formula instead of referencing the entire column.
Reference a single cell: If you only need to perform the calculation for a single cell, specify that particular cell in your formula instead of referencing a range. This helps limit the size of the spill range.
Use the "@" operator for implicit intersection: In Excel 365, you can use the "@" operator to perform implicit intersection. This means that the formula will only consider the intersecting cells of the spill range and the range it references, resulting in a smaller spill range. For example, instead of =B:B*10%, you can use =B1@*10% to perform the calculation for each individual cell.

Fixing #SPILL! Error in Excel: Ranges, Single Cell, and Implicit Intersection

To resolve the #SPILL! error in Excel when the spill range is too big, there are several solutions you can apply depending on your specific needs:

Refer to ranges rather than entire columns: Instead of referencing an entire column in your formula, specify the specific range that contains your data. Referencing only the relevant data range can avoid the spill range extending beyond the spreadsheet edges. For example, if your data is in cells B2 to B10, use the range B2:B10 in your formula.
Calculate a single cell and copy the formula down: This traditional approach involves calculating a single cell and then copying the formula to other cells in the column. By calculating one cell at a time, you can limit the size of the spill range. For instance, you can calculate the formula =B2*10% in cell C2 and then copy it to cells C3 to C10.
Apply implicit intersection with the "@" operator: In Excel 365, you can use the "@" symbol as an implicit intersection operator. This allows you to reduce an array or a range to a single value. Insert the "@" character in your formula before the range/column/row reference, function, or expression. For example, you can use the formula =@B:B*10% to perform the calculation with the implicit intersection.

FAQs

How do I get rid of the error warning in Excel?

To get rid of the error warning in Excel, you can either correct the error in the formula or use error handling functions like IFERROR or ISERROR to handle the errors gracefully.

What are the errors in Microsoft Excel?

Errors in Microsoft Excel refer to issues that occur when a formula or function cannot execute properly, resulting in incorrect or unexpected results.

What causes Excel errors?

Excel errors can be caused by various factors, including incorrect formula syntax, referencing errors, dividing by zero, invalid data types, or compatibility issues with other functions or add-ins.

How do you solve problems in Excel?

To solve problems in Excel, you can use troubleshooting techniques such as reviewing the formula syntax, verifying cell references, checking data inputs, using Excel's built-in error checking tools, or seeking help from online resources or communities.

How do I fix Excel encountered errors during save?

If you encounter errors during saving in Excel, you can try the following solutions: check for sufficient disk space, ensure the file is not read-only or locked, disable any conflicting add-ins, repair or reinstall Microsoft Office, or save the file in a different format (e.g., XLSX instead of XLS).

Final Thoughts

The #SPILL! error in Microsoft Excel occurs when a formula is unable to populate multiple cells with calculated results. This error can be caused by various reasons such as non-empty cells in the spill range, merged cells, unsupported dynamic arrays in tables, unknown spill ranges, or spill ranges that are too big.

To correct the #SPILL! error, you can clear the spill range, unmerge cells, convert tables to ranges, use alternative formulas or references, or apply implicit intersection techniques. Understanding the cause of the error and applying the appropriate solution will help you resolve the #SPILL! error and ensure accurate calculations in Excel.

One more thing

If you have a second, please share this article on your socials; someone else may benefit too.

Subscribe to our newsletter and be the first to read our future articles, reviews, and blog post right in your email inbox. We also offer deals, promotions, and updates on our products and share them via email. You won’t miss one.

» Resolving the 0x80246007 Windows Update Error: A Step-by-Step Guide
» How to Fix "Device Descriptor Request Failed" Error on Windows
» Fix "There isn't enough memory to complete this action Excel" Error

Find anything you need

#MORSEN! Fout in Excel - Oorzaken en oplossingen

Table of Contents