Function Problems

How to Fix Excel UNIQUE Returning 0 from a Filtered Array

Camila Milosovich Oct 21, 2026 869 views

Question details

The user needs to fix an issue where the UNIQUE function returns a 0 when extracting data from a filtered array on a different worksheet, which is often caused by blank cells in the source data.

How to Fix Excel UNIQUE Returning 0 from a Filtered Array

Product: Microsoft Excel
Device & OS: not provided
Scenario: Extracting, filtering, and sorting unique values from a dataset that spans across different worksheets.
Observed behavior: While the FILTER and SORT functions return values correctly, adding the UNIQUE function to the formula causes it to output an unexpected 0 due to blank cells in the referenced range.

Before you start

Verify that your source data range does not contain completely empty rows, as array formulas often evaluate empty cells as zeros. Ensure you are using a version of Excel or WPS Spreadsheet that supports modern Dynamic Array functions.

Solution 1Recommended

Combine UNIQUE, SORT, and FILTER to Exclude Blank Cells

Use a nested formula to filter out empty strings before applying the UNIQUE function. This prevents the spreadsheet from treating blank cells as 0.

When referencing a range that contains blank cells, the UNIQUE function automatically interprets those blanks as zeroes. By placing a FILTER function inside the UNIQUE function, you can logically exclude any blank cells ("") from the dataset before the unique values are evaluated.

Select the target cell

Click on the empty cell where you want the dynamic array results to begin spilling. Make sure there is enough empty space below it.

Enter the nested formula

Type the formula: =UNIQUE(SORT(FILTER(Sheet1!A2:A12,Sheet1!A2:A12<>""),1,1)). Be sure to replace 'Sheet1!A2:A12' with the actual range of your source data.

Apply the array formula

Press Enter to execute the formula. The result will now automatically filter out blanks, sort the data alphabetically or numerically, and display only unique IDs.

Combine UNIQUE, SORT, and FILTER to Exclude Blank Cells

Dynamic Array Spilling: The results will automatically spill into adjacent cells. If you see a #SPILL! error, simply clear the cells directly below your formula to make room for the data.

WPS Spreadsheet Solution

Easily Manage Dynamic Arrays with WPS Spreadsheet

WPS Spreadsheet provides robust native support for modern dynamic array functions like UNIQUE, FILTER, and SORT, allowing you to easily process complex datasets and seamlessly handle Excel workbooks without returning unexpected zeros.

1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your source data.
2. Select the output cell: Click the cell on your destination worksheet where you want the unique values to be displayed.
3. Input the array formula: Type =UNIQUE(SORT(FILTER(Sheet1!A2:A12,Sheet1!A2:A12<>""),1,1)) and adjust the range to match your data.
4. View the extracted data: Press Enter to instantly view your cleaned, sorted, and unique data spilling perfectly into the column.

Fully supports advanced dynamic array formulas including UNIQUE, FILTER, and SORT.Ensures 100% format compatibility with Microsoft Excel (.xlsx) files.Lightweight, fast, and completely free alternative for powerful data analysis.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the UNIQUE function return a 0 instead of being blank?

When a dynamic array function evaluates an empty cell within a referenced range, it interprets that empty cell as a zero. Using a FILTER function with the condition `<>""` removes the blanks before UNIQUE is applied, solving the problem.

How do I fix the #SPILL! error when using the UNIQUE function?

A #SPILL! error occurs when the dynamic array needs to output results into cells that already contain data or formatting. To fix this, simply delete or move the data in the cells below and to the right of your formula so the array has enough space to expand.

Can I use the UNIQUE function across different worksheets?

Yes. You can reference data on another worksheet by including the sheet name followed by an exclamation mark before the cell range, such as `=UNIQUE('Data Sheet'!A2:A20)`.

Does WPS Office support the UNIQUE and FILTER functions?

Yes, the latest versions of WPS Spreadsheet fully support dynamic array functions like UNIQUE, FILTER, SORT, and others, ensuring seamless processing and full compatibility with complex Excel formulas.

Excel & Data Problems