Duplicate Filter in Google Sheets: Find Repeated Rows

To filter duplicates in Google Sheets, add a helper column with a COUNTIF formula, then filter that column to show TRUE. For repeated values in column A, put =AND(A2<>"",COUNTIF($A$2:$A,A2)>1) in B2, fill it down, and use Data > Create a filter to show only TRUE rows. This flags repeats without deleting anything.

What a Duplicate Filter Does

A duplicate filter is a way to display records that share a value, while leaving the underlying data in the sheet. Google Sheets does not have a single menu command called “filter duplicates” that automatically finds and displays every repeated row. Instead, you can identify them with conditional formatting or a formula, then use the normal filter controls to isolate the matches.

That distinction matters. A filter hides rows that do not meet its conditions; it does not remove them. You can clear the filter to see the complete list again. If your goal is to permanently remove repeated records, use the separate Remove duplicates tool, and review its options before confirming.

People may also mean different things by “duplicate.” They might want to flag repeated email addresses, show every occurrence of a repeated invoice number, or find rows that are identical across several columns. Choose the method and comparison fields to match the question you are trying to answer.

Find Repeated Values in One Column

For a simple list, a helper column is usually the clearest way to create a filterable result. The example below assumes the values to check are in A2:A, with a header in A1; column B is available for the helper formula.

Enter a heading such as “Duplicate?” in B1.
In B2, enter =AND(A2<>"",COUNTIF($A$2:$A,A2)>1).
Copy or fill the formula down alongside your records. The result is TRUE for a nonblank value that occurs more than once, and FALSE otherwise.
Select a cell in the data range and choose Data > Create a filter.
Open the filter dropdown in B1, clear the current selections if needed, and select TRUE. The visible records now have values in column A that appear more than once.

For example, if A2:A5 contains “North,” “South,” “North,” and “West,” the helper returns TRUE, FALSE, TRUE, and FALSE. Filtering to TRUE displays both North records. It does not decide which one is the original and which one should be deleted.

The formula counts each value against the full column, not just the rows above it. That is why both occurrences of a repeated value are flagged. If you instead need to mark only the second and later occurrences, use a running count. Assuming the list starts in A2, enter =AND(A2<>"",COUNTIF($A$2:A2,A2)>1) in B2 and fill down. In the North example, the first North is FALSE and the second is TRUE.

Filter Rows That Match Across Several Columns

Sometimes a value alone is not enough to define a duplicate. Two customers can share a surname, for instance, while having different email addresses. To flag rows only when the values in columns A, B, and C all match, put this formula in D2, assuming row 1 contains headers and the records begin in row 2:

=AND(A2<>"",COUNTIFS($A$2:$A,A2,$B$2:$B,B2,$C$2:$C,C2)>1)

Fill the formula down and filter the helper column to TRUE. COUNTIFS checks the criteria as a group: a row is flagged only when the A, B, and C values together occur in another row. This is useful for combinations such as date, product code, and location, where no single field is unique by itself.

Decide which columns define a duplicate before applying the formula. If you include a timestamp that differs by a few seconds, records that otherwise look identical will not count as duplicates. If you leave out an important identifier, distinct records may be flagged together. A “duplicate row” is not an absolute category; it depends on the fields you choose to compare.

Highlight Duplicates Without Filtering

If you only need to spot repeats visually, conditional formatting can color them without adding a helper column. For a list in A2:A, select the range and choose Format > Conditional formatting. Under “Format cells if,” choose “Custom formula is,” enter =AND(A2<>"",COUNTIF($A$2:$A,A2)>1), choose a formatting style, and select Done.

The formula’s row reference adjusts for each cell in the selected range, while the counted range stays anchored to column A. If you apply the rule to multiple columns, make sure the reference matches the first cell in the selected range. Conditional formatting is useful for review, but color alone is not a reliable filter condition: for a filterable TRUE/FALSE result, use a helper column.

Filter the Results and Keep the Data Organized

After adding a helper formula, create a filter over the full table, including its headers and helper column. The filter dropdown on the helper heading lets you show TRUE, FALSE, or both. Keep each record on one row and include all columns in the filtered range; filtering only the helper column can make the displayed information confusing or separate it from the corresponding records.

For a temporary view that should not change what collaborators see, use Data > Filter views > Create new filter view. Filter views can be useful in a shared spreadsheet because each person can work with a view of the data rather than changing the active filter for everyone. Give the view a descriptive name, such as “Repeated email addresses,” so its purpose is clear later.

When you finish, clear the filter or close the filter view to return to the full dataset. The formulas remain in the helper column until you remove them. If you add new records later, make sure the formula is copied into the new rows; otherwise those rows will not receive a duplicate flag. An open-ended range such as $A$2:$A includes later entries in the count, but it does not automatically place a formula in every newly added helper cell.

Remove Duplicates Instead of Filtering

If you want to delete repeated entries rather than inspect them, make a copy of the data first. Select the relevant range, then choose Data > Data cleanup > Remove duplicates. In the dialog, indicate whether the range has a header row and select the columns Sheets should use when deciding whether rows are duplicates. Review the selection carefully before confirming.

Choosing every column means rows are considered duplicates only when the selected values match across all those columns. Choosing just an email column means rows sharing that email can be treated as duplicates even if other fields differ. That may be appropriate for a mailing list, but it could also discard meaningful information, such as two transactions made by the same person. The tool changes the data; unlike a filter, it is not just a temporary view. Keep a backup when the original records matter.

Common Issues and Important Limitations

Blank cells: The AND condition in the examples prevents empty cells from being flagged as repeated values. If blank entries are meaningful records in your workflow, adjust that condition deliberately.
Extra spaces: “alex@example.com” and “alex@example.com ” may appear alike but contain different text. Clean inconsistent spaces before relying on duplicate checks; TRIM can help create a cleaned comparison value in a separate column.
Capitalization: COUNTIF is generally case-insensitive, so values that differ only by letter case may be treated as matches. If case-sensitive comparisons are essential, use a different approach and verify it against representative data.
Wildcard characters: COUNTIF treats an asterisk and question mark in criteria as wildcard characters. If your values contain those symbols and need literal matching, test the results carefully rather than assuming every match is an exact character-for-character comparison.
Filtered ranges: Make sure the filter includes the whole table and the helper heading. A filter applied to only part of the data can make rows appear misaligned or omit relevant fields.
Formula fill: A correct formula in B2 does not help rows where column B is empty. Fill it through the existing records and extend it when adding more rows.

Before acting on a duplicate flag, inspect the underlying values and the rule used to compare them. A repeated name may be legitimate, while a repeated unique ID may indicate an import problem. Filtering identifies records according to a rule; it cannot determine whether a repeat is an error in your particular workflow.

Google Sheets Compared with Excel

The core approach is similar in Excel: a COUNTIF helper column can identify repeated values, and filters can display the TRUE results. The instructions here use Google Sheets’ web-browser menus, including Data > Create a filter and Format > Conditional formatting. Excel has its own ribbon layout and duplicate-removal commands, so do not assume the menu paths are identical.

Google Sheets also offers Remove duplicates under Data > Data cleanup, while Excel’s Remove Duplicates command is on its ribbon. In either program, the important choice is which columns define a duplicate. If a sheet is shared with people using Excel, formula results and basic functions such as COUNTIF are familiar, but formatting, filters, and menu locations may look different.

Frequently Asked Questions

Does Google Sheets have a built-in duplicate filter?
There is no dedicated menu item that automatically filters duplicates. Add a helper formula such as COUNTIF, create a filter, and show TRUE results. Conditional formatting can highlight repeats if you do not need to filter them.

How do I show only the second and later occurrences?
With values in A2:A, put =AND(A2<>"",COUNTIF($A$2:A2,A2)>1) in B2 and fill it down. Filter column B to TRUE. The first occurrence remains FALSE; later occurrences are TRUE.

Will filtering duplicates delete any rows?
No. A filter temporarily hides rows that do not match the selected condition. Use Data > Data cleanup > Remove duplicates only when you intend to remove records, and consider preserving a copy first.

Can I find duplicate rows using more than one column?
Yes. Use COUNTIFS in a helper column and include each comparison column as a range-and-value pair. The row is flagged when that complete combination appears more than once.

Why are values that look identical not being flagged?
Check for trailing spaces, hidden characters, inconsistent data types, and differences in the columns used for the comparison. Also confirm that the formula is filled down and that the filter includes the helper column.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.