The #SPILL! error occurs in an official Excel Table (created via Ctrl + T or Insert > Table) when a formula attempts to return multiple values. In modern versions of Excel, functions like FILTER, UNIQUE, SORT, and SEQUENCE output dynamic arrays that spill across neighboring rows and columns. However, Excel’s structured Table architecture does not support dynamic arrays. If a formula in an Excel Table returns more than a single value, the application halts calculation and displays #SPILL!.
Quick Fix
Move the dynamic array formula to an empty range outside the Excel Table. If the formula must remain inside the Table, force it to return a single value using the implicit intersection operator (
@), or wrap the array in an aggregating function likeTEXTJOIN(),SUM(), orINDEX().
The Architectural Conflict Between Tables and Arrays
To understand why this error occurs, consider the competing design principles behind Tables and Dynamic Arrays:
┌─────────────────────────────────────────────────────────────┐
│ EXCEL TABLE │
│ • Every row represents one distinct, self-contained record │
│ • Formula in one cell applies to the entire column │
│ • Columns cannot vary in length │
└──────────────────────────────┬──────────────────────────────┘
│
CANNOT RECONCILE WITH
│
┌──────────────────────────────▼──────────────────────────────┐
│ DYNAMIC ARRAY │
│ • Single formula outputs variable rows and columns │
│ • Output size expands or contracts dynamically │
│ • Requires empty adjacent cells to "spill" into │
└─────────────────────────────────────────────────────────────┘
When you enter a formula into an Excel Table column, Excel applies that formula across every row using its Calculated Column feature, which is prone to reference mistakes documented in #N/A in Excel Tables ([@Column] reference errors).
If a dynamic array function were permitted inside Row 2 of a Table and tried to spill down 10 rows:
- It would overwrite the formulas in Rows 3 through 12.
- The Table would need to expand vertically to accommodate the spill, creating new rows that run the formula again, triggering an infinite calculation loop.
To protect data integrity, Microsoft disabled dynamic array spilling inside structured Tables entirely.
Diagnostic Scenarios Inside Tables
Scenario 1: Using Array Functions (FILTER, UNIQUE, SORT)
Entering any native dynamic array function inside a Table triggers an immediate #SPILL! error:
=[@Department] = FILTER(DeptList, DeptList="HR") <-- Returns #SPILL!
Excel recognizes that FILTER returns an array of matching values. Because the Table cell cannot expand to display multiple rows, the entire calculated column displays #SPILL!.
The Solution:
Place the dynamic array formula in standard worksheet cells outside the Table, and reference the Table using structured references:
=FILTER(EmployeesTable[Name], EmployeesTable[Department] = "Sales")
Scenario 2: Whole-Column References Without the “@” Operator
In traditional Excel ranges, referencing an entire column like A:A often relied on implicit intersection—Excel automatically grabbed the single value located on the current row.
In modern Excel, referencing an entire column inside a Table tells the formula to return every value in that column simultaneously:
=EmployeesTable[Salary] * 1.10 <-- Inside a Table, returns #SPILL!
Because EmployeesTable[Salary] represents an array of all salaries in the Table, the formula attempts to output the entire list into every individual row, producing #SPILL!.
The Solution:
Use structured reference notation to specify the current row only using the @ symbol:
=[@Salary] * 1.10
The [@Salary] notation instructs Excel to pull strictly the salary from the active record, returning a single value that fits comfortably inside the Table cell, resolving the conflict explained in #SPILL! vs. The Implicit Intersection Operator (@).
Scenario 3: Accidental Multi-Cell Lookups
If you write a lookup formula that returns multiple cells, such as:
=XLOOKUP([@ID], MasterTable[ID], MasterTable[First_Name]:MasterTable[Last_Name])
Because the return array covers two columns (First_Name and Last_Name), the formula attempts to spill horizontally across the adjacent Table column. Tables do not permit horizontal spilling, resulting in #SPILL!.
The Solution:
Extract only one column at a time:
=XLOOKUP([@ID], MasterTable[ID], MasterTable[First_Name])
Then create a separate column for Last_Name.
Three Workarounds for Table Workflows
Workaround 1: Aggregate the Output into a Single String
If you need to return multiple matching records inside a single Table row, aggregate the output into one cell using TEXTJOIN:
=TEXTJOIN(", ", TRUE, FILTER(SkillsTable[Skill], SkillsTable[EmpID] = [@EmpID], "None"))
Why this works: FILTER generates an array of skills, but TEXTJOIN concatenates that array into a single text string (e.g., "Excel, SQL, Python"). Because the output is a single value, it complies with Table design rules.
Workaround 2: Extract Specific Array Elements via INDEX
If you want to fetch only the first, second, or third item from an array query:
=INDEX(FILTER(ProjectsTable[ProjectName], ProjectsTable[Lead] = [@LeadName]), 1)
Wrapping the array inside INDEX(..., 1) extracts only the first matching item, preventing the formula from spilling.
Workaround 3: Convert the Table to a Normal Range
If your model requires dynamic array calculations throughout the dataset and you do not need formal Table features (such as automated alternating row colors, total rows, or SharePoint syncing):
- Click any cell inside the Excel Table.
- Go to the Table Design tab on the Ribbon.
- In the Tools group, click Convert to Range.
- Confirm with Yes.
The data retains its visual formatting, but the underlying structure reverts to a standard grid range. Dynamic array formulas will now spill across rows and columns normally, provided adjacent cells are empty, as shown in #SPILL! Error: Non-Empty cells in the spill range (The “Ghost” character).
To review the full diagnostic spectrum of dynamic array behaviors and spill-blockage fixes, read Troubleshooting #SPILL! and #CALC! Errors: The Dynamic Array Diagnostic Guide.