Excel’s Remove Duplicates command does not remove both copies of a repeated entry: it keeps the first matching record and deletes later copies. To remove every row whose value appears more than once, add a helper formula such as =COUNTIF($A$2:$A$100,A2), filter for counts greater than 1, and remove or set aside those rows. The right method depends on whether you mean duplicate values in one column or duplicate records across several columns.
What Excel means by removing duplicates
People use “remove both duplicates” to mean two different things. Sometimes they want to remove the extra copy and keep one good record. Other times they want to delete every record involved in a repeated value, so no copy remains. Excel’s built-in command does the first, not the second.
For example, if a list contains Cedar, Maple, Cedar, the built-in command leaves one Cedar and one Maple. If the goal is to eliminate every name that occurs more than once, both Cedar rows must be identified and excluded, leaving only Maple. This distinction matters before you make changes: deleting all occurrences may remove legitimate information, especially when a repeated name or code belongs to separate transactions.
Also decide what counts as a duplicate. It may mean the same value in one column, such as an email address, or an identical record across several columns, such as matching name, date, and amount. Your choice determines which columns to check.
Use Remove Duplicates when one copy should remain
In Excel for Microsoft 365 on Windows, select a cell in the data, then choose Data > Remove Duplicates. In the dialog, confirm whether the table has headers, select the columns that define a duplicate, and choose OK. Excel reports how many duplicate values or rows it removed and how many unique entries remain.
- Make a copy of the worksheet or workbook if the source data matters.
- Select the full table, including every column you want to keep together. Avoid selecting just one column if removing its duplicates would leave related columns misaligned.
- Open Data > Remove Duplicates.
- Check My data has headers if the first row contains column names.
- Select the columns Excel should compare, then choose OK.
If you select only Email, Excel treats rows with the same email as duplicates even if their names or dates differ. If you select Name, Email, and Date, records are duplicates only when all selected values match. A column you leave unchecked does not affect the comparison, though Excel keeps the other fields from the first matching row.
The command changes the selected data rather than producing a separate review list. On Excel for Mac, use the Data tab’s Remove Duplicates command and the same column-selection logic; the dialog may be arranged differently. Ctrl+Z on Windows, or Command+Z on Mac, can undo the change immediately, but keeping a backup is safer than relying on undo. Excel for the web’s available commands can vary, so use the desktop app if you cannot find the option in your ribbon.
Remove every occurrence of a repeated value with a helper column
For a one-column test, a helper formula counts how often each row’s value appears in the complete list. Assuming the values are in A2:A100, enter the following in B2 and fill it down through B100:
With the values to check in A2:A100 and the formula entered in B2, =COUNTIF($A$2:$A$100,A2) returns the number of times the value in A2 appears in the full range. For instance, if A2 contains Cedar and Cedar appears twice in A2:A100, the result in B2 is 2.
Now turn on filters with Data > Filter and filter the helper column to show values greater than 1. Every row for a repeated value will appear, including its first occurrence. That is the important difference from Remove Duplicates, which preserves one occurrence.
- To make a clean output without altering the source, filter the helper column to show 1, then copy the visible original rows to a new worksheet. Those are the values that occurred exactly once.
- To delete all repeated entries from the original, filter for counts greater than 1, carefully select the visible data rows, and delete the entire rows. Check that the filtered rows—not just cells in the helper column—are selected.
- Afterward, clear the filter and remove the helper column if it is no longer needed.
The copy-to-new-sheet approach is often safer: it preserves the original list and lets you compare the retained records before replacing anything. If there are blank cells in the checked range, COUNTIF will count blanks too. Decide whether blank rows should be excluded, and do not treat an empty value as a meaningful duplicate by accident.
Check duplicates across multiple columns
If a duplicate means that a combination of fields repeats—for example, the same customer and order date—count the combination rather than one field alone. Assuming customer names are in A2:A100 and dates are in B2:B100, enter the following in C2 and fill down to C100:
With customer names in A2:A100 and dates in B2:B100, =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2) counts rows with both the same customer and the same date. A result above 1 marks every row belonging to a repeated pair. Filter the helper column in the same way as the single-column example.
To define a full duplicate record, include each relevant column as another range-and-criteria pair in COUNTIFS. The ranges must cover the same number of rows. Conversely, if an email address alone identifies a duplicate even when names differ, count only the email column. A practical rule is to define the key fields first, before filtering or deleting anything.
Choose the comparison carefully
Small differences can affect which rows Excel considers the same. Remove Duplicates and COUNTIF comparisons generally do not distinguish uppercase from lowercase, so “LEE@example.com” and “lee@example.com” may be treated as matching. Leading or trailing spaces, however, can make text appear identical to a person while remaining different values to Excel. Nonprinting characters copied from websites can cause similar confusion.
Before removing records, inspect suspicious entries and standardize data if appropriate. For example, TRIM can help remove ordinary extra spaces in a separate cleanup column, but do not overwrite original values until you have checked the results. Dates can also look alike while containing different underlying values or times. If two timestamps display the same date but include different times, they are not necessarily identical for a comparison that includes the timestamp column.
Sorting affects which record the built-in command keeps: it retains the first occurrence in the current order. If one row has a more complete address or a newer status, sort or otherwise arrange the data so the preferred record appears first, then use Remove Duplicates. Keep a backup because the command does not ask you to choose the best row for each match.
Common mistakes and safer checks
- Removing duplicates from only part of a table: Selecting one column and deleting duplicates can separate values from their related row details. Work with the complete table.
- Selecting the wrong comparison columns: Including a unique ID can make every row appear unique; excluding an important field can merge records that should stay separate.
- Assuming both copies will disappear: The built-in command keeps one. Use a count-and-filter method when all occurrences must go.
- Deleting without reviewing the result: Copy the original sheet or save a separate workbook before a bulk operation. Verify row counts and a few known examples afterward.
- Forgetting that filtered rows are hidden, not removed: Filtering only changes what is displayed. Copy the visible unique rows to a new sheet or deliberately delete the visible matching rows if that is the intended result.
For repeated work, Power Query can provide a refreshable workflow. Use Data > From Table/Range to load a table, then use the Power Query Editor to group by the key columns and count rows. Retaining groups whose count is 1 identifies keys that occur only once. If you need to preserve all the original columns when grouping by only a few key fields, plan how to bring the matching records back into the result; grouping only by keys does not automatically preserve every detail column. Power Query is useful for recurring imports, but a helper column is usually easier for a quick one-time check.
When to use each approach
Use Data > Remove Duplicates when each repeated group should collapse to one row. It is quick and built into Excel, but changes the data in place and keeps the first row. Use a COUNTIF helper when repeated values must all be removed or reviewed, because it labels every occurrence and makes the rule visible. Use COUNTIFS when duplicates are defined by a combination of columns.
For a large recurring cleanup, consider Power Query so the transformation can be refreshed as new data arrives. For a small list where accuracy matters more than speed, create a copied worksheet, add the count, filter, and inspect the outcome before changing the source. There is no single best definition of duplicate: it depends on the meaning of the data, not merely on whether two cells look alike.
Frequently Asked Questions
Does Remove Duplicates delete both copies in Excel?
No. It keeps the first matching row and removes later copies. To remove every row for a repeated value, count occurrences with COUNTIF or COUNTIFS and filter for counts greater than 1.
How do I keep only values that appear once?
Add a COUNTIF helper column, filter its results to 1, and copy the visible original rows to a new worksheet. This keeps the source intact while excluding every value that occurs multiple times.
Can I remove duplicates based on just one column?
Yes. In the Remove Duplicates dialog, select only the column that defines a match. Excel then keeps one row for each value in that column, including the other fields from the first matching row.
Why does Excel not recognize two entries as duplicates?
They may contain extra spaces, hidden characters, different dates or times, or a difference in a column included in the comparison. Check the underlying values and the selected columns; displayed formatting alone does not guarantee that stored values match.
Can I undo Remove Duplicates?
Use Ctrl+Z on Windows or Command+Z on Mac immediately after the operation. Because later actions can make recovery harder, save a copy before cleaning important data.
Related
- Import Multiple CSV Files into Excel: Fast Methods
- How to Import CSV to Excel: Steps and Common Fixes
- PKSolver in Excel: What It Does and How to Use It Safely
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Date as Number: Excel Serial Dates ExplainedYes. Excel stores dates as serial numbers: the whole-number part counts days, and any decimal part represents a…
- Mail Merge in Word: A Step-by-Step Guide for LettersTo mail merge in Word, prepare a recipient list, connect it to a Word document, insert fields such…
- Microsoft PowerPoint Loop Slideshow: Setup and FixesYes. In the PowerPoint desktop app, open Slide Show > Set Up Slide Show, select Loop continuously until…
- Excel RANK vs RANK.EQ: Differences, Ties, and ExamplesRANK and RANK.EQ return the same result in current Excel: each gives a number’s position in a list,…
- Delete Row in Excel: Methods and Keyboard ShortcutsTo delete a row in Excel for Microsoft 365 on Windows, click its numbered row heading, right-click, and…
- Excel Find and Replace in Selection: Steps and TipsTo use Find and Replace only in selected cells in Excel for Microsoft 365 on Windows, select a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.