#SPILL! Error in UNIQUE: Complete Troubleshooting Guide
#SPILL!UNIQUE# Understanding the #SPILL! Error in UNIQUE Formulas Excel's UNIQUE function is powerful for extracting distinct values from a dataset, but it frequently triggers the #SPILL! error—a frustrating roadblock for many users. This error occurs when the formula cannot expand its results into the adjacent cells it needs, typically because existing data or formatting blocks the spill range. The good news: #SPILL! errors with UNIQUE are among the easiest to resolve. They're not formula logic problems; they're simply spatial conflicts within your worksheet. Whether it's hidden rows, merged cells, or existing content in the output area, the solution is straightforward once you identify the culprit. In this guide, we'll walk through the most common causes of #SPILL! with UNIQUE and show you exactly how to fix them, so you can confidently use this dynamic array formula without interruption.
Why UNIQUE causes #SPILL!
Insufficient space in spill range
The array returned by UNIQUE is larger than the available empty cells below and to the right of the formula cell. Excel cannot spill the results because adjacent cells contain data or formulas that block the spill area.
=UNIQUE(A1:A100) in cell C1, but cells C2:C50 already contain data. The spill range is blocked and #SPILL! appears.Invalid array reference or empty range
The array argument references an invalid range, contains errors, or is completely empty. UNIQUE cannot process malformed input data or extract unique values from a range with no valid data.
=UNIQUE(#REF!:A100) where the starting reference is broken, or =UNIQUE(A1:A1) on a single empty cell with no values to extract.Circular reference in spill area
The formula cell or its spill range overlaps with the source array, creating a circular reference. Excel detects that UNIQUE would need to reference its own output, which is not allowed.
=UNIQUE(A1:C100) placed in cell A1, causing the spill range to overlap with the source data range A1:C100.Step-by-Step Solution
- 1Click on the cell displaying the #SPILL! error to select it and view the formula in the formula bar
- 2Check the range immediately to the right and below the formula cell—identify if there are any merged cells, existing data, or formatting that would block the spill range
- 3Delete or move any content occupying the cells where UNIQUE results need to spill (clear the obstruction completely)
- 4Press Delete to clear the current error, then press Ctrl + Home and navigate back to your UNIQUE formula cell
- 5Verify your UNIQUE syntax is correct: =UNIQUE(range, [by_col], [exactly_once]) with no circular references or invalid range references
- 6If using UNIQUE with other functions like FILTER or SORT, ensure the input range is valid and doesn't reference the output range itself
- 7Press Ctrl + Shift + Enter if using an older Excel version, or simply press Enter in Microsoft 365 (which handles dynamic arrays automatically)
- 8Confirm the spill range is now clear and unobstructed by pressing Ctrl + Shift + End to check for hidden data in the worksheet
Concrete Example
Removing duplicate customer orders from a sales report
A sales manager uses UNIQUE to extract distinct customer names from a transaction list to create a mailing list. The data spans columns A through D with 500+ rows of orders.
Before (error)
=UNIQUE(A2:A501)After (fixed)
=UNIQUE(A2:A501)Problem: The #SPILL! error appears because there isn't enough empty space to the right of the formula cell to accommodate all unique results. The adjacent columns (B, C, D) contain existing data that blocks the spill range.
Solution: Move the UNIQUE formula to a column with sufficient empty space to its right, or delete/move the blocking data. Alternatively, use a helper column further to the right with no adjacent data.
Prevention Tip
Ensure the UNIQUE formula result range is completely empty before entering the formula, including hidden rows and columns. If cells below or to the right contain data, UNIQUE will throw #SPILL! because it has nowhere to expand the results.
Free Tools to Fix Your Formulas
Use these free tools to avoid this error:
Excel Formula Generator
Describe what you want to calculate and get the Excel formula instantly
VLOOKUP Generator
Generate VLOOKUP formulas instantly by describing what you need in plain English
Excel Formula Explainer
Paste any Excel formula and get a clear, step-by-step explanation powered by AI. Understand complex formulas instantly.