To return a changing list with duplicates removed, combine Excel’s FILTER and UNIQUE functions. With values in A2:A100 and blanks to exclude, enter =UNIQUE(FILTER(A2:A100,A2:A100<>"","")) in an empty cell; Excel spills one copy of each nonblank value into the cells below.
This is usually the simplest formula-based answer to “Excel filter formula no duplicates?” FILTER selects which records to include, while UNIQUE removes repeated values from the selected results. The list updates when the source data changes, so you do not need to rerun a manual deduplication step each time.
What the formula does
The functions do two different jobs. FILTER examines an array and returns the entries whose corresponding conditions are TRUE. UNIQUE then compares the returned entries and keeps one copy of each distinct item. In the opening example, the FILTER condition checks whether each cell is not blank, and UNIQUE removes repetitions from the resulting list.
For example, if A2:A6 contains Apple, Pear, Apple, Orange, and Pear, the formula returns Apple, Pear, and Orange, in the order each first appears. It does not delete or change anything in A2:A6: the output is a separate, calculated result. That distinction matters when the original list is a record of transactions or other data that must remain intact.
FILTER is not the same as the Filter button on the Data tab. The worksheet function creates a result array from a formula; the ribbon filter hides rows in place so you can inspect a table. You can use both, but they solve different problems.
Excel version and where to enter it
FILTER and UNIQUE are available in Microsoft 365 and Excel 2021 or later. The instructions here are for Excel for Microsoft 365 on Windows. In Excel for Mac or Excel for the web, the functions work in supported versions too, though the interface and the way you select cells may look different. Older perpetual editions, such as Excel 2019, do not include these functions; use one of the alternatives below if your installation does not recognize them.
Choose a blank cell with enough empty space beneath it for the result. Type the formula and press Enter. Excel’s dynamic-array behavior places the returned values in neighboring cells automatically. The formula itself occupies only the starting cell, called the anchor cell. Do not copy it down to make the list; the result spills as far as needed.
If Excel displays a #SPILL! error, another value, merged cell, or other obstruction may be occupying part of the intended output range. Clear the obstructing cells or move the formula to a less crowded area. You can click the error indicator to see more information about what is blocking the spill.
Filter by a condition, then remove duplicates
Often the goal is not to deduplicate the entire column, but to get a unique list from records meeting a condition. Suppose column A contains product names and column B contains regions. The condition range must line up row for row with the values being returned.
With product names in A2:A100 and regions in B2:B100, enter this formula in an empty cell to list nonblank products from North region records, once each: =UNIQUE(FILTER(A2:A100,B2:B100="North","")). It first keeps names whose region is North, then removes repeated names. If the matching records contain Maple, Birch, Maple, the result is Maple and Birch.
FILTER’s include argument must have the same number of rows or columns as the array being filtered. If A2:A100 is the array, B2:B100 is an aligned condition range; using B2:B99 instead creates a size mismatch. You can also combine conditions, but use parentheses so Excel evaluates each comparison clearly. For example, the ampersand operator can combine two TRUE/FALSE tests as an AND condition; multiplication can also be used for AND-style filtering, while addition can represent OR-style inclusion.
Return unique rows instead of unique cells
UNIQUE can compare complete rows across several columns. This is useful when a record is defined by a combination of fields, such as product, size, and location. It keeps a row only when that combination has not appeared earlier in the filtered array.
With product, size, and location in A2:C100, and with column A populated for each valid record, enter =UNIQUE(FILTER(A2:C100,A2:A100<>"","")) in an empty cell. The formula returns distinct rows across columns A through C, excluding rows where the product cell is blank. If Maple, Small, East appears twice but Maple, Large, East appears once, both different combinations remain.
This is not the same as asking for unique product names alone. For that, make the array just the product-name column. When the array contains multiple columns, UNIQUE treats each row as the item to compare, so two rows with the same product but different sizes are distinct. Choose the array based on what counts as a duplicate in your data.
Sort the unique results
UNIQUE normally preserves the order of first appearance rather than sorting alphabetically. That is often helpful because the output follows the source list, but it may not be the order readers expect. Wrap the unique result in SORT when you want an ascending result.
With source values in A2:A100, enter =SORT(UNIQUE(FILTER(A2:A100,A2:A100<>"",""))) in a blank cell. The result contains each nonblank value once and sorts the output in ascending order. For text, that is generally alphabetical; for numbers, it is numerical. If you need descending order, SORT has an optional sort-order argument, but confirm the resulting order with your actual data and locale.
Sorting is a presentation choice, not part of removing duplicates. If first-seen order carries meaning, such as the order in which categories were encountered, use the unsorted formula. The source data remains unchanged either way.
Unique values versus values that occur only once
A common source of confusion is the phrase “no duplicates.” Usually it means keep one copy of every distinct value, even if the value appeared several times. That is the default behavior of UNIQUE. A different request is to show only entries that occur exactly once in the original list, excluding anything repeated.
With values in A2:A100, enter =UNIQUE(FILTER(A2:A100,A2:A100<>"",""),,TRUE) in an empty cell to request values that occur exactly once among the nonblank entries. The final TRUE uses UNIQUE’s exactly_once option. For the list Apple, Pear, Apple, Orange, Pear, this returns Orange, not Apple and Pear. Leave this option out when the goal is to keep one copy of each repeated item.
In that formula, the two commas before TRUE are intentional: they leave UNIQUE’s optional by_col argument at its default and provide TRUE for exactly_once. This option is useful for finding one-off entries, but it is not the usual deduplication setting. If you are reconciling names or inventory labels, decide whether repeated items should be represented once or excluded entirely before choosing the formula.
Common problems and practical checks
- #NAME?: Your Excel version may not support FILTER or UNIQUE, or a function name may be misspelled. Check the version first. In locales that use different formula separators, Excel may also expect a semicolon instead of a comma.
- #CALC! when nothing matches: FILTER returns an empty-result error by default when no records meet its condition. The third argument in the examples supplies an empty text result instead. This makes the formula easier to use in reports, though the result is visually blank rather than a message explaining why nothing matched.
- #SPILL!: Clear cells in the output area, unmerge obstructing cells, or move the formula. The spill range must be free to expand.
- Unexpectedly different entries: Inspect leading or trailing spaces, inconsistent spelling, and values that look alike but are stored differently. For instance, a name with an extra space may be treated as a different text value. Clean the source data if those differences are accidental.
- Formula entered inside an Excel table: Dynamic array formulas should be placed outside the table because spilled formulas are not supported within table cells. The source may still be an Excel table; put the formula in a nearby worksheet range.
Also check what the word blank means in your source. The condition A2:A100<>”” excludes empty cells and formula results that return an empty string. A cell containing spaces is not truly empty, so it can still appear in the output. If spaces are common, clean or normalize the source column rather than assuming the nonblank test removes them.
Formula method compared with manual deduplication
Use the formula approach when the source changes and the result should update automatically, when you want a separate report list, or when the output must respond to criteria. It leaves the original data untouched and makes the selection logic visible in a cell. Its main requirements are a compatible Excel version and room for the result to spill.
For a one-time cleanup, Excel also has a Remove Duplicates command on the Data tab. It removes repeated records from the selected data, so make a copy first if you need to preserve every original row. For a filtered copy rather than a live formula result, the Advanced Filter feature can copy records to another location and select unique records only. These tools are useful when a fixed result is preferable, but they do not recalculate as a dynamic UNIQUE formula does.
Power Query is another option for repeatable imports and data-cleaning workflows, especially when the data arrives from files or external sources. It can remove duplicates as part of a query that you refresh. Choose it when transformation and refresh steps are broader than producing one small unique list; for a simple live list, FILTER plus UNIQUE is usually more direct.
Frequently Asked Questions
Can I use FILTER alone to remove duplicates?
No. FILTER selects rows based on conditions but does not, by itself, deduplicate them. Combine it with UNIQUE when you need a filtered list with one copy of each distinct result.
Does the formula delete duplicates from my original data?
No. It creates a separate spilled result and leaves the source range unchanged. Use Remove Duplicates only when you intend to alter the selected data, and keep a backup if the original records matter.
Can I get unique results from two or more columns?
Yes. Set the array argument to the full set of columns that defines a record. UNIQUE then compares rows, so rows are considered duplicates only when their values across those columns match.
Why does my formula return #SPILL!?
Excel cannot place the complete result because something is in the spill range or the formula is in a location that cannot spill, such as a table cell. Clear the blocked range or move the formula to an unobstructed area outside the table.
What can I use if my Excel version lacks UNIQUE?
For a fixed list, use Data > Remove Duplicates on a copy, or use Advanced Filter to copy unique records elsewhere. In older Excel versions, a PivotTable can also summarize distinct categories, although the setup and refresh behavior differ from a live dynamic-array formula.
Related
- Excel Custom Number Formatting Codes: How They Work
- Excel Custom Number Formatting Syntax: A Clear Guide
- Excel Custom Number Format with Text: How It Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Data Model: How to Sort Two Columns TogetherYes—but the right method depends on what you mean by “sort two columns.” In Excel for Microsoft 365…
- Excel Formula to Find All Tab Names in Your WorkbookExcel has no built-in worksheet formula that directly returns every tab name. In Microsoft 365 for Windows, you…
- Align Right in Excel: Shortcut, Menu, and Mac KeysIn Excel for Microsoft 365 on Windows, select the cell or range and press Alt, H, A, R—one…
- Find Lowercase Letters in Excel with Formulas and FiltersTo find cells containing at least one lowercase letter, put the text in A2 and enter =IF(A2="",FALSE,LET(chars,MID(A2,SEQUENCE(LEN(A2)),1),SUMPRODUCT(--EXACT(chars,LOWER(chars)),--NOT(EXACT(chars,UPPER(chars))))>0)) in…
- Excel Formula to Uppercase the First Letter in CellsTo uppercase only the first character in an Excel cell while leaving the rest unchanged, use =UPPER(LEFT(A2,1))&MID(A2,2,LEN(A2)). If…
- Excel INDEX Formula Examples for Rows, Columns, and MatchesExcel’s INDEX function returns the value at a chosen position in a range. With values in A2:C5, =INDEX(A2:C5,3,2)…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.