How to Remove Duplicates in Excel (Easy Steps)

The quickest way to remove duplicates in Excel is to select your data, open Data > Remove Duplicates, choose the columns to compare, and click OK. Excel keeps the first matching record and removes the later duplicates.

Before doing that, save a copy of your worksheet. Remove Duplicates deletes data from the selected range, and choosing the wrong columns can remove rows you meant to keep.

How to Remove Duplicates in Excel

These steps work in current desktop versions of Excel and Excel for the web, although the ribbon may look slightly different.

1. Open your Excel workbook and click a cell inside the list or table you want to clean. If Excel does not recognize the complete range, select the full dataset, including its column headings.
2. Click the Data tab at the top.
3. Select Remove Duplicates in the Data Tools group.
4. In the dialog box, check My data has headers if your first row contains headings such as Name, Email, or Order ID.
5. Under Columns, select the columns Excel should use to decide whether two records are duplicates.
6. Click OK. Excel will tell you how many duplicate values it removed and how many unique values remain.

To remove rows only when every selected field matches, leave all relevant columns checked. To remove repeated values based on a specific field, such as Email, select only that field in the dialog box.

Important: Unchecking a column does not protect its data. It only tells Excel not to use that column when comparing rows. Excel can still remove the entire matching row, including information in unchecked columns.

Remove Duplicates From One Column Without Losing the Wrong Rows

Suppose your worksheet contains this data:

If you select Email as the only comparison column, Excel keeps the first Maya record and removes the later Maya row. That means order 103 disappears, even though it represents a different purchase.

If the aim is to remove duplicate orders, compare Order ID instead. If you want to delete only rows where Name, Email, and Order ID are all identical, select all three columns.

The safest choice depends on what counts as a duplicate in your spreadsheet, not simply whether two cells contain the same name or email address.

If you only need a clean list of email addresses while keeping every order, copy the Email column to a separate sheet and remove duplicates there, or use the UNIQUE formula explained below.

Remove Rows That Match Across Multiple Columns

You might need to treat two records as duplicates only when several details match. For example, two entries should be considered the same customer record only if both the Email and Phone fields match.

1. Select the complete table.
2. Open Data > Remove Duplicates.
3. Click Unselect All.
4. Check Email and Phone.
5. Click OK.

Excel now uses the combination of those two columns to find matches. Identical emails with different phone numbers are kept because the selected combination is different.

This also works for combinations such as Product + Invoice Number or First Name + Last Name. Be careful with names, though: two people can share the same name without being duplicate records.

How to Highlight Duplicates Before Deleting Them

Not sure which entries should go? Highlight them first instead of deleting anything.

1. Select the column or range you want to inspect.
2. Go to Home > Conditional Formatting.
3. Choose Highlight Cells Rules > Duplicate Values.
4. Pick a highlight style, then click OK.

Excel marks matching values in the selected range. You can review them and decide whether to remove anything.

This is especially helpful when a repeated email or product name might be legitimate. Highlighting duplicates does not delete any records.

One limitation: the standard Duplicate Values rule works on selected cell values. To judge records by a combination of columns, use Remove Duplicates with the appropriate columns selected, preferably on a copy of the data.

Use UNIQUE to Remove Duplicates Without Changing the Original List

For a list you want to preserve, UNIQUE is often better than deleting rows.

If your email addresses are in cells A2 through A100, click an empty cell and enter:

=UNIQUE(A2:A100)

Press Enter. Excel creates a separate list containing one copy of each distinct value. The original column remains unchanged.

You can also return distinct rows. For example:

=UNIQUE(A2:C100)

This returns each distinct combination of the values across columns A to C.

The UNIQUE function is available in Microsoft 365, Excel 2024, Excel 2021, and supported Excel web and mobile versions. It is not available in older perpetual releases such as Excel 2019 or Excel 2016. If Excel shows #NAME?, check whether your version supports the function and whether you typed it correctly. If you see #SPILL!, clear the cells blocking the results.

The output can update when the source values change, so UNIQUE is a useful choice for lists you need to maintain. See Microsoft’s UNIQUE function reference for supported versions and syntax.

What to Do If Excel Removes the Wrong Rows

If you have just removed duplicates and the results are incorrect, press Ctrl + Z (or use Undo) immediately. Then review which columns were selected before trying again. If you already saved, closed, or made several other changes, Undo may no longer restore the earlier data. Use your saved backup or version history if available.

A few other issues are worth checking:

• Header row disappeared: Repeat the process on a backup and make sure My data has headers is selected when appropriate.
• Expected duplicates remain: Look for leading or trailing spaces, inconsistent formats, or differences in the fields you chose to compare.
• Different records were removed: You likely selected too few comparison columns, such as Email alone when each email can have multiple orders.
• Remove Duplicates is unavailable: Check whether the workbook is protected, editing is restricted, or the range contains outlines or subtotals that must be removed first.

If ordinary copying or pasting is failing while you’re trying to make a backup, see our guide to fixing copy and paste in Windows 11 before altering the original worksheet.

For the underlying deletion rules and column-selection behavior, see Microsoft’s Remove Duplicates instructions.

Can You Keep the Last Duplicate Instead of the First?

Excel normally keeps the first occurrence in the selected list. If you want to retain the most recent record, sort your data first, then remove duplicates.

For example, if your table includes an Updated Date column, sort that column from newest to oldest. Then use Remove Duplicates based on the customer ID or other appropriate key. The latest record will appear first and will be the one Excel keeps.

Check that the entire table is included in the sort so names, dates, and other fields stay on the correct rows. Make a backup before sorting or deleting any records.