Excel FILTER Function: Return Results Without Duplicates

Formulas & functions · Updated November 19, 2026

Excel’s FILTER function does not remove duplicates by itself. To return matching results without repeats, wrap FILTER inside UNIQUE; for example, use =UNIQUE(FILTER(B2:B10,C2:C10="Open","No matches")) when the values are in B2:B10 and their status is in C2:C10.

How FILTER and UNIQUE work together

FILTER selects values or rows that meet a condition. UNIQUE then removes repeated values or repeated rows from what FILTER returns. The order matters: filtering first means Excel removes duplicates only among the matching records, rather than across the entire source list.

In the opening example, the formula checks the status in column C and returns the corresponding entries from column B only when the status is Open. If the matching values include Widget, Cable, Widget, Adapter, and Cable, the spilled result is Widget, Cable, and Adapter. UNIQUE keeps the first occurrence of each value and leaves the values in their original order; it does not sort them alphabetically.

For a concrete layout, suppose B2:B10 contains product names and C2:C10 contains statuses. If the Open rows contain Widget, Cable, Widget, Adapter, and Cable, this formula returns the three distinct names in that order:

With product names in B2:B10 and statuses in C2:C10, use:

=UNIQUE(FILTER(B2:B10,C2:C10="Open","No matches"))

The third FILTER argument is the optional value to display if nothing matches. Here it displays No matches instead of allowing FILTER to return a no-results error. The result is a dynamic array: Excel places the items in the formula cell and spills the remaining values into cells below it. Keep those spill cells clear so the result can expand.

Return distinct values or distinct rows

What counts as a duplicate depends on the array supplied to UNIQUE. If FILTER returns a single column of product names, UNIQUE compares names. If FILTER returns several columns, it compares entire rows. Two records with the same product name but different dates or regions are therefore distinct rows, even though the name itself repeats.

For example, to return every distinct open record across columns A to C, where those columns contain order number, product, and status, use this formula:

With records in A2:C10 and statuses in C2:C10, use:

=UNIQUE(FILTER(A2:C10,C2:C10="Open","No matches"),FALSE)

The FALSE argument tells UNIQUE to compare rows rather than columns. It is optional in this example because row comparison is the default, but including it can make the intention clearer. If your goal is a list of distinct product names rather than complete records, return only the product-name column from FILTER, as in the earlier example.

This distinction is useful when a list appears to contain “duplicates” but the results do not collapse as expected. Check whether the formula is comparing one field or whole records. If two rows differ in even one included column, UNIQUE treats them as different rows.

Show values that occur exactly once

Removing duplicates is not the same as returning values that appear only once. By default, UNIQUE keeps one copy of each value that appears in its input. Its optional exactly_once argument changes the behavior: TRUE returns only values whose count in the input array is one, excluding values that repeat.

With product names in B2:B10 and statuses in C2:C10, this formula filters to Open records and then keeps only product names that occur exactly once in that filtered set:

=UNIQUE(FILTER(B2:B10,C2:C10="Open","No matches"),FALSE,TRUE)

If Widget occurs twice among the open records, it will not appear in this result. Cable will also be excluded if it occurs twice; Adapter will remain if it occurs once. This is often the right choice for finding one-off entries, but it is not the setting to use when you simply want a clean list with one copy of each matching value.

Filter with more than one condition

FILTER can use an expression that combines conditions. Multiplying condition tests works like AND: a row must satisfy both tests. Adding tests works like OR: a row can satisfy either test. UNIQUE can then remove repetitions from the filtered output just as it does with a single condition.

Suppose column B contains product names, column C contains status, and column D contains region. To list distinct products that are Open and in the West region, use:

With product names in B2:B10, statuses in C2:C10, and regions in D2:D10, use:

=UNIQUE(FILTER(B2:B10,(C2:C10="Open")*(D2:D10="West"),"No matches"))

The two tests produce arrays of TRUE and FALSE values. Multiplication keeps a row only when both values are TRUE. For an OR condition, use addition between the tests instead; for example, a row could qualify if its status is Open or its region is West. Make sure each condition range has the same number of rows as the values being returned, or Excel may report a calculation error.

Sort the unique results

UNIQUE preserves the order in which distinct items first occur in the filtered input. If you want an alphabetical or numeric order, wrap the result in SORT. This keeps the tasks separate: FILTER chooses records, UNIQUE removes repeats, and SORT arranges the final list.

With product names in B2:B10 and statuses in C2:C10, this formula returns distinct matching names in ascending order:

=SORT(UNIQUE(FILTER(B2:B10,C2:C10="Open","No matches")))

SORT is useful for a presentation list or a dropdown source that readers expect to scan alphabetically. It may not be appropriate if the original sequence carries meaning, such as the order in which products appeared in a transaction log. In that case, use UNIQUE without SORT to preserve first-occurrence order.

Common problems and how to fix them

  • #SPILL! means Excel cannot place the full dynamic-array result in the cells it needs. Clear obstructing cells, remove merged cells in the spill area, or move the formula to an open area. A formula entered inside an Excel Table can also cause confusion because spilled array formulas are not supported within table cells; place the formula outside the table.
  • No matches or an error: FILTER returns an error when no records meet the condition unless its optional if_empty argument is supplied. Add a message such as No matches, or another suitable fallback. Check that the criteria text matches the source data and that the condition range aligns with the return range.
  • Values that look identical remain separate: leading or trailing spaces, nonprinting characters, different numbers stored as text, or genuinely different underlying values can affect results. Inspect or clean the source data if needed. UNIQUE is not a general-purpose fuzzy matching or data-cleaning tool.
  • Blanks appear in the output: a matching source row may contain an empty cell in the returned column. If blanks should be excluded, add a not-blank test to the FILTER condition, using the same row span as the other conditions.
  • The formula displays as text: check that the cell is not formatted as Text and that the entry begins with an equals sign. After changing the format to General, re-enter the formula if necessary.

Excel’s duplicate comparison is not case-sensitive in the way many readers expect: values that differ only by capitalization are generally treated as duplicates by UNIQUE. If letter case is important to your definition of a distinct value, a standard UNIQUE formula may not meet that requirement; a case-sensitive approach needs additional formula logic.

Version and platform support

FILTER and UNIQUE are dynamic-array functions available in Microsoft 365 and Excel 2021 or later, including the relevant current Excel editions for Windows, Mac, and the web. They are not available as native functions in older perpetual versions such as Excel 2019. If someone opens a workbook in an older version, formulas using these functions may not calculate correctly.

In Microsoft 365 for Windows, enter the formula in a cell and press Enter; Excel spills the result automatically. Mac and Excel for the web support dynamic arrays as well, although exact interface details and update timing can vary by platform. The formula syntax shown here uses commas between arguments, which is standard in many installations. Some regional settings use semicolons as separators, so an installation configured that way may require semicolons instead.

Alternatives when UNIQUE is unavailable

In older Excel versions, you can use the Advanced Filter command to copy distinct records to another location. Select the source data, open the Data tab, choose Advanced in the Sort & Filter group, select Copy to another location if you want a separate output, and check Unique records only. Advanced Filter is a one-time operation rather than a formula that automatically updates when source data changes, so repeat it when the underlying list changes.

A PivotTable can also summarize categories into a distinct list, particularly when you need counts or other summaries alongside the categories. It is more interactive than a simple formula but may need refreshing after the source data changes. If you have Microsoft 365 or Excel 2021 and want a live list that responds to edits, FILTER combined with UNIQUE is usually the most direct option.

Frequently Asked Questions

Does FILTER remove duplicates automatically?

No. FILTER returns every row or value that meets its condition, including repeats. Wrap it in UNIQUE when the result should contain one copy of each matching value or row.

How do I get unique values after filtering in Excel?

Use UNIQUE around FILTER. For example, with names in A2:A20 and categories in B2:B20, you could return distinct names for a chosen category by placing both functions in one dynamic-array formula and setting the FILTER condition to that category.

What is the difference between UNIQUE and exactly once?

UNIQUE normally keeps one instance of each value, even if it appeared several times in the source. Its exactly_once option keeps only values that appeared once and removes values that appeared more than once.

Can I use FILTER and UNIQUE in Excel 2019?

Excel 2019 does not include these functions as native dynamic-array functions. Use a method such as Data > Advanced with Unique records only, or use a PivotTable, and update or refresh the result when the source changes.

Why does my FILTER and UNIQUE formula show #SPILL!?

Excel needs clear cells to display the full result. Clear any values blocking the spill range, check for merged cells, and place the formula outside an Excel Table if needed.

Related

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

More in Formulas & functions

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

Spotted an error in this guide? Let us know.