Excel’s FILTER function does not remove duplicates by itself; combine it with UNIQUE to return distinct rows that meet a condition. With records in A2:C100 and regions in B2:B100, this formula returns the distinct rows for West: =UNIQUE(FILTER(A2:C100,B2:B100="West","No matching rows")).
What FILTER and UNIQUE Each Do
FILTER selects records that satisfy a condition. It does not compare the selected records to one another, so repeated rows in the source remain repeated in the result. UNIQUE does the comparison: it returns distinct values or rows from the array it receives. Used together, FILTER narrows the data first and UNIQUE removes repeated entries from that filtered result.
This distinction matters when a workbook has a list of transactions, customers, products, or locations. If the goal is to see every West-region transaction, even when two rows happen to match, use FILTER alone. If the goal is a list of distinct West-region records, wrap FILTER in UNIQUE. The two functions solve different steps of the task rather than acting as interchangeable filters.
FILTER and UNIQUE are available in Microsoft 365 and Excel 2021 or later. Their results are dynamic arrays: Excel places the returned values in the formula cell and spills them into adjacent empty cells. You generally enter the formula once, rather than copying it down as you would with a traditional row-by-row formula.
How to Filter and Deduplicate Rows
In the opening example, columns A:C contain each complete record and column B contains the region. The condition B2:B100=”West” selects the matching records, and UNIQUE then compares the returned rows. Because the array supplied to UNIQUE has three columns, it treats a row as a duplicate only when all three values match the corresponding values in another row.
To adapt the example, replace the source range with the columns you want returned and change the condition to match your data. For example, if column C contains a status and you want distinct records marked Open, use the following formula. This assumes records are in A2:C100 and statuses are in C2:C100: =UNIQUE(FILTER(A2:C100,C2:C100="Open","No matching rows")).
When more than one condition must be true, multiply the condition arrays. With records in A2:C100, regions in B2:B100, and statuses in C2:C100, this returns distinct West-region records with an Open status: =UNIQUE(FILTER(A2:C100,(B2:B100="West")*(C2:C100="Open"),"No matching rows")). In Excel formulas, multiplying Boolean conditions works as an AND: both comparisons must be true for a row to be included.
For an OR condition, add the Boolean conditions instead. With regions in B2:B100, this returns distinct rows from either West or East: =UNIQUE(FILTER(A2:C100,(B2:B100="West")+(B2:B100="East"),"No matching rows")). A row matching both conditions is still included once by FILTER; UNIQUE also prevents repeated complete rows in the returned list.
Choose What Counts as a Duplicate
Before choosing a formula, decide what “duplicate” means for your data. UNIQUE applied to A2:C100 compares complete rows. If two records have the same customer ID but different names, dates, or amounts in the other columns, they are not duplicate rows by that definition.
Sometimes a single column is the key. To list distinct customer IDs from a filtered group, pass only the ID column to UNIQUE. With IDs in A2:A100 and regions in B2:B100, this returns distinct IDs from the West region: =UNIQUE(FILTER(A2:A100,B2:B100="West","No matching IDs")). The result is a list of identifiers, not complete customer records.
Returning one complete record for each distinct key is a different task. UNIQUE cannot be told to compare only column A while automatically returning the other columns from the first matching row. A practical approach is to create a helper column that flags the first occurrence of each key, then use FILTER to return flagged records. Make the rule explicit: for example, keep the earliest row in the current worksheet order, or sort by date first if the newest or oldest record should win. Without a defined rule, “keep one” can produce an arbitrary-looking result.
There is also a meaningful difference between “distinct” and “appears exactly once.” UNIQUE normally returns one instance of a value or row when it occurs one or more times. Its optional third argument, exactly_once, can instead return only items that occur exactly once. With values in A2:A100, this formula returns entries occurring once and omits values repeated two or more times: =UNIQUE(A2:A100,FALSE,TRUE). This is useful for finding one-off values, but it is not the usual way to create a de-duplicated list.
Exclude Blank Rows and Handle No Matches
A common surprise is that a source range includes empty rows. If the formula filters a large fixed range, blank records may appear in the result. Add a nonblank test to the include condition. With complete records in A2:C100, region in B2:B100, and the record’s first column in A, this keeps nonblank West-region rows and returns distinct records: =UNIQUE(FILTER(A2:C100,(B2:B100="West")*(A2:A100<>""),"No matching rows")).
The test in column A assumes every real record has a value there. If that column can legitimately be blank, test a different column that must be populated, such as a record ID. Otherwise, valid rows could be excluded. The text supplied as FILTER’s third argument appears when no rows meet the condition; it prevents the usual no-results error from appearing. Choose wording that makes sense for the report, or use an empty string if a blank result is preferable.
Criteria must also match the data as stored. A cell that looks like a date may contain a real date serial number or text, and numbers stored as text may not behave like numeric values in every comparison. Extra spaces in imported text can make apparently identical categories differ. If expected rows are missing, inspect the underlying values and clean inconsistent data rather than assuming UNIQUE or FILTER has changed the records.
Use the Formula in Microsoft 365
On Excel for Windows, enter the formula in a blank cell outside the source data and press Enter. The results spill down and across as needed. Keep the cells in that spill area empty; if any cell is occupied, Excel displays a #SPILL! error because it cannot place the complete result. Clear the obstructing cells or move the formula to a larger empty area.
When the source is an Excel Table, new rows are easier to include than with a fixed range, but a dynamic-array result should be placed outside the table. Excel does not support spilled array formulas inside table cells in the same way it does in a worksheet range. You can still use a table as the formula’s source, then let the result spill into ordinary worksheet cells. On Mac and Excel for the web, dynamic-array formulas work in supported current versions as well; the interface may differ slightly, but the formula logic is the same.
A spilled result updates when its source data changes. That makes this method useful for live summaries and reports, but it also means the result is not a separate, frozen copy. If you need a static list, copy the results and use Paste Special > Values, or use the Data tab’s Remove Duplicates command on a copy of the source data.
Formula Results Compared with Remove Duplicates
The formula approach is non-destructive: it leaves the source list in place and creates a separate result that can recalculate. This is usually the better choice when source data is refreshed, when the original must remain intact, or when a report should stay in sync with changing criteria.
To permanently remove repeated rows from a selected range, use Excel’s built-in command. On Windows, select the data and choose Data > Remove Duplicates. Confirm whether the data has headers, then select the columns Excel should use when deciding that rows are duplicates. Excel removes duplicate records from the selected data and keeps one occurrence. Save a copy first if the original matters: the command changes the data directly, although you can normally undo it immediately with Ctrl+Z. On Mac, the command is also on the Data tab; use Command+Z to undo.
The columns you select define the comparison. Selecting every column removes only rows that match across all selected fields. Selecting just Customer ID can treat different rows with the same ID as duplicates, keeping one of them and removing the others. This can discard meaningful differences, such as separate orders for the same customer, so inspect the selection carefully. If you want a repeatable cleanup that can be refreshed, consider Power Query rather than manually changing the source each time.
Common Problems and Misconceptions
- Expecting FILTER to deduplicate: FILTER returns all matching rows. Add UNIQUE around it when distinct results are required.
- Comparing the wrong columns: UNIQUE compares every column in the array you pass. Use a key-only array for a distinct key list, or plan a helper-column method if you need one full record per key.
- Getting #SPILL!: Clear cells blocking the output range, including cells that look blank but contain spaces or formulas.
- Seeing a no-results error: Supply FILTER’s optional third argument, such as “No matching rows”, to display a useful message when the condition matches nothing.
- Seeing apparent duplicates remain: Check whether the records actually match in every included column. Trailing spaces, different dates, or different amounts mean the rows are not identical.
- Trying the formula in an older Excel version: FILTER and UNIQUE are not available in some older perpetual versions. Use Remove Duplicates or Power Query instead, or open the workbook in a supported version.
When Power Query Is a Better Fit
Power Query is useful when a recurring process needs to import, clean, filter, and deduplicate data before loading a result. It records transformation steps, so you can refresh the query when the source changes rather than rebuilding the cleanup by hand. In Excel for Windows, open the Data tab and use the Get & Transform Data tools to create or edit a query; available commands depend on how the source data is brought in.
In the Power Query Editor, select the column or columns that define a duplicate and use Home > Remove Rows > Remove Duplicates. Selecting several columns makes the combination of those fields the comparison key. This is conceptually similar to selecting comparison columns in the worksheet’s Remove Duplicates dialog, but the transformation is saved as part of the query. Power Query is more setup than a one-cell formula, yet it is often a better fit for repeated imports or larger cleanup workflows.
Frequently Asked Questions
Does Excel FILTER remove duplicates automatically?
No. FILTER returns every row that meets its condition, including repeated rows. Wrap it in UNIQUE when you want distinct results, for example =UNIQUE(FILTER(A2:C100,B2:B100="West","No matching rows")).
Does UNIQUE remove duplicate rows or just duplicate cells?
It depends on the array you provide. If the array has multiple columns, UNIQUE compares rows across those columns. If you pass one column, it returns distinct values from that column.
How do I keep one row for each customer ID?
First decide which row should be kept, such as the first row in the current order or the most recent order. UNIQUE on the full table compares complete rows, not just the customer ID, so use a helper flag or a Power Query transformation when you need one complete record per key.
Why does my formula return #SPILL!?
Excel cannot place the dynamic result because one or more cells in its output area are occupied. Clear those cells or move the formula somewhere with enough empty space.
Can I remove duplicates without changing the original data?
Yes. Use a UNIQUE and FILTER formula to create a separate, updating result, or duplicate the source before using Data > Remove Duplicates. A formula result is dynamic; the Remove Duplicates command edits the selected data.
Related
- Excel Data Validation List: Can You Select Multiple?
- Excel Data Validation List: Remove Duplicates
- Excel Data Validation List Search: Setup and Fixes Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel SUM Based on Another Column: Formulas and ExamplesTo sum numbers in Excel according to a value in another column, use SUMIF: with categories in A2:A5…
- Excel Formula to Calculate Days Between Dates, ExplainedTo calculate elapsed days between two dates in Excel, subtract the earlier date from the later one. With…
- Excel TEXT Function Format Options and Examples ExplainedExcel’s TEXT function converts a number, date, or time into text displayed with a format code: use =TEXT(value,…
- Excel Equation to Remove Duplicates: Formulas & StepsIn Microsoft 365, use =UNIQUE(A2:A100) to return a list of distinct values from A2:A100. This formula leaves the…
- Excel Filter Shortcut: Turn Filters On and Open MenusIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn filter buttons on or off for a…
- Excel IFS Statement Examples and How to Use the FunctionExcel’s IFS function checks conditions in order and returns the result paired with the first condition that is…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.