To remove duplicate records based on two columns in Excel for Microsoft 365 on Windows, select the entire data range, choose Data > Remove Duplicates, and in the dialog select only the two columns that define a duplicate. Excel compares those two values, keeps the first matching row, and removes later matching rows from the selected range.
What Excel considers a duplicate
Excel treats a row as a duplicate when its values match the values in both selected columns. For example, if columns A and B contain a customer name and an order number, the pair “Mira Chen” and “1048” is the comparison key. Another row is a duplicate only if it has the same customer and the same order number. A matching name with a different order number is a different pair.
The two selected columns do not have to be next to each other. In the Remove Duplicates dialog, you can select the relevant headers from a table with many fields—for example, compare Customer ID and Product Code while keeping the date, price, and notes attached to each retained row. Excel removes whole records from the range, not just the repeated values in the two key columns.
For a repeated pair, the standard command keeps the first occurrence in the current row order and removes the later occurrences. It does not combine information from duplicate rows or add their quantities together. If the rows differ in other columns, decide which record should survive before removing anything.
Remove duplicates using the ribbon
- Make a backup or confirm that you can undo the change. Removing duplicates deletes rows from the selected data.
- Select the full table or data range, including every column that belongs to each record. If the range has headings, include the header row.
- On the ribbon, choose Data > Remove Duplicates.
- In the dialog, turn on My data has headers if the first row contains column names.
- Choose Unselect All, then check only the two columns that should define a duplicate. Their order does not affect the comparison.
- Click OK. Read the confirmation message to see how many duplicate values were removed and how many unique values remain, then click OK again.
For instance, suppose a table contains Name, Order ID, Date, and Amount. To keep only one row for each name-and-order-ID combination, select the entire table but check only Name and Order ID in the dialog. Excel keeps the first row for each pair, including its date and amount, and deletes later rows with that same pair. It does not compare Date or Amount unless you also check those fields.
Why selecting the whole table matters
A frequent mistake is selecting only the two key columns before running the command. Excel may offer to expand the selection to include adjacent data or continue with the current selection. If you continue with just the key columns, Excel can remove cells in those columns without removing the corresponding cells in the other columns. The remaining rows can then contain mismatched names, dates, or amounts.
The safer method is to select the complete rectangular data range first and use the dialog to specify the comparison columns. If Excel asks whether to expand the selection, choose the option that includes the adjacent data so records stay together. If the relevant columns are separated by unrelated areas or the data is not a single contiguous table, select the exact full range deliberately before opening the dialog.
Excel Tables make the range easier to manage. Click a cell in the table and open Data > Remove Duplicates; the command works on the table, while the dialog lets you choose the key columns. If the table has headers, verify the header setting before proceeding. Removing rows changes the table itself, so formulas and references that depend on its records may update.
Choose the right columns and the right row to keep
Before deleting anything, define what “duplicate” means for the task. Two transactions from the same person may be separate transactions if their transaction IDs differ. Two inventory lines with the same product and warehouse might be duplicates, or they might represent separate batches. The columns you check encode that decision: adding a column makes the match stricter, while leaving it out makes more rows qualify as duplicates.
- Use stable identifiers when available, such as an account ID plus a period, rather than a name that may be spelled in different ways.
- Check the other fields in rows that share the key. If their dates, quantities, or statuses conflict, decide which record is authoritative instead of letting row order make that decision accidentally.
- Sort first only when appropriate. If the first row should be the newest or highest-priority record, sort the full table by that rule before removing duplicates. Sorting changes the row order, so check that the chosen order is correct.
“First” means first in the order Excel processes the selected range; it does not mean newest, oldest, most complete, or highest value. The command is a deletion tool, not a data-merging tool. If a duplicate group contains useful information spread across several rows, reconcile those records before deleting any of them.
Preview pairs with a formula instead
If you want to inspect the unique combinations without changing the source, the UNIQUE function can return distinct two-column pairs in a separate area. This is useful for a quick list of keys, but it returns just the specified columns; it does not choose a source row and retain all the other fields from that row.
With the two key columns in A2:B6, enter this formula in an empty cell such as D2:
=UNIQUE(A2:B6)
Suppose A2:B6 contains the pairs Lee/501, Patel/502, Lee/501, Diaz/503, and Patel/502. The formula spills three rows: Lee/501, Patel/502, and Diaz/503. The spill area must be clear. UNIQUE is available in Microsoft 365 and Excel 2021 or later; it is not available in every older Excel release. This result is a live formula output, so changes to the source can change the list.
Keep full rows with a helper column
When you need to preserve complete records but want to review the duplicates first, add a helper column that numbers each occurrence of the two-column key. For example, assume the key fields are in A and B, the data begins in row 2, and the helper column is D. In D2, enter:
=COUNTIFS($A$2:A2,A2,$B$2:B2,B2)
Fill the formula down alongside the data. The first occurrence of each pair returns 1; its second occurrence returns 2, its third returns 3, and so on. For example, if A2:B5 contains Lee/501, Patel/502, Lee/501, and Lee/501, the results are 1, 1, 2, and 3. Filter the helper column to values greater than 1 to review later occurrences; filter to 1 to display the first records. Because the formula counts from the first data row down to the current row, the row order determines which record is marked first.
This approach is reversible while you are reviewing: clear the filter or remove the helper column without deleting the source records. If you decide to delete the later occurrences, select the full rows shown by the filter and delete those rows, rather than clearing only the key cells. Check that the table or range includes all associated columns.
Common issues and comparisons
Excel’s duplicate check is based on cell values, not on whether two cells look identical on screen. Formatting such as font color or number format does not make otherwise matching values different. Conversely, values that appear alike may differ underneath: a number stored as text can behave differently from a numeric value, and names with leading or trailing spaces may not match the cleaned version. Dates are stored as numeric values, so two dates with different underlying times may not be identical even if their display format hides the time.
Case alone generally does not distinguish text for Remove Duplicates: entries such as “North” and “north” are treated as duplicates. If capitalization, spaces, or inconsistent data entry matter, standardize or inspect the columns before removing rows. For example, use a separate cleanup column or a review process to trim spaces and make spelling consistent, then compare the cleaned keys. Keep the original fields until you have confirmed the cleaned results.
Choose a method based on the outcome you need:
- Remove Duplicates: best for permanently reducing a range to one record per selected pair. It changes the source data.
- UNIQUE: best for producing a separate, updating list of distinct pairs. It does not delete source rows or return the rest of each record by itself.
- A helper column: best for identifying and reviewing later occurrences while retaining the original rows until you decide what to do.
- Power Query: useful for repeatable cleanup of imported data. In Excel for Microsoft 365 on Windows, load the data with Data > From Table/Range, select the two key columns in the Power Query Editor, and use Home > Remove Rows > Remove Duplicates. Load the result back to Excel when ready. The original source can remain available, and the query can be refreshed after source data changes.
Power Query is especially helpful when the same cleanup must be repeated, but it is a separate transformation workflow rather than an immediate edit to the worksheet range. Check the preview and the columns selected before loading the result. If you need a particular record to win within each duplicate group, establish and verify a clear ordering or review rule rather than assuming the tool will select the record you intend.
Undo, backups, and platform notes
Remove Duplicates is destructive to the current range, but it can usually be reversed immediately with Ctrl+Z on Windows or Command+Z on Mac. Undo may no longer be available after closing the workbook or performing other actions, so do not treat it as a substitute for a backup. Before a large cleanup, save a copy of the workbook or duplicate the worksheet.
In Excel for Mac, the Remove Duplicates command is also available from the Data tab, though the ribbon layout can vary by release. Excel for the web may present a different interface or feature availability from the Windows desktop app; if you cannot find the command, use the desktop version or use a formula-based review method supported by your version. The core decision remains the same on any platform: select the full records, then compare only the two intended key columns.
Frequently Asked Questions
Does Excel remove the entire row or just the two duplicate cells?
It removes duplicate rows from the selected range. Select the full table or range first; selecting only two columns risks separating those cells from the rest of each record.
Can I remove duplicates based on two columns that are not next to each other?
Yes. Select the complete data range, open Data > Remove Duplicates, and check the two relevant column names in the dialog. The key columns do not have to be adjacent.
Which duplicate row does Excel keep?
The command keeps the first occurrence in the range’s current order and removes later matches. Sort or review the data first if a particular date, status, or version should be retained.
Can I highlight duplicates based on two columns without deleting them?
Yes. Use a helper column with the COUNTIFS formula above to mark first and later occurrences, then filter or inspect the results. This lets you review the records before deciding whether to delete any rows.
Why did Excel keep records that look the same?
The underlying values may differ, even when formatting makes them look alike. Check for extra spaces, numbers stored as text, hidden time values in dates, or differences in other key fields. Clean or standardize the comparison values before running the removal again.
Related
- Excel Protect Sheet: Can Users Expand Groups?
- Excel Protect Sheet: Can You Insert Table Rows?
- Excel Protect Sheet: Can You Refresh a PivotTable?
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Identify Duplicate Values in Excel: A Step-by-Step GuideTo identify duplicate values in Excel, select the cells you want to check, then choose Home > Conditional…
- How to Mail Merge in Word 365: A Step-by-Step GuideTo mail merge in Word 365, create or open a recipient list, choose a merge type from the…
- How to Set an Out of Office Reply in Outlook on PhoneTo set an out-of-office reply in Outlook on your phone, open the Outlook app, go to Settings, select…
- Excel Page Break Button Locked? Common Causes and FixesIn Excel for Microsoft 365 on Windows, the Page Break command is usually unavailable because Excel is editing…
- Excel Conditional Formatting for Dates Before TodayTo highlight dates before today in Excel, select the date cells and create a formula-based conditional formatting rule…
- Copy a Dropdown in Google Sheets: Steps and FixesTo copy a dropdown in Google Sheets, copy the cell that contains it, select the destination cell, and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.