Function Problems

How to Fix Spill Error When Using UNIQUE Function in Excel Tables

Maira Mehtab Sep 22, 2026 868 views

Question details

The user encounters a #SPILL! error when trying to use dynamic-array functions like UNIQUE inside a formatted Excel Table, expecting the table to automatically resize to fit the results.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Attempting to extract a unique list of values by entering the UNIQUE formula directly into a column of a structural Excel Table.
Observed behavior: Excel returns a #SPILL! error because dynamic-array formulas that return multiple results are not permitted to expand or spill inside an Excel Table.

Before you start

Ensure you have identified a completely empty standard worksheet range outside of any formatted tables to accommodate your extracted data.

Solution 1Recommended

Move the Dynamic-Array Formula Outside the Table

Since dynamic arrays cannot spill inside structured tables, the standard solution is to place the formula in a regular cell range and let the results spill dynamically.

Dynamic-array formulas, such as UNIQUE, SORT, or FILTER, are designed to spill results into adjacent empty cells. However, Microsoft Excel currently does not support this spilling behavior inside structural Tables (created via Insert > Table). If you attempt this, Excel will block the expansion and return a #SPILL! error. To resolve this, you must output the results into a normal worksheet range.

Select an empty range

Click on a cell in a normal worksheet range that is strictly outside the boundaries of any formatted Excel Table.

Clear surrounding cells

Ensure there are enough empty cells below and to the right of your chosen starting cell so the dynamic array has room to expand and contract without obstruction.

Enter the UNIQUE formula

Type your formula referencing the table data (for example, =UNIQUE(Table1[Column1])) and press Enter. The results will automatically spill down the standard range.

Converting Table to Range: If you absolutely need the formula output in the exact location where your table currently is, you must convert the table back to normal cells. Right-click anywhere in the table, select 'Table', and click 'Convert to Range' before applying the UNIQUE function.

Free Microsoft Office alternative

Use WPS Office for Your Spreadsheet and Data Needs

While structural limitations with dynamic arrays exist in Excel, WPS Office offers a highly compatible, lightweight, and free alternative for managing spreadsheets, arrays, and standard data ranges without the heavy subscription costs.

1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite on your device.
2. Open your workbook: Launch WPS Spreadsheet and open your existing .xlsx files without losing any data or formatting.
3. Apply formulas seamlessly: Use standard range formulas and data management tools efficiently with 100% familiar syntax.

Fully compatible with Microsoft Excel (.xlsx) formats, ensuring your formulas and data translate seamlessly.Lightweight software architecture that loads large datasets and complex calculations much faster.Familiar user interface requiring zero learning curve for users migrating from Microsoft Office.Completely free to use for daily spreadsheet tasks, data filtering, and formatting.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a #SPILL error only sometimes?

A #SPILL! error occurs whenever a dynamic array formula is blocked from expanding. This happens if there is existing data, a merged cell, or a structured Excel Table in the way of the expected output range. Clearing the obstructing cells resolves the error.

Can I force an Excel table to accept spilled array formulas?

No, Microsoft Excel does not support dynamic array spilling inside structured Tables. You must use standard ranges for any functions that return multiple spilled results.

How do I fix a #SPILL! error without deleting my table?

You can place the dynamic array formula in an empty standard cell next to or below your table, referencing the table data (e.g., =UNIQUE(Table1[Data])). The results will spill correctly outside the table while keeping your original source table completely intact.

What is a dynamic array in Excel?

A dynamic array is a feature where a single formula can return multiple values that automatically 'spill' into adjacent blank cells. The spilled range dynamically resizes as the source data grows or shrinks.

Excel & Data Problems