Excel Keep Duplicates: Remove Unique Rows

To keep every row whose value appears more than once, add a helper formula such as =COUNTIF($A$2:$A$8,A2)>1, fill it down, and filter for TRUE; in Microsoft 365, =FILTER(A2:A8,COUNTIF(A2:A8,A2:A8)>1,"No duplicates") returns those rows in a separate range. Excel’s Remove Duplicates command does something different: it removes repeated copies and keeps one instance, so it does not remove values that occur only once.

What “keep duplicates, remove unique” means

The phrase can describe two different cleanup jobs. You might want to keep all occurrences of values that repeat and exclude values that appear just once. Or you might want a list containing one copy of each value, removing extra repetitions. These are opposite operations, and choosing the wrong one can discard useful records.

For example, imagine a list of seven names: Cedar, Maple, Cedar, Birch, Pine, Maple, and Ash. Cedar and Maple each occur twice; the other names occur once. Keeping duplicates while removing unique values produces Cedar, Maple, Cedar, Maple. Removing duplicate copies instead produces Cedar, Maple, Birch, Pine, and Ash—one copy of each distinct name.

Keep repeated records: retain every occurrence of a value that appears at least twice.
Keep one of each value: remove extra copies while retaining one instance, including values that appeared only once.
Delete unique records: remove every row whose identifying value occurs just once.

Before editing, decide which result you need and what makes two records “the same.” A repeated customer ID may matter even when the customer’s address differs; a duplicate invoice might mean every column in the row is identical. The right comparison key determines the result.

Use a helper column to identify repeated values

A helper column is a transparent way to mark records without immediately changing or deleting the source data. The following example assumes names or IDs are in A2:A8, with a header in A1. Enter the formula in B2, then fill it down to B8:

With values in A2:A8 and the current row’s value in A2, use =COUNTIF($A$2:$A$8,A2)>1. The result is TRUE for Cedar and Maple and FALSE for Birch, Pine, and Ash. Because the formula is copied down, the first range stays fixed while the reference to the current row changes.

To see only repeated values, turn on filtering and filter column B to TRUE. On Excel for Microsoft 365 on Windows, select a cell in the data and choose Data > Filter, then use the dropdown in the helper-column header. If the range is an Excel table, use its existing header filters. Copy the visible matching rows to a new sheet or location if you need a separate results list.

To identify values that occur just once instead, use the same test with equals one. With the same A2:A8 layout, enter =COUNTIF($A$2:$A$8,A2)=1 in B2 and fill it down. TRUE now marks Birch, Pine, and Ash. Filtering those rows lets you review unique records before deciding whether to remove them.

Return matching rows with FILTER

If you have Microsoft 365 or Excel 2021, FILTER can return results to a separate area and update them as the source data changes. It does not overwrite the original list. Leave enough empty cells below the formula for the results to spill; otherwise Excel can show a spill error.

With the names in A2:A8, enter =FILTER(A2:A8,COUNTIF(A2:A8,A2:A8)>1,"No duplicates") in an empty cell. For the example list, the result is four entries: Cedar, Maple, Cedar, and Maple. Each occurrence is included because both occurrences meet the condition. The optional message appears if no values repeat.

To return the values that occur once, change the test to equals one. With the same list in A2:A8, use =FILTER(A2:A8,COUNTIF(A2:A8,A2:A8)=1,"No unique values"). The result is Birch, Pine, and Ash. These formulas are useful for review and reporting; they do not delete records from the source.

For a table with more than one column, filter the full rows using the column that defines a duplicate. If the data is in A2:C100 and column A contains the customer ID, use =FILTER(A2:C100,COUNTIF(A2:A100,A2:A100)>1,"No repeated IDs"). This returns every row whose ID occurs more than once, including rows where other columns differ. FILTER and dynamic-array formulas are available in Microsoft 365 and Excel 2021; older Excel versions can use the helper-column and filter approach.

Choose the right columns for a duplicate test

Excel needs a rule for deciding whether records match. If you choose only the ID column, two rows with the same ID count as repeats even if their dates, amounts, or descriptions differ. That may be exactly right for finding repeated transactions, or it may flag legitimate multiple transactions belonging to the same person.

If a duplicate means that every field in a row must match, create a helper test that checks each relevant column. For data in A2:C100, with a possible duplicate row on row 2, enter the following in D2 and fill down:

With the complete records in columns A, B, and C, use =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2,$C$2:$C$100,C2)>1. TRUE means that the combination of values across all three columns occurs more than once. Filter for TRUE to inspect repeated full rows. Add another range-and-criteria pair if another column should be part of the comparison.

For the FILTER approach, the condition must likewise count matching combinations. A helper column is often easier to understand and audit for multiple fields, especially in a large workbook. It also makes the duplicate rule visible to anyone reviewing the sheet.

Remove unique rows without confusing the commands

There is no single Remove Duplicates button setting that means “keep every repeated occurrence and delete only the one-off values.” The built-in command is designed to remove extra copies. To remove unique rows, first identify them with a count formula or FILTER, review the results, then delete or move those records deliberately.

A cautious workflow is to add the COUNTIF helper, filter for FALSE when the formula tests whether the count is greater than one, and inspect the visible rows. Copy the repeated rows to a new sheet if that is the desired final dataset. If you intend to delete the unique rows from the original, make a backup first and confirm that the selected range includes entire records—not just the values in one column. Deleting cells in a single column can misalign the remaining fields.

By contrast, to keep one copy of each value, select the data and choose Data > Remove Duplicates. In the dialog, select the columns that define a duplicate. If all columns are selected, Excel compares complete rows; if only the ID column is selected, it treats repeated IDs as duplicates. Excel keeps the first occurrence it encounters and removes later matches, so sort first if a particular record should be the one retained. This command changes the data, so keep a copy or use Undo immediately if the outcome is wrong.

Common issues that change the result

Case differences: COUNTIF is not case-sensitive, so “ACME” and “Acme” are treated as matching text. If capitalization distinguishes codes in your workflow, a different, case-sensitive comparison is needed.
Spaces and hidden characters: “Maple” and “Maple ” may not compare as expected. Imported data can contain leading or trailing spaces or nonprinting characters. Clean or standardize a copy before counting, rather than changing original records without review.
Wildcard characters: COUNTIF interprets * and ? as wildcard characters in criteria. If those symbols occur literally in IDs or text, the count may not behave like a simple exact-text match. Test such data carefully or use a comparison method designed for literal matching.
Blank cells: Blank entries can be counted as a repeated value, making empty rows appear to be duplicates. Add a nonblank condition or filter out empty IDs before interpreting the results.
Numbers stored as text: A number and a text value that looks like the same number may not behave alike in every operation. Standardize imported columns where possible, and check the cell format and underlying values when a result seems surprising.
Formula results: Cells that display the same text may contain different formulas or values, while different formulas may return identical results. Decide whether you are comparing displayed results or underlying data before cleaning.

Also check whether headers are included in the selected range. In Remove Duplicates, use the option indicating that the data has headers when appropriate. A header accidentally treated as a record can produce confusing results, and selecting too few columns can make distinct rows look identical under the chosen duplicate rule.

Which method should you use?

Use a helper column when you need to remove one-off records, explain the decision, or work in an older Excel edition. Use FILTER when you want a live, separate result that refreshes as source values change. Use Data > Remove Duplicates only when the goal is to remove extra copies and retain one instance of each record or key.

For an auditable cleanup: make a backup, add a count-based helper, filter and review, then copy or delete complete rows.
For a changing report: use FILTER and keep the original data untouched.
For a one-time distinct list: use Remove Duplicates, or use the UNIQUE function if you want a formula-driven result in Microsoft 365 or Excel 2021.

UNIQUE is not the same as “keep only values that repeat.” It returns distinct values by default, generally one of each, including values that appear once. For the sample names in A2:A8, =UNIQUE(A2:A8) returns Cedar, Maple, Birch, Pine, and Ash. It is suitable for a distinct list, not for excluding one-off values.

Frequently Asked Questions

Does Remove Duplicates keep the repeated rows?
It keeps one occurrence and removes later matching copies, based on the columns selected in the dialog. It does not preserve every occurrence of repeated values while removing values that occur once.

How do I keep all repeated values in Excel?
Use a helper formula such as =COUNTIF($A$2:$A$100,A2)>1, fill it down, and filter for TRUE. In Microsoft 365 or Excel 2021, FILTER can return the matching rows to another range.

How do I delete values that appear only once?
Mark them with a count-equals-one helper formula, review the filtered records, and delete or move entire rows only after confirming the result. Keep a backup before making a permanent cleanup.

Does UNIQUE remove values that occur just once?
No. UNIQUE returns one copy of each distinct value, including values that appeared only once. Use a count test greater than one when you need only values that repeat.

Can Excel detect duplicates across a whole row?
Yes. In Remove Duplicates, select all columns that define an identical record. With formulas, COUNTIFS can count rows matching the values in each selected column; a match on one ID column alone is a different rule.

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.