To remove duplicate records by comparing multiple columns in Excel for Microsoft 365 on Windows, select the full data range, then choose Data > Remove Duplicates. In the dialog, check the columns that together define a duplicate, confirm whether the first row contains headers, and select OK; Excel keeps the first matching record and removes later ones.
For example, you can compare First Name and Last Name to remove repeated people even when their email addresses differ. The important distinction is that you should select the whole table or record range, but check only the columns Excel should use to decide whether two records match.
Remove duplicates with Excel’s built-in command
The built-in command is usually the simplest choice when you want to clean the existing data in place. First, make a copy of the workbook or duplicate the worksheet if you may need to restore deleted rows. Then select the complete block of records, including any columns you want to keep. Avoid selecting just the comparison columns if you intend to delete entire records.
- Click a cell in the data, or select the full range of records. Include every field that belongs to each record.
- On the ribbon, select Data > Remove Duplicates.
- In the dialog, check My data has headers if the first row contains column names.
- Check the columns that should be used together to identify a duplicate. Clear the check beside columns that should not affect the comparison.
- Select OK. Excel reports how many duplicate values it removed and how many unique values remain.
Suppose a table has columns for First Name, Last Name, Email, and Department. If the goal is one record per person identified by name, select the full table but check only First Name and Last Name in the dialog. Excel compares the pair of values and removes later rows with the same pair, while retaining the other fields from the first matching row. If instead a person is defined by their email address, check Email alone.
On Excel for Mac, the command is also on the Data tab as Remove Duplicates; dialog details can vary slightly by release. Excel for the web also provides data-cleaning tools, though the ribbon or dialog can differ. If you cannot find the command in your version, open the workbook in the desktop app and use its Data tab.
How Excel decides that records are duplicates
Excel compares the values in the columns you check. When you check several columns, their combined values form the test: two rows are duplicates only when the values match across all checked columns. If you check City and Postal Code, matching cities with different postal codes are not duplicates, and matching postal codes with different cities are not duplicates.
The command does not merge information from matching records or choose the most complete one. It retains the first row it encounters in the current order and removes subsequent rows that match the selected comparison columns. If the records contain different details in unchecked columns, those details are not combined. This is why sorting the data before cleanup can matter: sort so the version you want to keep appears first.
Cell formatting is not a reliable way to distinguish records. A value displayed in bold or with a different fill is still compared by its underlying content, not by its appearance. Excel’s duplicate-removal comparison is not case-sensitive, so names that differ only in capitalization can be treated as duplicates. Extra spaces, inconsistent spellings, or values stored in different forms can make apparently similar records fail to match, so inspect and standardize data when exact matching is important.
Choose the right columns for the comparison
The right selection depends on what counts as the same record in your work. A transaction may be unique by invoice number, while a contact list may be unique by email address. Checking every column is not always the right answer: if two records share the same name but have different phone numbers, comparing every field will keep both. Checking too few columns can delete records that are distinct for your purpose.
- One identifier column: Use a stable ID, such as an order number, when it uniquely identifies a record.
- A combination of fields: Check columns such as Date, Product, and Location when only that full combination defines a repeat.
- Names without a unique ID: Decide whether a name alone is sufficient. Two different people can share a name, so including another reliable field may prevent unwanted removals.
- All columns: Check every column when only completely identical records should be removed.
Before confirming, read the column names in the dialog and verify that the checked fields express your definition of a duplicate. For instance, if you want to remove repeat customer IDs but keep each customer’s separate order, selecting Customer ID alone would remove later orders. In that case, the rows are not duplicates as order records; the comparison rule is simply too broad.
Protect the data and decide which copy to keep
Remove Duplicates changes the selected data, so treat it as a cleanup operation rather than a preview. Save the workbook first, or copy the source range to a new worksheet. You can also use Ctrl+Z immediately after the operation to undo it in the desktop app, but undo history may be lost after other actions or after closing the workbook. A separate copy is safer when the source data matters.
If duplicates contain different information, establish a keep rule before running the command. For example, if the same customer appears several times with different update dates, sort by the date so the newest record appears first, then remove duplicates using the customer identifier. Confirm that the sort order places the intended record first; otherwise, Excel may retain an older or incomplete row. If you need to combine fields from several rows, review or reconcile those records manually instead of expecting the command to merge them.
Filtering a table to inspect likely repeats can help, but do not assume that visual formatting or a filter defines what the command will remove. Use a clear comparison rule and check the resulting row count. For high-impact cleanup, work on a copy and compare the cleaned records with the original before replacing the source.
Use UNIQUE to create a separate list instead
If you want to preserve the source and display distinct combinations elsewhere, a dynamic-array formula can be more suitable. In Microsoft 365 or Excel 2021, the UNIQUE function returns unique rows from a range and spills the results into nearby cells. It does not delete or alter the original records.
With a data layout of headers in A1:C1 and records in A2:C100, enter this formula in an empty cell outside the source range: =UNIQUE(A2:C100). Excel returns one row for each distinct combination across columns A, B, and C. For example, two rows with the same name but different email addresses remain separate because the complete rows differ.
UNIQUE is useful for reports and lists that should update when source values change. Make sure the cells below and to the right of the formula are empty so the results can spill; otherwise, Excel may show a #SPILL! error. If you need distinct values based on only some fields while returning associated details from other columns, a basic UNIQUE formula over the full range compares full rows. You may need a different formula design or a separate cleanup process to define that relationship.
For a one-time cleanup of the original records, use Remove Duplicates. For a live list that reflects changes to the source, use UNIQUE. The formula is available in Microsoft 365 and Excel 2021 or later; older Excel versions may not support it.
Common problems and how to avoid them
A frequent problem is selecting the wrong fields in the dialog. If Excel removes too many rows, the checked columns may not uniquely describe the records you intend to retain. If it removes none, the rows may differ in one of the checked fields, perhaps because of spaces, spelling differences, or a genuinely different value. Review a few examples before repeating the operation on the original data.
- Header row treated as data: Check My data has headers when the first row contains labels. Otherwise, Excel may include that row in its comparison.
- Unexpected rows removed: Check whether the comparison uses too few columns, then restore from a copy or undo if available.
- Similar-looking values remain: Check for leading or trailing spaces and inconsistent entry formats. Clean or standardize the fields before comparing.
- Wrong version retained: Sort the data into the desired order first, because Excel keeps the first matching row.
- Formula results blocked: For UNIQUE, clear cells in the expected spill area or move the formula to an empty region.
For recurring imports, consider standardizing data before removing duplicates. Consistent date formats, trimmed spaces, and a dependable identifier make the comparison rule more predictable. Keep the original imported data available until you have verified that the cleaned result meets the requirements.
Frequently Asked Questions
Can I remove duplicates based on only two columns?
Yes. Select the full record range, open Data > Remove Duplicates, and check only those two columns in the dialog. Excel compares the pair of values and removes later matching rows from the selected range.
Does Remove Duplicates keep the first or last row?
It keeps the first matching row in the current order and removes later matches. Sort the data first if you want a particular record, such as the newest entry, to appear first.
Can Excel remove duplicates without deleting the source data?
Yes. Use =UNIQUE(A2:C100) in Microsoft 365 or Excel 2021 or later to return unique full-row combinations in another area. The source stays unchanged, and the result can update when the source changes.
Does Excel treat uppercase and lowercase text as different?
No. Remove Duplicates is not case-sensitive, so entries that differ only in letter case can be treated as matches. If capitalization carries meaning in your data, use a different comparison method.
Why are rows that look identical not being removed?
They may differ in a checked field because of spaces, spelling, or another underlying value. Check the compared columns and standardize the data. Formatting alone does not make otherwise different values match.
Related
- Excel Print Area Button: Where to Find It and How to Use It
- Excel Print Area: Set It, Clear It, and Print Automatically
- Excel Print Area Button Greyed Out? Causes and Fixes
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 Freeze More Than the Top Row in ExcelTo freeze more than the top row in Excel, select the cell directly below the rows you want…
- Add an Auto Reply to a Shared Mailbox in Outlook 365To add an automatic reply to a shared mailbox, open that mailbox in Outlook on the web and…
- Enable Circular References in Excel: Step-by-StepTo enable circular references in Excel for Microsoft 365 on Windows, open the workbook, select File > Options…
- Embed a SharePoint Video in PowerPoint: What WorksYes—you can play a SharePoint video from a PowerPoint slide if your version accepts its link under Insert…
- How to Make a Brochure in Microsoft Word, Step by StepTo make a brochure in Microsoft Word, start with a brochure template or set a blank document to…
- How to Paste Backwards in Excel: Reverse Cell OrderTo paste a list backwards in Excel for Microsoft 365, enter =SORTBY(A2:A6,ROW(A2:A6),-1) in an empty cell, replacing A2:A6…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.