Formula Errors

How to Fix Excel #SPILL! Error with UNIQUE and TOCOL

Natalie Taylor Sep 28, 2026 871 views

Question details

The user is encountering a #SPILL! error when attempting to use the UNIQUE and TOCOL functions in Excel to extract unique values from a dataset.

How to Fix the Excel #SPILL! Error with UNIQUE and TOCOL

Product: Microsoft Excel
Device & OS: not provided
Scenario: Extracting unique values from a column using dynamic array formulas.
Observed behavior: Excel returns a #SPILL! error, indicating that the formula cannot populate the required neighboring cells, or a #NAME? error if the dynamic array function is not supported.

Before you start

Ensure that your version of Excel supports dynamic array formulas (such as Microsoft 365 or Excel 2021) and verify that there is sufficient empty space below your target cell for the results to populate.

Solution 1Recommended

Clear the Spill Range and Simplify the Formula

Resolve the #SPILL! error by removing data obstructions and streamlining the UNIQUE formula.

The #SPILL! error occurs when the dynamic array formula does not have enough blank cells to output its results. Additionally, since the UNIQUE function outputs a vertical array when referencing a column, adding TOCOL is unnecessary.

Identify the spill range

Click the cell displaying the #SPILL! error. A dashed blue border will outline the range where Excel is trying to display the results.

Clear obstructions

Check the highlighted dashed range for any typed values, invisible spaces, or merged cells. Delete or clear all contents in these cells.

Simplify the formula

Remove the TOCOL function. Instead, enter the direct formula =UNIQUE(O2:O1000) into the cell.

Press Enter

Hit Enter to apply the formula. The unique values should now successfully populate the cleared vertical space.

Clear the Spill Range and Simplify the Formula

Optimization Tip: Avoid using entire column references like =UNIQUE(O:O) as they can cause performance issues. Always specify the exact data range, such as =UNIQUE(O2:O1000).

Solution 2

Verify Dynamic Array Support (Fixing the #NAME? Error)

Determine if your Excel version supports the UNIQUE function, which is required for dynamic arrays.

Free Microsoft Office alternative

Use WPS Office to Easily Handle Dynamic Arrays

If your current spreadsheet software does not support modern dynamic arrays like the UNIQUE function, consider switching to WPS Office. It provides a highly compatible, free, and lightweight alternative to Microsoft Office, ensuring your formulas work seamlessly without versioning headaches.

1. Download and Install: Download WPS Office from the official website and follow the straightforward installation process.
2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing Excel file.
3. Apply the UNIQUE Formula: Type =UNIQUE() in your target cell to effortlessly extract unique values without encountering version-based #NAME? errors.

Fully compatible with Microsoft Excel (.xlsx) formats and formulas.Built-in support for modern dynamic array functions including UNIQUE and FILTER.Free and lightweight with a familiar, user-friendly interface.Seamless migration with no steep learning curve required.

Frequently Asked Questions

Why is the TOCOL function unnecessary when using UNIQUE?

The UNIQUE function automatically outputs its array in the same orientation as the source data. Since you are referencing a vertical column, UNIQUE will naturally return a vertical array, making the TOCOL function redundant.

How can I extract values that appear more than once using UNIQUE?

You can combine UNIQUE with FILTER and COUNTIF. For example, use the formula =UNIQUE(FILTER(G2:G21, COUNTIF(G2:G21, G2:G21)>1)) to return a list of only the duplicate values in that range.

What does it mean if the #SPILL! error is accompanied by a dashed line?

The dashed blue line indicates the specific boundary of cells that Excel requires to display the results of your dynamic array formula. You must ensure every single cell inside this dashed area is completely empty to resolve the error.

Excel & Data Problems