Remove Duplicates in Excel: A Practical Step-by-Step Guide

To remove duplicate rows in Excel for Microsoft 365 on Windows, select your data, choose Data > Remove Duplicates, select the columns that define a duplicate, and click OK. Excel keeps the first matching record and removes later ones, so make a copy of your data first if you may need to restore it.

The key decision is what counts as a duplicate. If you select every column, Excel removes rows that match across the whole row. If you select only a name column, it treats repeated names as duplicates even when other details differ. The steps below help you choose the right option and check the result.

What You’ll Need

An Excel workbook containing the list you want to clean.
A clear idea of which column or combination of columns identifies a duplicate record.
A backup copy of the workbook, or a copy of the list on another worksheet, before deleting anything.

Step-by-Step

Save a backup. Use File > Save As to save a separate copy, or copy the list to a new worksheet. The Remove Duplicates command deletes rows from the selected data; it is not just a filter you can switch off. You can immediately use Undo, but a saved copy is safer if you make more edits afterward.
Check the shape of your list. Arrange the data in a continuous block, with one record per row and a heading at the top of each column, such as Customer, Email, and Region. Avoid completely empty rows or columns inside the list, because they can make Excel select only part of the data. If your list is already an Excel Table, click a cell in it; you do not need to select the whole worksheet.
Select the list or table. Click a cell inside the data. For an ordinary range, select the full set of columns and rows to clean, including the headings. You can drag across the range or click within it and use Ctrl+A to select the current data region. Check the highlighted outline before continuing, especially if the sheet contains nearby tables or notes. On Mac, use Command+A to select the current data region.
Open the duplicate-removal dialog. On Windows, choose Data > Remove Duplicates on the ribbon. The command is in the Data Tools group. In Excel for Mac, click within the range or table and use Data > Remove Duplicates; ribbon placement can vary slightly by build. If a prompt asks whether to expand the selection, choose Expand the selection when you want entire records to stay together. Continuing with only one selected column can remove cells without removing their related row data.
Confirm whether your list has headers. In the dialog, make sure My data has headers is checked if the first row contains column names. If the first row is actual data, clear the checkbox. A wrong setting can cause Excel to treat a heading as a record or omit the first data row from the comparison, so review this before choosing columns.
Choose the columns that define a duplicate. The dialog lists the headings or column names in your selection. Leave all columns checked if only records identical in every listed field should be removed. To identify duplicates by one field, clear Select All and check only that column. To identify a record by a combination, check the relevant fields together—for example, Email and Account ID. Excel compares values in the checked columns; it removes the entire duplicate row from the selected range or table.
Run the command and read the result. Click OK. Excel reports how many duplicate values or rows it removed and how many unique values remain. Click OK again to close the message. The first occurrence in the current row order is retained, so sort the list first if you want a particular record—such as the newest date or most complete entry—to be the one that stays.
Review the cleaned list. Check several records around where duplicates were removed, and compare the remaining row count with the message Excel displayed. Confirm that each surviving row still has its associated information, such as address, date, or amount. If the result is wrong, press Ctrl+Z on Windows or Command+Z on Mac immediately, then repeat the process with the correct range and column choices.

Example: Imagine a list with Name, Email, and City. If you check all three columns, two rows are duplicates only when all three values match. If you check Email alone, Excel keeps the first row for each email address and removes later rows with that address, even if their names or cities differ. Use the second approach only when an email address is a reliable unique identifier in your data.

Common Mistakes

Checking the wrong columns. Checking every column will not find two records that have the same email but different spelling in the name field. Checking only Email may remove people who share an address. Choose fields that match the way your organization defines one distinct record.
Forgetting to expand the selection. If you select a single column inside a wider range, Excel may ask whether to expand the selection. Choose the option that includes the complete records, unless you deliberately intend to clean a standalone column. Otherwise, related values can become separated from the correct names or IDs.
Misidentifying the header row. If the header checkbox is off when headings are present, a heading can be compared as data. If it is on when there are no headings, the first actual record may not be treated as data. Check the first row in the selected range, not just the worksheet’s top row.
Expecting near-matches to count as duplicates. Excel compares cell values; it does not decide that different spellings or similar-looking values mean the same thing. Extra spaces, different punctuation, alternate spellings, and different IDs can prevent a match. Clean or standardize values before removing duplicates if those differences are accidental.
Removing a valid repeat. A person, product, or transaction can legitimately appear more than once. Repeated names alone are often not enough to establish that two rows represent the same record. Compare a stable identifier or a suitable combination of fields before deleting anything.

Tips

Sort to control which record remains. Remove Duplicates preserves the first occurrence in the list’s current order. If you want to keep the latest transaction or the record with a preferred status, sort by that field before running the command. Make sure the entire list is sorted together so rows remain intact.
Use a unique identifier where possible. An account number, order ID, or email address can be more dependable than a person’s name. If no single field is unique, select multiple columns that together identify a record. For example, Name plus Date of Birth may be more specific than Name alone, depending on the data and its purpose.
Convert an ordinary list to a Table for ongoing work. Select a cell in the list and choose Insert > Table, then confirm whether the table has headers. Tables make it easier to include new rows in later data work and keep headings visible. You can still use Data > Remove Duplicates with a table selected.
Separate finding duplicates from deleting them. If you need to inspect repeated records first, use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values to highlight repeated values in a selected column. This helps you review potential matches, but it is not a substitute for checking complete records when duplicates depend on several fields.
Clean inconsistent text before comparing. Look for leading or trailing spaces, inconsistent punctuation, and values stored in different formats. For a small list, you can correct those cells manually. For a larger list, use a separate helper column or Power Query to standardize the values, then compare the cleaned fields. Keep the original values if they are needed for audit or reference.
Run the command again after adding data. Remove Duplicates is a one-time action, not a live rule. New rows added later are not automatically checked against the earlier results. Repeat the command after appending records, or set up a separate review process for regularly refreshed data.

Troubleshooting

The Remove Duplicates command is unavailable. Click a cell inside the data or select a valid range first. Check that the worksheet is not protected in a way that prevents editing, and confirm that you are on the Data tab. If the data is in a filtered range, clear or review the filters so you understand which records are visible; filtering does not change what you intend the whole selection to contain.

Excel removed too many rows. Undo immediately with Ctrl+Z on Windows or Command+Z on Mac. Reopen the dialog and check whether you selected too few columns, such as Name alone when different people can share a name. Also verify that the selection did not include unrelated rows or another list. If Undo is no longer available, restore the backup rather than trying to reconstruct deleted records by hand.

Excel removed no rows, but duplicates are visible. Check for small differences in the compared cells: extra spaces, different punctuation, a number stored as text, or a date represented differently. Confirm that the repeated records fall within the selected range and that the relevant columns are checked. For hidden spaces or inconsistent formats, create a cleaned comparison field, review its results, and run duplicate removal against the cleaned data only after saving a copy.

The wrong entry was kept. Undo, sort the full range so the preferred record appears first, and run Remove Duplicates again using the same identifying columns. Do not sort just one column; that would detach values from their associated records. If the list contains conflicting records that need review, resolve those differences before deleting one of the rows.

The result looks different from the duplicate highlights. Conditional Formatting may highlight repeated values in a single selected column, while the Remove Duplicates dialog may compare a different set of columns. A repeated name is not necessarily a duplicate row. Align the columns used in both checks, and remember that highlighting identifies repeated values for review rather than automatically confirming that a whole record should be deleted.

Frequently Asked Questions

Does Remove Duplicates delete the whole row?
Yes. When you run it on a multi-column range or table, Excel removes the duplicate record row from that selected data. It keeps the first occurrence and removes later matches based on the columns you checked. Make sure the selection includes the entire record so related cells are handled together.

Can I remove duplicates from just one column?
Yes, if that column is a standalone list and you do not need to preserve row relationships with other fields. If it is part of a wider table, include the full table and use the column as the duplicate criterion. That removes matching records rather than shifting or cleaning only the selected cells.

Does Excel distinguish uppercase and lowercase?
Remove Duplicates is not case-sensitive for this comparison, so values such as Alex and ALEX are treated as duplicates when the same checked fields otherwise match. If capitalization carries meaning in your data, review and normalize it before using the command, and test the process on a copy.

Will it remove rows that look similar but are not identical?
Only if they match in all the columns you selected. With one column checked, matches in that column count even if unchecked columns differ. Excel does not automatically correct misspellings, remove extra spaces, or decide that near-matches refer to the same person.

Can I undo duplicate removal?
Usually, yes, if the command is still in the undo history: press Ctrl+Z on Windows or Command+Z on Mac. If you have closed the workbook, made other changes, or cleared the undo history, restore a saved copy or use your organization’s file version history if available.

Does Excel automatically remove duplicates when I add new rows?
No. The command cleans the selected data once and does not create a continuing rule. Run it again after adding records, or use a repeatable process such as Power Query if you regularly import and clean updated lists.

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.