Excel UNIQUE and FILTER Not Working? Fixes

In Excel for Microsoft 365, put FILTER inside UNIQUE to return distinct values that meet a condition. With names in A2:A100 and region labels in B2:B100, enter =UNIQUE(FILTER(A2:A100,B2:B100="North","No matches")) in an empty cell; it returns each matching name once, or “No matches” when none qualify. If it still fails, the error message, range sizes, and space available for the results are the best places to start.

What UNIQUE and FILTER do

FILTER selects values or rows that meet a test; UNIQUE removes repeated values or rows from the result. Nesting them lets you filter first and deduplicate second. The order matters: UNIQUE(FILTER(…)) means “distinct results among the matches.” Reversing the functions can give a different result because filtering a list that has already been deduplicated is not always equivalent.

Both are dynamic-array functions. Rather than returning a single value into the formula cell, they can spill results into neighboring cells. Enter the formula in the top-left destination cell and let Excel fill the output area automatically. You do not copy the formula down. UNIQUE and FILTER are available in Microsoft 365 and Excel 2021 or later, including supported Excel for the web versions. They are not available as native worksheet functions in Excel 2019 and earlier perpetual versions; an unsupported version may show #NAME? or fail to recognize the function.

Check the ranges and formula syntax

The array to return and the include test used by FILTER must have compatible dimensions. If the formula filters A2:A100 using B2:B100, both ranges contain 99 rows. A common mistake is to use a whole column for one argument and a short range for the other, or to start one range on row 2 and the other on row 3. FILTER needs one include value for each row or column being tested.

Check commas, parentheses, quotation marks, and range endpoints. In a formula, text criteria such as North need straight double quotation marks. Also confirm that the formula is in a cell outside the source range if the output could overwrite or interfere with its inputs. Excel’s function-argument tooltips can help identify which argument is incomplete while editing.

Some regional settings use a semicolon rather than a comma as the argument separator. If a correctly written comma-separated formula is rejected at entry, check Excel’s regional list-separator setting or try the separator your installation expects. This is a parsing issue, not a change to what UNIQUE or FILTER does.

Identify the error message

The displayed error usually narrows the cause quickly:

#SPILL! means Excel cannot place the full dynamic result in the cells it needs. Look for occupied cells, merged cells, or other obstacles in the spill area.
#CALC! often appears when FILTER finds no qualifying rows and its optional if_empty argument was left out. Supply a result such as “No matches” if an empty result is expected.
#VALUE! can indicate incompatible array dimensions or an invalid include array. Confirm that the condition returns one TRUE/FALSE result for every row being filtered.
#NAME? often means the Excel version does not support the function, or the function name or quotation marks were entered incorrectly.
A wrong or empty result may not be a formula error at all. The criterion may not match the underlying values, or a source range may exclude new records.

When a calculation error appears, select the formula cell and use the warning icon or Formula Bar to inspect the expression. For #SPILL!, selecting the error indicator may identify the obstructing cell. Clear or move that content rather than deleting data without checking what it contains.

Fix #SPILL! and output-area problems

Dynamic results need a clear rectangular spill area. A space character, a formula returning an empty string, or an old value can block a cell even if it looks blank. Clear the intended output cells, unmerge cells in the area, and move the formula if another table or report occupies the required space. A spill range cannot expand into a worksheet table, so place the formula outside the table. You can still use table columns as the formula’s source.

Spilled results resize as source data changes, which is useful but can surprise people who have placed notes or other formulas directly beside the output. Keep a buffer around the result, or reference the complete spill range from another formula. With the UNIQUE results beginning in D2, this formula counts the current number of spilled items:

With the unique output starting in D2, enter this in a separate cell to count its current spill range: =COUNTA(D2#). The # refers to the whole spill range anchored at D2; the count changes when the result grows or shrinks. If the anchor cell contains an error, the count will not repair the original formula.

Check blanks, spaces, and inconsistent source data

UNIQUE compares the values it receives, not what a person assumes the cells mean. A cell containing a space is not the same as an empty cell, and “North” with a trailing space may fail to match the criterion “North.” Imported data can also contain nonprinting characters, inconsistent punctuation, or numbers stored as text. These differences can create apparent duplicates or cause FILTER to return no matches.

If blank source cells should not appear in the results, add a nonblank test to the include condition. With names in A2:A100 and regions in B2:B100, the following returns distinct nonblank names from North:

Assuming names are in A2:A100 and regions in B2:B100, use =UNIQUE(FILTER(A2:A100,(B2:B100="North")*(A2:A100<>""),"No matches")). Multiplying the two tests acts as AND: the region must be North and the name must not be an empty string. The formula will not remove cells containing only spaces, so clean those values separately if they are present.

For a quick inspection, compare suspicious values in the formula bar and test representative rows directly. TRIM can remove ordinary leading and trailing spaces, but imported nonbreaking spaces may need additional cleanup. Converting text-formatted numbers to actual numbers can also matter when criteria compare numeric values. Clean a copy or a helper column first if the source data must be preserved as imported.

Use table references and criteria carefully

When data is an Excel table, structured references can grow automatically as rows are added, which avoids a fixed range accidentally excluding new records. For a table named Sales with columns Customer and Region, place the formula outside the table:

Assuming the Excel table is named Sales and has Customer and Region columns, enter =UNIQUE(FILTER(Sales[Customer],Sales[Region]="North","No matches")). It returns distinct customer values whose Region is North, including future rows added to the table. The formula cell must remain outside the table because a spilled array cannot spill within a table.

For multiple conditions, multiply Boolean tests for AND logic or add them for OR logic. Make sure each test refers to an equally sized range. For example, requiring both a region and a status can be expressed by multiplying the two comparisons. If you use a condition containing dates, check whether the source cells contain real Excel dates rather than text that merely looks like a date.

Return values that occur exactly once

A frequent misconception is that UNIQUE always means “show values that appeared once.” By default, it returns one copy of every distinct value, including values that appeared many times. Its optional third argument changes that behavior: TRUE returns only values occurring exactly once in the input array.

With product codes in A2:A100, this formula returns only codes that occur once in that range:

Assuming product codes are in A2:A100, enter =UNIQUE(A2:A100,FALSE,TRUE). The second argument FALSE says the list is arranged by rows, as ordinary vertical data is; the third argument TRUE requests items that occur exactly once. This is different from returning a deduplicated list of all codes.

You can combine the exact-once option with FILTER when you need uniqueness among a subset, but be deliberate about the order: the values passed into UNIQUE determine what counts as a duplicate. If duplicates should be counted across the entire source rather than only among matching rows, filtering first changes the population being counted.

Check calculation and workbook compatibility

Excel normally recalculates dynamic formulas automatically. If the output appears stale, confirm that calculation is set to Automatic from Formulas > Calculation Options > Automatic in Excel for Microsoft 365 on Windows. The same setting is available from the Formulas tab in Excel for Mac, although ribbon layout can vary by release. Press F9 on Windows to recalculate formulas; on Mac, use Fn+F9 on keyboards where the function keys control hardware features, or use the Excel calculation command.

Also consider where the workbook will be opened. A file using modern dynamic-array functions may not calculate correctly in an older Excel installation. If colleagues use older versions, consider providing a static copy of the results or using an older-compatible method, and verify behavior in the actual target version. Excel for the web supports these functions, though a large workbook or browser performance may affect responsiveness.

Alternatives when dynamic arrays are unavailable

If the workbook must work in an older Excel version, Data > Advanced can extract unique records from a selected list. In the Advanced Filter dialog, choose Copy to another location, specify the source and destination ranges, and select Unique records only. This is a command-based extraction rather than a formula that updates its spilled result automatically, so repeat the operation when the source data changes.

For a repeatable refresh workflow, Power Query can import a table, filter rows, and remove duplicates before loading the result back to a worksheet. Use Data > From Table/Range to open the query editor, then apply the filter and remove-duplicates steps appropriate to the data. Power Query is useful for larger or routinely refreshed datasets, but it requires refreshing the query and is not a direct substitute for a live worksheet formula.

Frequently Asked Questions

Why does UNIQUE return duplicates?
It returns one instance of each distinct value by default, not only values that occurred once. Use its exactly_once option when the goal is to keep only values with a single occurrence in the input.

Why does FILTER return #CALC!?
Most often, no rows meet the condition and the formula has no if_empty result. Check whether the criteria truly match, then provide a message or other fallback if an empty result is normal.

Can I use UNIQUE and FILTER inside an Excel table?
You can use table columns as source references, but a dynamic-array formula cannot spill inside a table. Put the formula in a regular worksheet cell outside the table and leave room for its result.

Why does my formula work in Microsoft 365 but not Excel 2019?
UNIQUE and FILTER are dynamic-array functions not included as native functions in Excel 2019. Use a compatible newer Excel version, Advanced Filter, Power Query, or another workflow suitable for the workbook’s users.

Does UNIQUE ignore blank cells?
Do not assume that it will exclude blanks in the way you want. Filter out empty cells explicitly if they should not appear, and remember that a cell containing spaces is not truly blank.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.