Excel FILTER Function for Duplicates: Find Repeats

Formulas & functions · Updated November 19, 2026

With IDs in A2:A100 and full records in A2:D100, enter =FILTER(A2:D100,COUNTIF(A2:A100,A2:A100)>1,"No duplicates") to return every row whose ID appears more than once. FILTER is available in Microsoft 365 and Excel 2021 or later; the result spills into nearby cells without changing the source data.

What the duplicate formula returns

The formula checks each value in A2:A100 against the entire ID range. COUNTIF returns the number of times each ID occurs, and the test >1 marks IDs that appear at least twice. FILTER then returns the corresponding rows from A2:D100. If an ID occurs three times, all three matching rows appear—not just the second and third occurrences.

The final argument, “No duplicates”, is the message shown if nothing meets the condition. It is optional, but useful: if you leave it out and no rows qualify, Excel returns a #CALC! error instead. The returned rows keep their original order, and the source list remains intact.

This formula identifies duplicates by one key column, such as an ID, email address, or product code. It does not require every cell in a row to match. That distinction matters: two orders with the same customer ID might be different orders, while two rows with the same ID, date, and amount might be duplicate records.

Exclude blank cells from the results

COUNTIF counts blank cells too. If several cells in the ID column are empty, the basic formula can treat those blanks as repeated values and return their rows. To ignore empty IDs, add a nonblank test to the include condition.

With IDs in A2:A100 and records in A2:D100, use =FILTER(A2:D100,(A2:A100<>"")*(COUNTIF(A2:A100,A2:A100)>1),"No duplicates"). The comparison A2:A100<>”” produces TRUE for nonblank cells; multiplying the two tests works as an AND for the arrays. A row is included only when its ID is not blank and its count exceeds one.

If blank IDs are meaningful in your data, do not exclude them automatically. Decide whether empty cells represent missing information, a valid shared category, or records that should be corrected before analysis.

Return only the repeated values

Sometimes the goal is a compact list of which values repeat, rather than every full record. Wrap FILTER in UNIQUE to show each repeated ID once.

With IDs in A2:A100, use =UNIQUE(FILTER(A2:A100,(A2:A100<>"")*(COUNTIF(A2:A100,A2:A100)>1),"No duplicates")). If IDs 104, 208, and 104 occur in the list, the result contains 104 once and 208 only if it also occurs more than once. The nonblank condition prevents empty cells from appearing as a repeated value.

UNIQUE and FILTER both return dynamic arrays, so leave enough empty cells below the formula for the results to spill. If you need a count beside each repeated value, a pivot table or a separate COUNTIF calculation may be more convenient than trying to make the result list do both jobs.

Find duplicate rows across several columns

A repeated ID is not always a repeated record. To define a duplicate as a row where every value in A through D matches another row, use COUNTIFS in a helper column. This approach makes the comparison criteria visible and is straightforward to audit.

With records in A2:D100, put this in E2 and fill it down through E100: =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2,$C$2:$C$100,C2,$D$2:$D$100,D2). Each row gets a count of rows that match its values in all four columns. Then, with the same records in A2:D100 and those counts in E2:E100, enter =FILTER(A2:D100,E2:E100>1,"No duplicate rows") in an empty area. Every occurrence of an identical row is returned.

COUNTIFS treats matching blanks as criteria too. If some columns are supposed to be ignored, omit those columns from the COUNTIFS formula. If only certain fields define a duplicate—for example, email and date, but not a notes field—include only those fields in the comparison. Be consistent about what makes two records the same before removing anything.

How FILTER and COUNTIF work together

FILTER has the general structure =FILTER(array,include,[if_empty]). The array is the data to return; include is a TRUE/FALSE test of the same height or width; and if_empty provides an optional result when no values qualify. In the duplicate example, A2:D100 is the array, while COUNTIF(A2:A100,A2:A100)>1 supplies the include test.

COUNTIF normally counts cells in a range that meet one criterion. Here, the criterion argument is itself the range of IDs. In current dynamic-array Excel, that produces a count for each ID, which FILTER evaluates row by row. The formula is therefore a live view: editing an ID or adding data within its referenced range can change the returned list.

The range sizes must line up. If the returned array covers A2:D100 but the include condition covers only A2:A99, FILTER cannot match one test to each row. Use ranges with the same number of rows, and avoid including a header in one range but not the other.

Set it up in a worksheet

  1. Keep a header row above the data, and choose the column that defines a duplicate, such as Customer ID.
  2. Select an empty cell outside the source data, with clear space to its right and below for the results.
  3. Enter the FILTER formula, adjusting the row limits and column references to fit the actual data.
  4. Compare a few returned records with the source list to confirm the chosen key matches your definition of a duplicate.
  5. If you want a reusable report, format the source as an Excel table with Ctrl+T. A table expands as records are added; structured references can make formulas easier to read, though the formula must still use matching columns and criteria.

On Windows, Ctrl+T opens the Create Table dialog. On Mac, use Command+T. FILTER formulas work in Microsoft 365 for Windows, Mac, and Excel for the web; the formula logic is the same, although ribbon layouts can differ. FILTER is not available in older perpetual versions such as Excel 2019, so those users can use a helper column or the built-in duplicate tools instead.

Choose the right duplicate tool

FILTER is best when you want a dynamic, reviewable list that updates while preserving the original records. Other Excel features address different needs:

  • Conditional formatting: Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values highlights repeated cell values in place. It is useful for visual inspection, but does not create a separate list of complete matching rows.
  • Remove Duplicates: Data > Remove Duplicates permanently deletes repeated records from the selected range, keeping one occurrence. Make a copy first if you may need to restore the original information; this action changes the data rather than producing a live view.
  • Advanced Filter: Data > Advanced can copy filtered records to another location and offers options for unique records. It can help in older Excel versions, but is less dynamic than a FILTER formula.
  • PivotTable: A PivotTable can summarize counts by ID or other fields. It is useful for reporting how often values occur, rather than simply listing all repeated source rows.

On Excel for Mac or the web, ribbon placement and labels may vary slightly by release, but the worksheet formula remains the same. If the goal is to delete duplicates, use Remove Duplicates only after confirming which columns define a duplicate and retaining a backup when appropriate.

Common problems and how to fix them

  • #SPILL!: One or more cells where the formula needs to return results are occupied, merged, or otherwise blocked. Clear the output area and try again. Select the warning icon by the formula cell to inspect the obstruction.
  • Unexpected blank rows: The key column contains multiple blanks, which COUNTIF can count as repeated. Add the nonblank test shown above if empty keys should not count.
  • Rows that look alike but are not counted together: Text values can differ because of extra spaces, inconsistent punctuation, or different stored values. Check the source data and standardize it where needed; do not assume that visual similarity means Excel sees the values as identical.
  • Too many rows returned: FILTER returns every matching occurrence. If you wanted one row per repeated key, apply UNIQUE to a filtered key list, or use Remove Duplicates on a copy after deciding which occurrence to retain.
  • #NAME? or an unrecognized function: Check that the Excel version supports FILTER. Also check spelling and, if the workbook uses a regional setting with different list separators, use the separator expected by that installation. The formulas here use standard comma separators.
  • Formula entered inside a table: Dynamic-array formulas generally need to spill into neighboring worksheet cells, while table columns are designed to fill down. Put the FILTER formula in a normal empty cell outside the source table.

Frequently Asked Questions

Can Excel FILTER return only duplicates?
Yes. Use COUNTIF as the include test and set it to greater than 1. FILTER returns the rows that meet that test, including every occurrence of a repeated key.

Does FILTER remove duplicate rows from the source?
No. FILTER displays matching results elsewhere and leaves the source data unchanged. Use Data > Remove Duplicates if you intend to delete duplicate entries, ideally after saving a copy.

How do I find duplicates in one column but return the whole row?
Use the full record range as FILTER’s array and the key column in COUNTIF. For example, return A2:D100 while checking IDs in A2:A100. The two ranges must cover the same rows.

Can I find duplicates based on multiple columns?
Yes. Use COUNTIFS to count matches across the fields that define a duplicate, often in a helper column, and filter the records where the count is greater than 1. Include only the fields that should determine whether records match.

Why does my formula show #CALC! when there are no duplicates?
FILTER returns #CALC! when no rows match and its optional if_empty argument is omitted. Add a message such as “No duplicates” as the third argument to display a readable result instead.

Choosing a reliable definition

The formula is only as useful as the rule behind it. Decide whether a duplicate means the same ID, the same email address, or an exact match across several fields. Then choose FILTER for a live results list, conditional formatting for a quick visual check, or Remove Duplicates when the objective is to alter the source. Testing the rule on a small sample before acting on a large workbook helps avoid treating valid repeated information as an error.

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.