To return filtered results without duplicates in Excel, combine FILTER with UNIQUE: FILTER selects the rows you want, and UNIQUE removes repeated values from those results. For example, with names in A2:A100, =UNIQUE(FILTER(A2:A100,A2:A100<>"")) returns a live list of distinct, nonblank names; it does not delete anything from the source data.
How FILTER and UNIQUE work together
These functions solve two different parts of the task. FILTER tests a range against a condition and returns matching values. UNIQUE then compares the returned values and keeps one copy of each distinct value. Nesting FILTER inside UNIQUE means the duplicate check applies to the filtered results, not necessarily to the entire source list.
The formula returns a dynamic array, also called a spilled result. Enter it in one cell, and Excel fills the cells below or beside it as needed. If the source data changes, the result recalculates automatically. The original records remain intact, which is useful when you want a report or a reusable list rather than a permanent change to the data.
FILTER and UNIQUE are available in Microsoft 365 and Excel 2021 or later. The examples below use Excel for Microsoft 365 on Windows and comma argument separators. In some regional settings Excel expects semicolons instead of commas; if Excel rejects a formula despite correct function names, your list separator may be the reason.
Return a distinct list and exclude blanks
For a simple list, use UNIQUE on the range. If the source may contain empty cells, wrap it in FILTER so blanks do not appear as an item in the output.
With names in A2:A100, use this formula in an empty cell outside the source range:
=UNIQUE(FILTER(A2:A100,A2:A100<>""))
If the source contains Ana, Ben, Ana, and Cara, with any blank cells ignored, the result is Ana, Ben, and Cara, each once. The result follows the order in which each value first appears in the source; UNIQUE does not sort the list. The condition A2:A100<>"" tells FILTER to keep only cells that are not empty.
If blanks are meaningful data in your situation, do not add the nonblank condition. With names in A2:A100, =UNIQUE(A2:A100) returns the distinct entries, including a blank entry if one is present. Choose the blank-handling behavior deliberately, especially when a blank represents an unknown value rather than an accidental empty cell.
Filter by a condition before removing duplicates
FILTER can use another column to decide which entries to return. For example, suppose a list has customer names in A2:A100 and regions in B2:B100. To get each nonblank name associated with the West region, enter this formula in an empty cell:
With names in A2:A100 and regions in B2:B100:
=UNIQUE(FILTER(A2:A100,(B2:B100="West")*(A2:A100<>""),"No matches"))
The multiplication sign combines the two tests as AND: a row must have West in column B and a nonblank name in column A. If the matching names are Ana, Ben, Ana, and Cara, the result is Ana, Ben, and Cara. The third FILTER argument supplies a message when nothing meets the condition, instead of returning a calculation error for an empty result.
For OR conditions, add logical tests rather than multiplying them. With names in A2:A100 and regions in B2:B100, the following returns distinct names from either the West or East region:
=UNIQUE(FILTER(A2:A100,((B2:B100="West")+(B2:B100="East"))*(A2:A100<>""),"No matches"))
In this pattern, addition represents OR and multiplication represents AND. Parentheses make the intended logic clear and help prevent mistakes when several conditions are combined.
Keep unique records across multiple columns
Sometimes a duplicate means a repeated record, not a repeated value in one column. To keep distinct combinations of fields, pass the full table range to UNIQUE. Excel then compares whole rows: two rows are duplicates only when their values across the selected columns match.
With item names in A2:A100 and categories in B2:B100, use this formula to return distinct, nonblank item-and-category pairs:
=UNIQUE(FILTER(A2:B100,A2:A100<>""))
If the pair “Mug, Kitchen” appears three times, it appears once in the output. “Mug, Gifts” remains a separate row because the category differs. The FILTER condition uses column A to exclude rows with no item name, while the returned array contains both columns. Make sure the condition range has the same number of rows as the array being filtered.
This distinction matters when choosing the range. Applying UNIQUE to just the item-name column would collapse different categories into one name. Applying it to both columns preserves distinct combinations. If the record spans more fields, include all fields that should count when deciding whether two records are the same.
Remove repeated items or show items that occur once?
“Remove duplicates” can mean either keep one copy of each value or show only values that appeared exactly once. These are not the same result. By default, UNIQUE keeps one copy of every distinct value, including values that appeared many times. Its optional third argument, exactly_once, changes the rule so repeated values are excluded altogether.
With product codes in A2:A100, use this formula to return only codes that occur exactly once, ignoring blanks:
=UNIQUE(FILTER(A2:A100,A2:A100<>""),FALSE,TRUE)
If the list is A12, B07, A12, and C30, the result is B07 and C30. A12 is omitted because it occurs twice. The second argument, FALSE, says to compare values down rows rather than across columns; the third argument, TRUE, requests values that appear exactly once. Use the default UNIQUE behavior instead when you want one representative copy of every value.
Sort the result when order matters
UNIQUE preserves the order of first appearance, which can be helpful when the source order has meaning. If you want an alphabetical or numeric order instead, wrap the result in SORT. For example, with names in A2:A100, this returns distinct nonblank names in ascending order:
=SORT(UNIQUE(FILTER(A2:A100,A2:A100<>"")))
SORT changes presentation, not the duplicate rule. It is optional, and leaving it out avoids reordering results. For a two-column result, sorting can be configured by column number and direction, but first confirm that alphabetical sorting is appropriate for the records you are displaying.
Common errors and things to check
- #SPILL!: Excel cannot place the full dynamic result because one or more destination cells are occupied, merged, or otherwise blocking the spill range. Clear the cells where the result needs to appear, or move the formula. A spilled formula also cannot be placed inside an Excel table; put it in a regular worksheet cell outside the table.
- Unexpected blank in the output: The source may contain genuinely empty cells or formulas that return an empty string. Add a nonblank FILTER condition if those should be omitted. Check for cells containing spaces too: a space is not the same as an empty string, so it may survive that test.
- Values that look identical remain separate: Look for leading or trailing spaces, inconsistent spelling, or values stored differently, such as a number and a text string that looks like that number. UNIQUE compares the underlying entries; standardizing the source data may be necessary before deduplication gives the expected result.
- The formula returns the wrong rows: Check that each include range in FILTER has the same height as the array. Also verify that AND and OR tests use the intended multiplication and addition, and that text criteria are spelled exactly as they appear in the data.
- The list does not update as expected: Confirm that new source rows fall within the referenced range. A fixed range ending at row 100 will not include data entered in row 101. An Excel table can expand as rows are added, and structured references can make the source range easier to maintain.
Another point to check is whether you expect hidden or filtered-out source rows to be ignored. A normal FILTER formula evaluates its stated criteria; it does not automatically mean “only rows currently visible after applying a worksheet filter.” Add an explicit condition for the data you want included, or use a different approach if visibility itself is the criterion.
Formula results versus Excel’s Remove Duplicates command
A formula is preferable when you want a live, non-destructive list that changes with the source. It is also easy to revise: change a criterion or expand the source range, and Excel recalculates the output. The trade-off is that the result is a formula-generated view, not a cleaned replacement for the original records.
For a one-time cleanup, Excel’s built-in command can remove duplicate rows from the selected data. On Windows, select the range, then choose Data > Remove Duplicates. In the dialog, select the columns Excel should compare and confirm. The command changes the selected data, so make a copy first if you may need the original rows. On Mac, the command is also available from the Data tab, though the ribbon layout can vary by Excel release. Excel for the web has a Remove Duplicates command on the Data tab as well.
Choose the columns carefully. Selecting only a customer-name column can remove rows that have the same name but different order details; selecting all record columns treats only fully matching rows as duplicates. The command is useful for permanent cleanup, while FILTER with UNIQUE is usually the better fit for repeatable reporting and preserving source data.
Frequently Asked Questions
Does FILTER remove duplicates by itself?
No. FILTER returns values that meet a condition but can return repeated values. Combine it with UNIQUE when the output should contain one copy of each distinct result.
How do I remove duplicates from a filtered list in Excel?
Wrap FILTER inside UNIQUE, such as =UNIQUE(FILTER(A2:A100,B2:B100="West","No matches")). With names in A2:A100 and regions in B2:B100, it returns distinct names for rows marked West. Add a nonblank test if blank names should be excluded.
Does the formula delete duplicates in my original data?
No. It spills a separate result and leaves the source range unchanged. Use Data > Remove Duplicates when you intend to edit the original records, and keep a backup if those rows may be needed.
How can I keep only values that appear once?
Use UNIQUE’s exactly-once option. With values in A2:A100, =UNIQUE(A2:A100,FALSE,TRUE) returns entries occurring one time only. It does not keep one copy of values that were repeated.
Can I use this formula in older Excel versions?
FILTER and UNIQUE require Microsoft 365 or Excel 2021 or later. In versions without dynamic-array support, use Data > Remove Duplicates for a one-time cleanup, or use an alternative such as Advanced Filter, depending on whether you need a copied list or a permanent edit.
Related
- Excel Data Validation List: Can You Pick Multiple?
- Excel Data Validation Lists: Can They Include Formatting?
- Excel Data Validation Lists: Hard-Coded or Flexible?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- INDIRECT Excel Formula for a Different Sheet: ExamplesIf cell A1 contains a worksheet name and you want the value in B2 on that sheet, use…
- Excel Filter Duplicates: Is Matching Case-Sensitive?No. In Excel for Microsoft 365 on Windows, the regular filter and built-in duplicate tools do not distinguish…
- Excel SUMIF After a Date: Formula and ExamplesTo sum values for dates strictly after a cutoff in Excel, use =SUMIF(A2:A100,">"&DATE(2024,1,1),B2:B100), where column A contains dates…
- MATCH Excel String: Find Exact Text MatchesTo find an exact text string in Excel, use MATCH with its third argument set to 0. With…
- Excel Formula Bar: What It Shows and How to Use ItIn Excel, the Formula Bar is the field above the worksheet that shows the contents of the selected…
- Excel VLOOKUP Examples With a Drop-Down ListTo make a drop-down selection drive a lookup in Excel for Microsoft 365 on Windows, create a list…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.