Keep Only One Duplicate in Excel: Step-by-Step

To keep one copy of each repeated entry in Excel, select your data, choose Data > Remove Duplicates, and select the columns that define a duplicate. Excel keeps the first matching row in the selected range and removes later matches. Make a copy of the data first, and decide whether a duplicate means the same value in one column or the same combination of values across several columns.

What You’ll Need

An Excel workbook with the data you want to clean. These steps are for Microsoft Excel for Microsoft 365 on Windows.
A clear rule for what counts as a duplicate. For example, you might compare email addresses alone, or compare a person’s name and department together.
A backup or an untouched copy of the original data. Remove Duplicates deletes rows, so a copy gives you a safe way to recover the source later.

Your data can be a normal range or an Excel table. If the range has column headings, make sure they are in the first row of the selected data. Blank rows and unrelated columns can make it harder to select the right range, so tidy the layout or select the intended block carefully before you start.

Step-by-Step

Choose what one record represents. Decide exactly which column or columns identify the item you want to keep once. If a product code uniquely identifies a product, use that column. If a person’s name can appear in different departments, use both Name and Department to distinguish records. Excel treats rows as duplicates only when their values match in every column you have selected in the Remove Duplicates dialog.
Make a safe copy of the data. The simplest protection is to duplicate the worksheet: right-click its sheet tab, choose Move or Copy, select Create a copy, and click OK. You can also copy the data to another sheet or save a separate workbook. This is especially important if you are cleaning a shared list, or if you are not certain which row should survive.
Decide which matching row should stay. Excel keeps the first occurrence from top to bottom and removes later matching rows. If you want to keep the newest record, the row with the most complete details, or a preferred category, sort the data so that record appears first within each group before removing duplicates. Sort the whole data range or table, not just one column, so the values in each record stay together.
Select the data range or table. Click inside an Excel table, or select the whole block of data, including all columns that belong to each record. Avoid selecting only the key column if you need to delete complete duplicate rows from a larger list. If your selection includes adjacent notes or other unrelated fields, those fields may affect which rows Excel considers duplicates.
Open the duplicate-removal command. On the Windows ribbon, choose Data > Remove Duplicates. If Excel asks whether the data has headers, check My data has headers when the first row contains labels such as Email or Order ID. If there are no labels, leave that option unchecked. Review the column list before proceeding; the selection determines how Excel compares records.
Choose the comparison columns. To keep one row for each email address, select Email and clear the checkboxes for other columns. To treat a row as a duplicate only when both its email and date match, select both columns. Use Select All only when you intend to compare every listed column. Click OK to run the command. Excel reports how many duplicate values it removed and how many unique values remain.
Check the cleaned results. Read the status message, then inspect several records that previously appeared more than once. Confirm that the correct row survived and that unrelated rows are still present. If the results are wrong, use Ctrl+Z on Windows immediately to undo, adjust your sort or column selections, and run the command again. On a Mac, use Command+Z. Undo may no longer be available after you close the workbook or perform later actions, which is why keeping a copy matters.

On current Excel for Mac versions, the duplicate-removal feature is also available from the Data tab, though ribbon layout and wording can vary slightly by version. Keyboard shortcuts differ: Ctrl+Z is the Windows undo shortcut, while Command+Z is used on Mac. If you use Excel for the web, look for Remove Duplicates on the Data tab; if the option is unavailable in your interface, open the workbook in the desktop app.

Common Mistakes

Selecting the wrong columns: If you select every column, two records with the same email but different phone numbers will not count as duplicates. If you select only a person’s first name, people who share that name could be incorrectly merged. Choose the narrowest set of columns that reliably identifies the record.
Assuming Excel keeps the newest row: It does not automatically choose the latest date or the most complete version. It retains the first matching row in the current order. Sort first if the row to keep should be selected by date, priority, or another preference.
Selecting a single column when you mean to remove whole records: You may compare only the key column, but make sure Excel is operating on the complete range or table so it removes entire duplicate rows, not just values you meant to inspect. Always review the selection and the dialog before confirming.
Forgetting the header setting: If the first data row is mistakenly treated as a header, it may be excluded from duplicate checking. Conversely, treating headings as data can produce confusing results. Check the header option and verify that the dialog shows the expected column names.
Expecting near-matches to count as duplicates: A trailing space, a different spelling, or a different number format can make entries distinct. “Lee” and “Lee ” may look alike but contain different text. Remove obvious extra spaces or standardize the data before comparing it.

Tips

Use a stable unique key when possible. An email address, customer ID, or product code is often safer than a name. Names can be shared or spelled in multiple ways, while an assigned ID is meant to identify one record consistently.
Keep all related fields together. Sort or filter the full table, rather than one column by itself. Sorting a lone column can mismatch names, dates, and addresses, making the retained records unreliable.
Turn the data into a table. Select the data and choose Insert > Table, or press Ctrl+T on Windows (Command+T on Mac in supported Excel versions). Confirm that the table has headers if appropriate. Tables make it easier to identify columns and keep adjacent fields together as the list changes.
Keep a record of the rule you used. If you are cleaning a recurring report, note which columns define a duplicate and how you decided which row should come first. That makes the next cleanup easier to repeat and review.
Use a helper column when you want to review before deleting. For example, with comparison fields in A2:A100 and B2:B100, enter this in C2 to flag the first occurrence of each A-and-B combination: =COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1. Fill the formula down through C100. TRUE marks the first matching combination; FALSE marks later repeats. Filter column C to inspect the FALSE rows, then copy the TRUE records to a separate area if you want to preserve the original. In a worksheet where A is Email and B is Department, the first row for each email-and-department pair is marked TRUE, and subsequent identical pairs are FALSE. This approach requires no newer dynamic-array function and lets you review before removing anything.

Troubleshooting

Excel removed too many rows. Undo immediately with Ctrl+Z on Windows or Command+Z on Mac. Reopen Remove Duplicates and select additional columns if records with the same key should remain separate. For instance, if the same customer can have multiple orders, compare Customer ID and Order ID rather than Customer ID alone.

Excel did not remove rows that look identical. Check whether the values contain hidden spaces, nonprinting characters, or different underlying values. Click a cell and inspect the formula bar, or compare the relevant cells in a helper column. You can standardize copied text in a separate column with Excel’s TRIM function, but check the cleaned results before replacing the originals; TRIM removes ordinary extra spaces, not every kind of invisible character. Also confirm that you selected the intended columns in the dialog.

The wrong version of a record stayed. Undo, sort the entire range so the preferred record appears above its duplicates, and run Remove Duplicates again with the same comparison columns. For a date-based choice, ensure dates are real Excel dates rather than text and sort newest to oldest if the newest row should be first.

The command is unavailable or the count seems unexpected. Check that the worksheet is not protected and that the selection includes the intended data. If the list is filtered, clear or review the filter so you understand which records are visible; do not assume the visible rows represent the whole table. In Excel for the web, use the Data tab if the command is present, or open the workbook in Excel for Microsoft 365 on Windows. If your range contains merged cells, unmerge them before sorting or cleaning, because merged layouts can interfere with selecting and managing records.

Frequently Asked Questions

Does Excel keep the first or last duplicate?
Remove Duplicates keeps the first matching row in the current top-to-bottom order and deletes later matches. Sort the complete data range first if you want a particular record to come first.

Can I remove duplicates based on just one column?
Yes. In the Remove Duplicates dialog, select only that column as the comparison key. Excel can still remove the matching rows from the selected range or table, so confirm that you selected the full records, not just the key column.

Can Excel remove duplicate rows only when every field matches?
Yes. Select all the relevant columns in the dialog. Excel will remove a later row only when its values match the earlier row across all selected columns.

Will Remove Duplicates change the original data?
Yes. It deletes later matching rows from the selected range or table. Make a copy first, or use a helper-column review method if you want to examine flagged records before deciding what to remove.

Does Excel distinguish uppercase and lowercase text?
Remove Duplicates is generally not case-sensitive for text comparisons, so entries such as ABC and abc are treated as duplicates when the other selected values match. If capitalization carries meaning in your data, check and standardize records carefully before using the command.

Can I keep one duplicate without deleting anything?
Yes. Add a helper column that flags first occurrences, then filter or copy the rows marked TRUE to a new sheet. The original list remains available for comparison, and you can inspect the proposed unique set before replacing or deleting the source.

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.