To find and remove duplicate records in Excel for Microsoft 365 on Windows, select your data, go to Data > Remove Duplicates, choose the columns that define a duplicate, and select OK. Make a copy of the workbook first: Excel deletes matching rows, and the choice of columns determines which records count as duplicates.
You can also highlight repeated values before deleting anything. That is useful when you want to review potential matches, while Remove Duplicates is the direct way to remove repeated records from a list. The steps below explain both options and how to check the result.
What You’ll Need
- An Excel workbook containing the list you want to clean. The instructions are for Excel for Microsoft 365 on Windows.
- A clear idea of what counts as a duplicate. It might be a repeated email address, or a complete row with the same name, date, and order number.
- A backup or a copy of the worksheet. Removing duplicates changes the selected data, so a copy gives you a way to recover the original.
Step-by-Step
- Save a copy and inspect the list. Save the workbook under a new name, or duplicate the worksheet before cleaning the data. Check that the list has one header row, consistent columns, and no unrelated information immediately beside it. If the list has blank rows or columns splitting it into sections, decide which section to clean and select that section deliberately.
- Decide which fields define a duplicate. For example, if every repeated email address should be removed, the email column alone defines a match. If a person can have several valid entries, use the combination of fields that identifies a repeated record, such as email address and appointment date. Choosing fewer columns can remove records that are different in other ways; choosing more columns may leave repeats that you intended to remove.
- Optionally highlight repeated values for review. Select the cells in one column you want to inspect, excluding the header if practical. Choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the dialog, leave Duplicate selected, choose a formatting style, and select OK. Excel marks repeated values in that selected range, which lets you review them before deletion. This is a visual check, not a removal operation.
- Select the data range or a cell within the list. If you have a simple, continuous list, clicking a cell in it is usually enough for Excel to detect the surrounding data. For greater control, select the full range, including every column that belongs to the records. Avoid selecting just one column if you want to delete complete rows while comparing several fields; the dialog needs the relevant data columns to make that comparison.
- Open the Remove Duplicates dialog. On the ribbon, choose Data > Remove Duplicates in the Data Tools group. If the list has column headings, select My data has headers. This prevents the heading row from being treated as an ordinary record. Check the column names shown in the dialog before continuing.
- Choose the matching columns. Select only the columns that should determine whether two records match. Select all columns if a duplicate means every included field in the row is the same. Clear columns that should not affect the match, such as a notes field that may differ even when the email address is repeated. The columns you leave selected are the comparison criteria; Excel removes entire duplicate rows from the selected data range.
- Review and confirm the removal. Select OK to let Excel remove repeated records while keeping the first occurrence it encounters in the range. Excel displays a message reporting how many duplicate values were found and removed, and how many unique values remain. Read the message and compare the remaining count with what you expected before moving on.
- Check the cleaned data and save. Look through the beginning, middle, and end of the list, and verify important totals or record counts if you have them. If the result is wrong and you have not made other changes since the operation, press Ctrl+Z on Windows to undo it. Otherwise, restore the saved copy. Save the workbook after you are satisfied with the result.
In Excel for Mac, the feature is also available from the Data tab as Remove Duplicates; the dialog may be arranged differently from the Windows version. Excel for the web also offers duplicate removal in the Data tab in supported current versions, but ribbon layout and availability can vary. If you cannot find the command or are working with important data, open the workbook in the desktop app or make a separate copy before proceeding.
Common Mistakes
- Comparing the wrong columns. Selecting only a surname column can cause Excel to treat two different people with the same surname as duplicates. Selecting every column when only an email address should be unique can leave repeated emails in place if another field differs. Set the criteria based on the meaning of the records, not just on which fields look similar.
- Forgetting the header setting. If the header checkbox is wrong, Excel might compare a heading as if it were data, or exclude the first actual record from comparison. Confirm that My data has headers matches the selected range before you remove anything.
- Assuming highlighting deletes records. Conditional Formatting only adds visual formatting. It does not remove rows, and removing the formatting later does not restore or change data. Use Data > Remove Duplicates when you are ready to delete matching records.
- Deleting a record that contains useful differences. When you compare by one field, Excel keeps one row and removes the other rows that match that field, even if unchecked columns contain different information. Review a copy first if those differences might matter.
- Cleaning a partial selection by accident. If the selection does not include all columns in a record, removal can leave related information out of step or fail to compare the intended records. Select the full record range, then use the dialog to choose which columns define a match.
- Overlooking extra spaces or inconsistent entries. A value with a leading or trailing space, a different abbreviation, or a slightly different spelling may not match the version you expect. Inspect the source values before concluding that Excel missed a duplicate.
Tips
- Use a table for an ongoing list. Select a cell in the data and press Ctrl+T on Windows to create a table, confirming whether it has headers. Tables make column headings easier to identify and expand as you add records. Remove Duplicates can be used on table data, but still verify the selected comparison columns and keep a backup.
- Keep one preferred version deliberately. Remove Duplicates retains the first matching record in the selected range. If you want to keep the newest or most complete version, sort the list into the desired order first, then remove duplicates. For example, sort a date column newest to oldest before removing repeated customer IDs if the newest entry is the one to retain. Check the sort order and the retained rows afterward.
- Highlight before you remove when the criteria are uncertain. Duplicate Values is helpful for checking repeated entries in a single selected column. For a match based on several fields, use a copied sheet and the Remove Duplicates dialog as a controlled test, or inspect records sorted by the key fields. A highlight in one column does not prove that entire rows are identical.
- Keep an audit-friendly original. Preserve the original worksheet or save a separate copy with a clear filename. This is especially useful when the list is shared, used for reporting, or contains records that may need to be restored. Do not rely on undo as a long-term backup.
- Standardize data before matching if needed. Decide how to handle spaces, inconsistent capitalization, punctuation, and formatting. For example, remove unintended spaces or make spelling consistent on a copy before deduplicating. Do not standardize automatically if those differences are meaningful, such as distinct product codes.
Troubleshooting
- No duplicates were removed. Reopen the dialog and confirm that the intended columns are selected. Check whether apparent matches contain extra spaces, different spellings, or genuinely different values. If you selected all columns, try the specific identifier fields that should define a duplicate, but do so on a copy so you can review what would be lost.
- Excel removed too many rows. Undo immediately with Ctrl+Z if possible. Check whether you selected too few comparison columns, such as comparing only a shared surname or category. Restore from the backup if undo is unavailable, then repeat with a more specific combination of fields.
- The first data record seems to be missing or treated oddly. Check whether the selection includes a header and whether My data has headers was selected correctly. Undo or restore the copy, select the complete range again, and confirm the checkbox before running the command.
- The command is unavailable or the dialog shows unexpected columns. Make sure you are in the worksheet with the data and have selected a cell or range in the intended list. Check for merged cells, separated blocks, or a selection that includes unrelated columns. In a browser or on Mac, ribbon placement can differ; look on the Data tab, or open the file in Excel for Microsoft 365 on Windows.
- Highlighted values do not match whole duplicate records. The Duplicate Values rule looks for repeated cell values in the selected range, not repeated combinations across a complete row. Select a single key column to check that field, and use Remove Duplicates with multiple selected columns when the comparison depends on a combination of fields.
- Filters or hidden rows make the result confusing. Clear filters and inspect the full range before removing duplicates, especially when you need to understand which records may be affected. Make the backup first, and review the final list after removal rather than relying only on the visible rows.
Frequently Asked Questions
Does Excel keep the first duplicate or the last one?
Remove Duplicates keeps the first occurrence it encounters in the selected range and removes later matching rows. Sort the list first if a particular version should be kept, then check the retained records.
Can I remove duplicates based on just one column?
Yes. In the Remove Duplicates dialog, select only that column. Excel removes the other matching rows from the selected range, so information in the removed rows may be lost even if other columns differ.
Can I find duplicates without deleting them?
Yes. Select a column and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This highlights repeated values but does not delete rows. It is best suited to checking one field at a time.
Does Remove Duplicates distinguish uppercase and lowercase text?
Do not rely on capitalization alone to keep otherwise matching entries separate. Review the resulting records if uppercase and lowercase versions might represent different items in your workflow.
Can I undo Remove Duplicates?
Usually, you can immediately press Ctrl+Z on Windows to undo the change, provided you have not taken other actions that affect the undo history. On Mac, use Command+Z. A saved backup is safer if the operation cannot be undone.
Will Excel remove duplicates from the whole workbook?
No. The command works on the selected range or detected data region, not every worksheet in the workbook. Repeat it for each list you intend to clean, and check the selected range before confirming each removal.
Related
- How to Enable Word Wrap in Excel and Adjust Cell Text
- How to Enable Wrap Text in Excel and Adjust Row Height
- Excel Trust Center: How to Open and Configure Settings
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Find Unique Values in Google SheetsTo list distinct values without changing your source data, enter =UNIQUE(A2:A10) in an empty cell, replacing the range…
- Excel Conditional Formatting: Highlight Errors in CellsYes. In Excel for Microsoft 365, select the cells you want to check, then create a conditional-formatting rule…
- Google Sheets COUNTIF: Count Cells Containing TextWith the text to check in A2:A100, count cells containing a word or phrase anywhere in the cell…
- How to Copy Excel Cells to Word Without Losing FormatTo copy Excel cells into Word, select the cells in Excel, press Ctrl+C, click where the table should…
- Schedule a Daily Email in Outlook: The Best MethodTo send the same email automatically every day, create a scheduled cloud flow in Microsoft Power Automate and…
- Excel Strikethrough Shortcut: Ctrl+5 and MoreIn Excel for Microsoft 365 on Windows, select the cell or cells and press Ctrl+5 to apply strikethrough;…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.