The Excel #SPILL! error appears in Microsoft 365 and Excel 2021 when a dynamic array formula tries to spill its results into multiple cells but something is blocking the spill range. Dynamic arrays are the modern Excel feature where a single formula in one cell produces results that automatically fill multiple adjacent cells. Functions like FILTER, SORT, UNIQUE, SEQUENCE, RANDARRAY, and several others all use dynamic arrays. When the spill range contains blocking content — text, numbers, formulas, or merged cells — Excel can't produce the spilled output, so it shows #SPILL! instead of the actual results you expected.

This guide walks through what dynamic array formulas are, what causes the SPILL error in each common scenario, how to find the cell that's blocking the spill range, the specific fixes for each cause, the differences between Excel versions (older Excel doesn't support dynamic arrays at all and so doesn't produce SPILL errors), the @ implicit intersection operator that lets you opt out of dynamic-array behavior in some cases, and the broader workflow for using dynamic arrays without running into SPILL errors regularly.

The simplest way to think about SPILL errors: if you write =FILTER(A1:A10, A1:A10>5) in cell C1 and the function would normally produce 5 results spilling into C1:C5, but C3 already contains text or a number, the spill is blocked. Excel doesn't overwrite existing content silently — it shows #SPILL! to alert you that something is wrong, and you have to either clear the blocking content or move the formula somewhere with empty cells available for the spill output.

Dynamic arrays were introduced to Microsoft 365 in 2020 and became part of Excel 2021. Older versions (Excel 2019, 2016, 2013, etc.) don't support dynamic arrays and don't produce SPILL errors because the underlying behavior doesn't exist. If you're seeing SPILL errors in a workbook you opened, you're using a current version of Excel. If a colleague using older Excel opens your dynamic-array workbook, they see static legacy results without the spill behavior, often producing different output than your current Excel session.

For Excel users new to dynamic arrays, the SPILL error is one of the most visible signs that something has changed since older Excel versions. The error message is informative if you know what it means but cryptic if you don't. The good news is that fixing SPILL errors is usually quick once you understand what's happening — clear the blocking cell, the spill output appears, and the formula works as intended. Most SPILL errors resolve in seconds rather than requiring deep debugging like some other formula error types do in everyday work.

What dynamic array formulas are

Dynamic arrays are formulas that automatically produce multiple results from a single formula entry. Instead of needing one formula per cell, you write one formula and Excel handles spreading the output across as many cells as the result requires. Common dynamic array functions include FILTER (returns rows matching criteria), SORT (sorts a range), UNIQUE (returns distinct values), SEQUENCE (generates sequential numbers), RANDARRAY (generates random numbers), and XLOOKUP (modern lookup that can return arrays). Older Excel functions like SUMIF and VLOOKUP can also produce array output in some patterns.

The formula lives in a single "anchor" cell. The output spreads — "spills" — into the surrounding cells. The spill range has a thin blue border to indicate the formula's output area. Click any spilled cell and the formula bar shows the formula in gray, indicating it's part of a spill but not the anchor. Click the anchor cell (top-left of the spill) and the formula bar shows the formula normally. Editing the formula always happens at the anchor; the spilled cells can't be edited individually because they're computed outputs of the anchor formula.

The spill range automatically resizes when the input data changes. If you add new rows to a source range that FILTER is reading, the spill range grows to include the new matching rows. If you remove rows, the spill range shrinks. This dynamic behavior is the key advantage of dynamic arrays over the older array formulas (Ctrl+Shift+Enter array formulas) where you had to pre-allocate fixed output ranges. Modern dynamic arrays are more flexible and easier to maintain over time as data evolves.

The spill behavior is what produces SPILL errors when blocked. If a dynamic array formula needs 5 rows of output but only 3 rows are empty before hitting an existing value, Excel can't produce the full spill. Rather than overwriting the blocking content silently, Excel shows #SPILL! at the anchor cell and refuses to spread the output. The behavior protects existing data from accidental overwriting while alerting you that the formula needs a different location or different input to work correctly in the current sheet layout.

How to find the blocking cell

Excel's UI provides a built-in way to identify what's blocking a spill. When you see a #SPILL! error, click the cell containing the error. A small green warning indicator appears at the corner of the cell with a dropdown arrow. Click the dropdown to see options including What's wrong with this formula? and Select Obstructing Cells. Click Select Obstructing Cells and Excel highlights the specific cells in the would-be spill range that are blocking the output.

The Select Obstructing Cells action lets you immediately see what content needs to move or be cleared for the spill to succeed. In most cases the blocker is something obvious — a leftover label, an old number, or formatting that included content. Clearing the highlighted cells (press Delete after selection) typically resolves the SPILL error immediately. The formula re-evaluates as soon as the blocking cells are clear, and the spill output appears with the data it should have produced originally.

If the dropdown isn't visible, sometimes Excel hides the warning indicator depending on display settings. The same information is available through the formula bar — when you select the SPILL cell, the formula bar shows the formula. You can also press F2 to enter edit mode on the formula and see the spill range it's attempting to produce. The spill range visualization (thin blue border around the intended output area) appears even when blocked, so you can see where the spill should go and identify the conflicting cells visually within that range.

For complex spreadsheets with many SPILL errors at once, working through them systematically helps. Start with the formula closest to your input data and work outward — sometimes one block cascades into others. Clearing one blocker may resolve multiple SPILL errors if they were chained together through interdependent formulas. Save the workbook periodically as you fix SPILL errors so you can recover if a fix produces unintended consequences elsewhere in the sheet during the cleanup process.

The @ implicit intersection operator

Modern Excel includes the @ implicit intersection operator that lets you tell Excel "return only a single value, not an array" for formulas that would otherwise spill. Adding @ before a function or range name forces the formula to behave like older Excel — returning a single cell value rather than spilling. This is sometimes the right answer when you want to use dynamic-array-aware functions but in a single-cell context where the spill behavior is unwanted.

The @ operator appears automatically when Excel converts older formulas to the new dynamic array engine. Files saved in Excel 2019 or earlier that contain implicit intersection formulas get @ added to those formulas when opened in Excel 365 or 2021 to preserve their original behavior. Most users don't manually add @ symbols; Excel handles the conversion automatically when opening older workbooks. Understanding what the @ does helps you read formulas in older files and modify them confidently when needed.

For new formulas you're writing in Microsoft 365, use @ when you specifically want single-value behavior from a function that could otherwise produce an array. Example: =@INDEX(A1:A10, B1) with @ explicitly forces single-cell output. The same formula without @ would behave the same way as long as B1 is a single value, but the @ makes the intent explicit and prevents the formula from spilling unexpectedly if you later modify the input to be a range that would normally produce array output.

The @ operator doesn't fix SPILL errors directly. If you have a formula that's producing SPILL, adding @ doesn't usually help because @ converts an array-producing formula into a single-value formula, which often isn't what you wanted in the first place. The proper fix for SPILL is usually to clear blocking cells or move the formula to empty space. Use @ when you're writing new formulas and want to opt out of dynamic-array behavior, not as a band-aid for SPILL errors caused by spill-range conflicts.

How to use dynamic arrays without producing SPILL

The cleanest way to avoid SPILL errors is to plan ahead for the spill range when writing dynamic array formulas. Anchor your formula in an empty area of the worksheet with enough surrounding empty cells to accommodate the maximum spill size. For functions like FILTER that return varying numbers of rows depending on the data, leave plenty of empty rows below the anchor. For functions like SORT that return the same number of rows as input, plan based on the input range size.

Another approach is to use dynamic arrays in conjunction with Excel Tables for the input data. If your input range is a Table column, the dynamic array formula reads the entire column dynamically — adding rows to the Table automatically expands what the formula sees. This pattern works well for data lists that grow over time. The spill output goes into a regular range adjacent to (or far from) the Table rather than inside the Table itself, which avoids the table-and-dynamic-array conflict that produces SPILL when both are mixed in the same area of the worksheet.

For dashboards or reports where you want predictable output sizes, consider using TAKE or DROP functions to constrain the spill output. =TAKE(SORT(A1:A100), 10) takes only the top 10 rows of the sorted result, regardless of how many input rows there are. This gives you a fixed-size output suitable for layout planning. Combined with FILTER, you can produce "top 10 highest-value records" or similar patterns with predictable output sizes that don't produce SPILL errors as input data fluctuates over time.

For workbooks that mix new dynamic array work with legacy formulas, keep them in separate ranges. Don't try to put dynamic array formulas next to traditional array formulas in the same area — the interactions are confusing and produce unpredictable behavior. Use modern dynamic arrays for new work in fresh ranges, leave legacy array formulas where they are if they still work, and consider whether the legacy formulas should be modernized to dynamic arrays as time permits during workbook maintenance cycles.

One useful workflow for working with dynamic arrays is to develop the formula in a temporary scratch area of the worksheet first, verify the output looks correct, then move it to its production location.

The scratch area approach lets you see what the spill range will look like and choose a production destination with enough empty space, avoiding SPILL errors that would otherwise pop up when you place the formula in a busier area of the workbook. The slight extra effort is worth it for complex dynamic array formulas where the output size or structure isn't immediately obvious from reading the formula.

SPILL vs other error types

Excel has multiple formula error types, and understanding which one you're seeing helps with debugging. #SPILL! specifically means a dynamic array formula was blocked from spilling. #REF! means a formula references a cell that no longer exists (deleted row/column). #VALUE! means a formula encountered an unexpected data type.

#NAME? means Excel doesn't recognize a function or named range (often the case when an Excel 365 function is opened in older Excel that doesn't have it). #DIV/0! means division by zero. #N/A means a lookup didn't find a match. #CALC! means a calculation isn't supported (often related to dynamic arrays or computed table interactions).

SPILL is unique to dynamic array formulas. If you're not using dynamic arrays, you won't see SPILL. If you're using a function that should produce a dynamic array (FILTER, SORT, UNIQUE, etc.) and seeing a different error type, the diagnosis is different. #NAME? on FILTER means you're in an older Excel version. #VALUE! on FILTER means an argument has the wrong type. #N/A on FILTER means no rows match the criteria (with the optional third argument controlling fallback behavior). The error type guides the diagnosis precisely if you know what each one signals.

For workflow troubleshooting, focus on SPILL errors first because they're the easiest to diagnose and fix once you understand the cause. The green warning indicator and Select Obstructing Cells feature give you exactly the information needed to resolve the issue. Other error types require more investigation into the formula's logic, the source data, or the Excel version. Most SPILL errors fix in seconds once identified; other error types sometimes take meaningful debugging time depending on the specific formula and data involved.

One subtlety: a SPILL error can mask other errors. If your dynamic array formula has a deeper problem (wrong arguments, missing data, etc.) but the formula is also being blocked from spilling, Excel might show SPILL as the visible error. Once you fix the SPILL by clearing blockers, a different error might appear if the formula has additional issues. The pattern is: fix the most visible error first, then re-evaluate the formula state and address any remaining issues that surface after the initial fix during normal worksheet operation.

Best practices for dynamic array workflows

The clearest practice is building dynamic array formulas in empty areas of the worksheet. Pick a region with no other content, anchor your formula there, and let the spill output materialize without conflicts. This avoids SPILL errors entirely and makes the formula's spill range easy to see and reason about. For dashboard and report layouts, design with the dynamic array output size in mind from the start rather than retrofitting formulas into busy regions.

For shared workbooks where multiple users add content over time, document which cells contain dynamic array formulas and which ranges should be left empty for spill output. A simple convention — "don't put anything in column F below the FILTER formula in F2" — prevents accidental blocking by users who don't realize the spill range needs to remain empty for the formula to function. Cell comments or sheet-level documentation help communicate this constraint to other users editing the workbook in the future.

For formulas where the output size could grow large (FILTER on a long source list, UNIQUE on a wide-input range), constrain the output explicitly with TAKE or DROP. =TAKE(FILTER(data, criteria), 50) caps the output at 50 rows regardless of how many rows would otherwise match. The constrained output makes layout planning predictable and prevents SPILL errors that would arise if the unconstrained output grew into adjacent occupied cells over time as data accumulated in the source.

For workbooks where dynamic arrays don't fit cleanly with the existing layout, consider whether Power Query or PivotTables would handle the same analysis more gracefully. Both alternatives produce structured output in dedicated areas without the spill conflicts of dynamic array formulas. Power Query is especially powerful for transformation work that dynamic arrays could also handle but with more layout flexibility. Pivot tables handle aggregation and summary work that dynamic arrays could approximate but typically less elegantly than the dedicated pivot tool designed for that purpose.