To clean duplicates in Excel for Microsoft 365 on Windows, select your data, choose Data > Remove Duplicates, select the columns that define a duplicate, and click OK. Make a copy first: Excel removes matching rows from the selected range or table, while Conditional Formatting can flag duplicates for review without deleting anything.
What counts as a duplicate in Excel?
A duplicate is a repeated value or record, but the right definition depends on the job. In a list of customer IDs, two rows with the same ID may be duplicates even if other fields differ. In an order table, the same customer can appear many times legitimately; you might only consider a row repeated if the order number, item, and date all match.
Decide which fields identify a repeated record before cleaning. For example, if a contact list has Name, Email, and Company columns, matching email addresses may be the best test. Names alone are often unreliable: two different people can share a name, and one person’s name can be entered in different ways.
- Repeated value: a cell, such as an email address, appears more than once.
- Repeated row: values in every relevant column match another row.
- Possible duplicate: records look similar but have differences, such as a spelling variation or extra space.
Excel’s built-in duplicate tools look for matching cell values, not whether two records are the same person or event in a broader sense. Choosing the comparison columns is therefore a data decision, not just a button-clicking decision.
Prepare the data before removing anything
Save a separate copy of the workbook, or duplicate the worksheet, before making a destructive change. Remove Duplicates usually can be reversed immediately with Ctrl+Z on Windows, but undo may no longer be available after other actions or after closing the workbook. A backup also gives you a reliable way to compare the original records.
Check the structure of the data before selecting the command. A single rectangular range with one header row is easier to inspect than a range with blank rows, merged cells, notes, or totals mixed into the records. If the data is a formal Excel table, click inside it and confirm the headers are clear. If it is a plain range, select the entire dataset, including all columns that should stay together.
- Keep each record on one row and each field in one column.
- Remove or separate subtotal and note rows so they are not treated as records.
- Check that the first row contains field names if Excel should treat it as headers.
- Decide whether blank cells are meaningful before treating rows with blanks as duplicates.
Sorting is not required for Remove Duplicates. However, if you want a particular version of a record to remain, sort the data first so the preferred version appears before the others. Excel retains the first occurrence it encounters and removes later matching records.
Remove duplicate rows with the Data tab
On Excel for Microsoft 365 for Windows, use this process when you have decided that matching records can be deleted:
- Select a cell in the table, or select the full data range. Selecting the full table helps ensure Excel removes complete records rather than leaving related fields out of step.
- Choose Data > Remove Duplicates.
- In the dialog, confirm whether My data has headers should be checked. Check it when the first row contains column names.
- Select the columns Excel should compare. Keep all relevant columns selected to identify fully repeated rows; select only the key field or fields when those define a duplicate.
- Click OK and read Excel’s result message, which reports how many duplicate values were removed and how many unique values remain.
For example, suppose a table has Customer ID, Name, and Region. If you select only Customer ID, two rows with the same ID count as duplicates even if the name or region differs. If you select all three columns, Excel removes a row only when the values in all three selected fields match another row. Selecting the wrong fields is a common reason a cleanup removes too much or too little.
On Excel for Mac, the Remove Duplicates command is also on the Data tab, although the ribbon layout can vary slightly by version. The undo shortcut is Command+Z on Mac; on Windows it is Ctrl+Z. If you use Excel for the web, check the Data tab for the available Remove Duplicates command, as the ribbon can differ from the desktop app.
Flag duplicates before deciding what to delete
When you want to inspect repeated values first, select the cells or column and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Choose a formatting style and confirm the dialog. This highlights repeated values in the selected cells; it does not remove rows or decide whether two full records should be treated as the same.
For a quick review of repeated email addresses, select the email column and apply the rule. Then filter or sort by that column and examine the matching records. If you need to flag complete rows based on a combination of fields, a helper formula can be more suitable than the built-in single-column highlighting rule.
Highlighting is a safer first move when records might contain useful differences. A repeated product code, for example, could represent two separate transactions. Inspect quantities, dates, and order numbers before deleting anything simply because one cell repeats.
Use formulas to mark first and later occurrences
A helper column can identify later repeats while leaving the source records in place. With IDs in A2:A6 and the helper formula entered in B2 and filled down, use =COUNTIF($A$2:A2,A2)>1. If the IDs are M-104, M-105, M-104, M-106, and M-105, the formula returns FALSE for the first occurrence of each ID and TRUE for the later occurrences in rows 4 and 6. Filter the helper column for TRUE to review those rows.
COUNTIF is not case-sensitive, so codes such as ab12 and AB12 are treated as matching. It also compares the values as Excel stores them; it does not automatically decide that similar-looking text with extra spaces or different punctuation is equivalent. Clean or standardize the fields first if those differences should not matter.
For a duplicate defined by a pair of fields, with names in A2:A20 and email addresses in B2:B20, enter this in C2 and fill down: =COUNTIFS($A$2:A2,A2,$B$2:B2,B2)>1. It returns TRUE only when that name-and-email combination has appeared earlier in the list. Change the criteria fields to match the columns that define a repeated record in your own data.
In Microsoft 365 or Excel 2021, the UNIQUE function can produce a separate list of distinct values. With source values in A2:A20 and an empty cell selected for the result, use =UNIQUE(A2:A20). Excel spills the distinct values into cells below the formula, leaving the source range unchanged. This is useful for creating a clean output list, but it does not delete duplicate source rows or preserve the context of other columns by itself.
Clean inconsistent values that look like duplicates
Some repeated-looking records are not exact matches. A cell might contain an accidental leading or trailing space, different punctuation, an inconsistent abbreviation, or a number stored as text in one row and as a number in another. Conversely, two entries that look alike on screen may contain different underlying values.
Inspect a sample of the flagged records before choosing a cleanup method. If extra spaces are the issue, a helper column using TRIM can remove ordinary leading and trailing spaces from text. For a source value in A2, enter =TRIM(A2) in B2 and fill down; for example, text with spaces around the word North is returned as North. Check the results before copying them back as values, because formulas should not silently replace source data you still need.
Standardization needs care. Replacing every hyphen, changing capitalization, or combining similar names can merge records that are genuinely different. If there is no dependable unique identifier, treat a match as a candidate for review rather than proof that one row is disposable.
Remove Duplicates, formulas, or Power Query?
The best method depends on whether you want a one-time edit, a reviewable result, or a repeatable import process.
- Remove Duplicates: best for a one-time cleanup when the comparison fields are clear and deleting repeated rows is intended. It changes the selected data.
- Conditional Formatting or a helper formula: best when you want to inspect matches, keep the original records, or apply your own review rules.
- UNIQUE: useful for a separate list of distinct values in supported Excel versions. It returns results elsewhere rather than cleaning the original table.
- Power Query: useful when you regularly import and clean data. In the Power Query Editor, choose Home > Remove Rows > Remove Duplicates. The cleanup becomes part of the query steps and can be repeated when you refresh, while the source data remains separate.
Power Query is particularly helpful for recurring files because the same transformation can be applied again to updated source data. Still, verify the columns used to identify duplicates and review the output before relying on a repeated process. A refresh can consistently repeat an incorrect rule just as easily as a correct one.
Common problems and how to avoid them
Excel removed records I wanted to keep. Undo immediately with Ctrl+Z on Windows or Command+Z on Mac if possible. Restore the backup if undo is unavailable. Next time, select only the fields that truly define a duplicate, and sort first if a preferred version should be retained.
Duplicates remain after cleanup. The rows may differ in a selected field, even if the difference is hard to see. Check spaces, punctuation, data types, and hidden characters. Also confirm that you selected all intended rows rather than only part of the table.
Different people were treated as duplicates. A shared name or phone number may not uniquely identify a person. Use a more reliable key, such as a customer ID, or compare a combination of fields and manually review uncertain matches.
The header was included as data. Reopen the process only after restoring the original if needed, then make sure the header setting matches the range. A missing header can lead Excel to treat the first record as a field name, while incorrectly marking the first row as a header can exclude a real record.
Removing duplicate rows does not automatically update related worksheets, external files, or systems that use the same records. If other data depends on the rows being removed, check those relationships before treating the workbook cleanup as complete.
Frequently Asked Questions
Does Remove Duplicates delete the entire row?
When applied to a selected table or full data range, Excel removes the duplicate record row across that selected data. Select the complete table so fields stay together; do not work on just one column if you need the other record details preserved.
Does Excel keep the first or last duplicate?
Excel keeps the first occurrence in the current order and removes later matching records. Sort the data first if you want a particular version to appear first, and make a backup before removing rows.
Can I highlight duplicates without deleting them?
Yes. Select the cells and use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. The rule highlights repeated values in the selected range but leaves the records unchanged.
Why does Excel not recognize two entries as duplicates?
The cells may differ in spaces, punctuation, data type, or another selected column. Review the underlying values and standardize them carefully before running Remove Duplicates again.
Can I remove duplicates based on just one column?
Yes. Select the full table, open Data > Remove Duplicates, and leave only the key column checked. Excel then removes later rows with a matching value in that field, so confirm that the field is a valid identifier before proceeding.
Related
- Excel Maximum Row Height: The 409-Point Limit
- Excel Max Date: What Is the Latest Date Excel Supports?
- Excel Max Columns: The 16,384-Column Limit Explained
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets Checkbox Column: Add and Use CheckboxesTo add a checkbox column in Google Sheets, select the cells where the checkboxes should go, then choose…
- How to Set Out of Office Dates in Outlook for Any DeviceTo set out-of-office dates in Outlook, open Automatic Replies, turn on replies for a specific time period, enter…
- Insert an Empty Checkbox in Word: Easy MethodsTo insert an empty checkbox in Word, type 2610 and press Alt+X on Windows to convert it to…
- Excel Freeze Panes: How Many Rows Should You Select?If Excel freezes too many rows, unfreeze the panes and freeze them again from the cell immediately below…
- Excel: Hide Column Headers Without Hiding DataTo hide the A, B, C column letters in Excel for Microsoft 365 on Windows, open the worksheet…
- How to Multiply a Cell by a Number in ExcelTo multiply a cell by a number in Excel, enter a formula in another cell using an asterisk:…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.