To remove duplicates based on two columns in Excel, select the entire data range, choose Data > Remove Duplicates, and in the dialog select only the two columns that define a duplicate. Excel keeps the first row for each distinct pair and removes later matching rows, including their other data.
What “duplicate based on two columns” means
A record is a duplicate when the values in both chosen columns match another row. The pair is treated as a combined key: a match in just one column is not enough. For example, if the key columns are Customer ID and Region, two rows match only when both the ID and region match.
That is different from removing identical rows. Suppose a table has Customer ID, Region, and Order Date. Two records with the same ID and region but different order dates count as duplicates when you choose only the first two columns. Excel removes one whole row, so the date and every other field in that removed record go with it. If you want to remove only rows identical across every field, select every column in the Remove Duplicates dialog instead.
Use this operation when your rule is “one row per combination of these two values,” such as one entry per product and warehouse or one contact per email address and mailing list. Decide that rule before deleting anything: two records can share a pair of values and still represent separate, legitimate events.
Remove duplicates using the Excel ribbon
In Excel for Microsoft 365 on Windows, work from a copy or save the workbook before making a permanent change. Then follow these steps:
- Click a cell in the dataset, or select the full data range, including all columns whose row information must stay together. Do not select only the two key columns if the rest of the row belongs to the record.
- Choose Data > Remove Duplicates. If the data is formatted as an Excel table, the command is available from the Data tab as well.
- In the dialog, check My data has headers if the first row contains column names. This prevents the header labels from being treated as data.
- Clear Select All, then check just the two columns to use as the duplicate key. The dialog labels the checkboxes with the column headers when it recognizes them.
- Check the selected columns and header setting, then choose OK. Excel reports how many duplicate values were found and how many unique values remain.
The command removes rows from the selected range; it is not a filter that merely hides them. Immediately after the operation, Ctrl+Z on Windows can undo it. Save a backup first if the original rows may be needed later. In Excel for Mac, the command is also on the Data tab as Remove Duplicates; ribbon appearance can differ by version, but the key choice is the same: select the two comparison columns in the dialog while including the complete records in the source range.
A concrete example
Imagine a list with columns Product, Warehouse, and Quantity:
- Pen, East, 40
- Notebook, East, 25
- Pen, East, 12
- Pen, West, 30
If Product and Warehouse are the selected columns, the first and third rows have the same pair: Pen and East. Excel keeps the first matching row and removes the third row, including its Quantity value of 12. The Notebook/East and Pen/West rows remain because each has a different pair.
This example highlights a decision that often matters: which quantity should survive? Remove Duplicates does not add the quantities together or choose the larger one. It retains one row and discards the other. If the desired result is a combined quantity, summarize the data with a PivotTable or a suitable aggregation in Power Query instead of deleting duplicate rows.
Which matching row does Excel keep?
Excel keeps the first occurrence it encounters in the selected range and removes later occurrences of the same pair. “First” means first in the range’s current row order; it does not mean earliest date, highest value, or most complete record. If the retained record matters, sort the data before using Remove Duplicates. For example, sort by Date from oldest to newest to retain the earliest row, or by Status so the preferred status appears first.
Sorting can change the order of all records, so consider adding a temporary index column first if you need to restore the original order. After deduplication, you can sort by that index. Another option is to copy the data to a new sheet and deduplicate the copy, preserving the source for review.
Do not assume that Excel will compare every column just because the full range is selected. The checkboxes in the dialog control the comparison key. The full range controls which rows are affected; the checked columns control which rows are considered duplicates.
Preview duplicates with a formula
If you want to inspect matches before deleting them, add a helper column. With the two key fields in A and B, headers in row 1, and data beginning in row 2, enter this in C2 and fill it down:
=COUNTIFS($A$2:A2,A2,$B$2:B2,B2)>1
The formula returns FALSE for the first occurrence of a pair and TRUE for later occurrences. For example, with A2:B5 containing Pen/East, Notebook/East, Pen/East, and Pen/West, C2:C5 returns FALSE, FALSE, TRUE, FALSE. Filter the helper column to TRUE to review the rows that would be removed. The expanding ranges make each row count matches from the start of the list through that row.
This formula is useful for checking the outcome or marking duplicates without deleting anything. It is not identical in every respect to the ribbon command: COUNTIFS comparisons are not case-sensitive, and it treats matching criteria according to Excel’s criteria rules. Check special data, such as text that looks like a number, before relying on a formula as a perfect preview.
Common problems and data checks
- Only the key columns were selected. If you need to preserve row alignment across other fields, select the complete dataset before opening Remove Duplicates. Otherwise, removing cells from a partial selection can leave related columns out of step.
- Spaces or hidden characters differ. “North” and “North ” may look alike but contain different text. Imported data can also include nonprinting characters. Clean or standardize the source first, for example with TRIM for ordinary extra spaces, and inspect values that appear identical but are not matched.
- Blank cells form repeated pairs. If both key cells are blank on multiple rows, those rows can be treated as the same pair. If blank keys are invalid or should be preserved separately, filter or correct them before deduplicating.
- The wrong duplicate survives. The command retains the first row in the current order. Sort by the field that defines the preferred survivor before running it, or review with a helper column first.
- A table or range has unexpected boundaries. Check that the selected range includes every record and all associated columns, and that there are no unrelated rows or notes inside it. For a table, verify that the intended fields are included.
For important datasets, compare the row count before and after and inspect a few retained pairs. Excel’s completion message is useful, but it does not confirm that the chosen columns express the right business rule.
Alternatives when you do not want to delete rows
To create a separate list of distinct two-column combinations while leaving the source intact, use UNIQUE. With the two key columns in A2:B100 in Excel 2021 or Microsoft 365, enter =UNIQUE(A2:B100) in an empty cell. Excel spills a list of distinct row pairs into the cells below and beside it. For the example above, the output contains Pen/East, Notebook/East, and Pen/West. This returns the key columns, not the other fields of a chosen original record.
Conditional Formatting can highlight duplicate values for visual review, but it does not remove records and a rule must be designed to assess the combination of two columns. A helper formula is often clearer for that purpose. Power Query is a better fit for repeatable cleanup: load the data, select the two key columns as the columns to compare, and use the Remove Rows > Remove Duplicates command in the Power Query Editor. You can refresh the query when the source changes rather than repeatedly editing the original data.
Choose based on the desired result: Remove Duplicates changes the selected data, UNIQUE produces a separate distinct list, and Power Query provides a refreshable transformation. If you need to combine information from duplicate records, use a summary or aggregation method rather than any method that simply retains one row.
Frequently Asked Questions
Can Excel remove duplicates using two columns but keep all other columns?
Yes. Select the entire data range, then select only the two key columns in the Remove Duplicates dialog. Excel compares those columns and removes later matching rows from the full selected range, including their other values.
Does Excel compare the two columns as a pair?
Yes. A duplicate requires both selected values to match another row. If the first column matches but the second differs, the pair is unique and both rows remain.
Does Remove Duplicates keep the first or last matching row?
It keeps the first occurrence in the current range order and removes later ones. Sort first if a particular date, status, or other record should be retained.
Can I remove duplicates without changing the original list?
Yes. Use UNIQUE on the two-column range in Excel 2021 or Microsoft 365 to return distinct pairs elsewhere, or create a separate copy before using Remove Duplicates. UNIQUE returns the pairs, not a complete selected record from the source.
Why are rows that look identical not removed?
Check for trailing spaces, nonprinting characters, different underlying values, or a difference in one of the two selected fields. Normalize the data and confirm that the dialog has the intended columns checked.
Choose the method that matches the goal
For a one-time cleanup where one row per two-column combination is the intended result, use Data > Remove Duplicates on the complete range and check only the two key fields. Sort first if the retained record matters. For review, use a helper formula; for a non-destructive list of distinct pairs, use UNIQUE; and for a repeatable import process, consider Power Query. The crucial step is defining what counts as the same record before removing anything.
Related
- MS Word Data Table: How to Create, Format, and Use It
- Microsoft Word Remove Duplicates: Best Ways to Clean Text
- Microsoft Excel Workbook: What It Is and How to Use One
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Family Tree Template in Word: Create and Customize OneTo create a family tree template in Microsoft Word, set the page to landscape, add editable boxes for…
- Excel Data Table Relationships and How to Create ThemIn Excel, table relationships link separate tables through matching key columns, letting PivotTables and the Data Model analyze…
- How to Share Excel Files in Google Sheets With OthersTo share an Excel workbook in Google Sheets, upload the .xlsx file to Google Drive, open it in…
- How to Create Calendar Rules in Outlook for EventsOutlook does not have a general calendar rule engine that automatically changes calendar events when they meet conditions.…
- How to Add a Watermark in Word 365, Text or PictureTo add a watermark in Word 365 on Windows, open the document and choose Design > Watermark, then…
- Highlight Duplicates in Google Sheets: How to Do ItYes. In Google Sheets in a web browser, choose Format > Conditional formatting, select the cells to check,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.