To remove duplicate records in Excel based on more than one field, select your data, choose Data > Remove Duplicates, and check every column that together defines a duplicate. Excel compares the selected columns as a combination, keeps the first matching row in the current order, and removes later matches; sort first if you want a particular record to be kept.
What “multiple criteria” means in Excel
Multiple criteria means that a row counts as a duplicate only when its values match across all the fields you selected. For example, a sales table might have Customer, Date, Product, and Amount columns. If you select Customer, Date, and Product as the criteria, two rows match when all three of those values match. Different amounts do not prevent a match, because Amount was not selected.
This is a combined, or composite, definition of a duplicate. It is not the same as asking Excel to remove a row when its customer matches or its date matches. In the Remove Duplicates dialog, selected columns are evaluated together: every selected field must match for Excel to treat records as duplicates. Fields you leave unchecked do not affect the comparison, though they remain in the worksheet for the row Excel keeps.
That distinction matters when a workbook has repeated names or dates that are valid on their own. A customer can appear many times, for instance, but a repeated Customer-Date-Product combination may represent an accidental duplicate transaction.
Remove duplicates using selected columns
In Excel for Microsoft 365 on Windows, use these steps:
- Make a copy of the worksheet or workbook if you may need the removed rows later. Remove Duplicates changes the data rather than creating a separate list.
- Click a cell in the data range. If the range is not a single continuous block, select the exact range you intend to process.
- Open Data > Remove Duplicates.
- In the dialog, confirm whether the data has headers. Check the columns that define a duplicate, and clear the checkboxes for columns that should not be part of the comparison.
- Choose OK. Excel reports how many duplicate values were removed and how many unique values remain.
Suppose columns A through D contain Customer, Date, Product, and Amount, with headers in row 1. To identify a transaction by Customer, Date, and Product, select those three fields in the dialog and leave Amount unchecked. Excel removes every later row with the same three selected values, even if its Amount differs. If different amounts should make transactions distinct, include Amount as another selected column.
Excel normally detects the surrounding data range when you begin from a cell inside it. Check the dialog’s column names before confirming, especially when adjacent notes or totals could be mistaken for part of the table. If the data is an Excel table, the column headings make the selection easier to verify.
Which columns should you select?
The right columns depend on what one real-world record means in your work. Do not select every field automatically: a timestamp, comment, or changing status can make otherwise repeated records look unique. Conversely, selecting too few fields can delete legitimate records that happen to share those values.
- Contacts: Email address may be the key if each address should appear once. Name alone is often insufficient because different people can share a name.
- Orders: Order ID plus line-item number may identify a distinct line. Order ID alone could incorrectly merge several products on the same order.
- Events: Person, event date, and event type may define a repeat. Decide whether start time or location also distinguishes separate events.
- Inventory: SKU and location can be the key if the same item is tracked in multiple warehouses.
Write down the identity rule before removing anything. A practical test is to take two rows you know should be treated as the same record and confirm that every selected field matches. Then check a pair that should remain separate and confirm that at least one selected field differs.
Which matching rows does Excel keep?
Remove Duplicates keeps the first occurrence it encounters in the selected range and removes subsequent rows that match on the checked columns. The first row is determined by the worksheet’s current order, not by which row has the newest date, the largest amount, or the most complete information. If the desired keeper is not first, sort the data before using the command.
For example, if you want the newest record for each customer and product, sort the date column from newest to oldest, then remove duplicates using Customer and Product. The newest matching row will be encountered first. Review the sort carefully: sorting only one column instead of the whole data range can detach values from their corresponding records. Select the full range or sort the Excel table so each row stays intact.
There is no separate “keep latest” setting in the Remove Duplicates dialog. Sorting establishes which matching row appears first; the dialog then keeps that first row. Save a copy before the operation if the removed versions could be useful for comparison or audit purposes.
Preview duplicates with a formula
If you want to review matching records before deleting them, add a helper column that marks the first occurrence of each combination. For example, with Customer in A2:A100, Date in B2:B100, and Product in C2:C100, enter this in D2 and fill it down through D100:
=COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$C$2:C2,C2)=1
The result is TRUE for the first occurrence of each Customer-Date-Product combination and FALSE for later occurrences. In a small example where rows 2 and 4 share the same three values and row 3 has a different product, the results are TRUE, TRUE, and FALSE. Filter the helper column to FALSE to inspect later copies, or filter to TRUE to see the rows the formula identifies as first occurrences.
This formula marks records; it does not remove them. It also treats blank values as criteria values, so repeated combinations containing blanks can be marked as duplicates. Check how blanks should be interpreted in your data before relying on the results. The expanding ranges, such as $A$2:A2, make each row count matches from the first data row through the current row.
Create a separate unique list with UNIQUE
If the goal is a clean output without changing the original data, a dynamic array formula can return unique combinations. With Customer in A2:A100, Date in B2:B100, and Product in C2:C100, this returns one row for each distinct combination of all three columns:
=UNIQUE(A2:C100)
UNIQUE returns results in a spill range, so leave empty cells beneath and beside the formula for the output. Unlike Remove Duplicates, it leaves the source range intact. The formula above considers all three columns together and returns the values from those columns; it does not include Amount or other fields outside A:C. UNIQUE is available in Microsoft 365 and Excel 2021 or later.
If the fields used for comparison are non-adjacent, a formula can assemble them into a comparison array. With data in A2:D100 and the desired fields in columns A, C, and D, this returns unique combinations of those selected fields:
=UNIQUE(CHOOSECOLS(A2:D100,1,3,4))
CHOOSECOLS is available in Microsoft 365 and Excel 2024. This example returns only the chosen columns, not complete original rows. If you need the other fields attached to each retained record, use Remove Duplicates on the original range or build and verify a more specific extraction method rather than assuming this formula preserves the full record.
Common problems and checks
Rows that look identical on screen may not contain identical values. Extra spaces, different data types, or small differences in dates can affect results. A number stored as text may not behave like a numeric value, and a date with a time component can display as the same calendar date while storing different times. Use appropriate cleanup and inspect the underlying values if Excel keeps rows you expected it to merge.
- Unexpectedly few rows removed: A checked field may vary slightly, contain trailing spaces, or include hidden time values. Inspect the selected columns and standardize data where appropriate.
- Too many rows removed: You may have selected too few criteria columns. Include every field that makes a record meaningfully distinct.
- Wrong copy remains: Sort the complete dataset into the desired order before running the command, then confirm the intended row is first within each group.
- Headers appear in the results: Reopen the dialog and check the setting that indicates the data has headers. Confirm the selected range starts at the header row.
- Need to recover removed records: Use Undo immediately if available, or restore the saved copy. Do not assume a later review will recreate deleted rows.
For a table with formulas, consider whether the visible values or the underlying formulas matter to your workflow. Remove Duplicates compares the values represented in the selected cells; if formulas return the same displayed result, the rows may be duplicates under the chosen criteria even if their formulas differ. Preserve a copy if that distinction is important.
Remove Duplicates, UNIQUE, or Power Query?
Choose the tool based on whether you need to change the source, review a live result, or repeat a cleanup process:
- Remove Duplicates: Best for a one-time cleanup of a range or table. It deletes later matching rows from the selected data and keeps the first one.
- UNIQUE: Best for a separate, automatically updating list of unique combinations. The source remains unchanged, but the result is an output array rather than a cleaned copy of every original row.
- Power Query: Useful for repeatable imports and cleanup steps. In Microsoft 365 for Windows, load or select the data through Data > From Table/Range, select the comparison columns in the Power Query Editor, and choose Home > Remove Rows > Remove Duplicates. Load the result back to Excel. Refreshing can repeat the recorded transformation when the source changes.
Power Query’s duplicate removal also depends on the columns selected, so choosing the correct key remains essential. It is a better fit than a one-off command when new files arrive regularly and the same cleanup needs to be applied again. For a quick decision on a sheet, the Data tab command is usually simpler.
Frequently Asked Questions
Can Excel remove duplicates based on two or more columns?
Yes. Open Data > Remove Duplicates and check all the columns that jointly define a duplicate. Excel removes later rows only when the selected-column values match as a combination.
Does Remove Duplicates delete the whole row?
Yes. It removes the later matching row from the selected data range, not just the repeated values in the checked columns. The checked columns determine the match; they are not the only cells removed.
Can I keep the newest duplicate instead of the first one?
Yes, if you sort the complete dataset so the newest record comes first within each group before removing duplicates. The command itself keeps the first encountered match and has no separate newest-record option.
Can I highlight duplicates using multiple columns without deleting them?
You can use a helper formula such as the COUNTIFS example above to mark first and later occurrences, then filter or review the results. Conditional formatting can also highlight duplicates, but for a multi-column combination a helper formula is often easier to inspect and control.
Does UNIQUE remove duplicates from the original table?
No. UNIQUE creates a separate spilled result and leaves the source data unchanged. Use Remove Duplicates for an in-place cleanup, or use the formula when you want a separate unique list.
Is the process different on Mac or Excel for the web?
Excel for Mac also provides a Remove Duplicates command on the Data tab, though the dialog layout can differ slightly by release. Menu availability and layout in Excel for the web can vary; verify the selected columns and resulting rows before relying on a web-based cleanup.
Related
- Excel Power Query: Split a Cell Into Multiple Rows
- Excel Power Query and SQL: How They Work Together
- Excel Power Query Shortcuts: Refresh, Edit, and More
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Named Range Won’t Delete? Causes and Fixes to TryNo—Excel can delete most named ranges, which it calls defined names. In Excel for Microsoft 365 on Windows,…
- Excel Keyboard Shortcut to Select an Entire Row FastIn Excel for Microsoft 365 on Windows, press Shift+Space to select the entire worksheet row containing the active…
- How to Delete Grouping in Excel: Rows, Columns & SheetsTo delete a row or column grouping in Excel, select the grouped rows or columns, then choose Data…
- Make a Word Form Fillable but Not EditableTo make a Word form fillable but keep its layout and instructions from being edited, add form controls,…
- How to Delete an Excel File on the Web in OneDriveTo delete an Excel file online, find it in the OneDrive or SharePoint location where it is stored,…
- Copy and Unhide Cells in Excel: Step-by-Step GuideTo unhide and copy data in Excel for Microsoft 365 on Windows, select the rows or columns around…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.