How to Find and Remove Duplicate Rows Based on Multiple Columns

To find duplicate rows based on specific columns rather than whole rows, use COUNTIFS with one pair of arguments per column you care about: =COUNTIFS(A$2:A$100, A2, B$2:B$100, B2) returns how many rows share both values. Anything above 1 is a duplicate on those columns, even if other columns differ. Excel’s built-in Remove Duplicates does the same comparison but deletes immediately, so flagging first is safer.

Excel’s built-in “Remove Duplicates” feature works fine when duplicates are obvious: identical rows in every column. But what about when you need to find duplicates based on specific columns only? An order might appear twice with the same customer and date but different notes. A contact list might have the same person with slightly different phone numbers. Here’s how to find and handle duplicates based on the columns that actually matter.

Sample order data with partial duplicates

	A	B	C	D
1	Customer	Date	Amount	Notes
2	Acme Corp	3/15/2026	$1,200	Initial order
3	Bright LLC	3/16/2026	$850	Rush delivery
4	Acme Corp	3/15/2026	$1,200	Duplicate entry
5	Chen Inc	3/17/2026	$2,400	Standard
6	Bright LLC	3/16/2026	$900	Corrected amount

Rows 2 and 4 are duplicates based on Customer + Date + Amount. Rows 3 and 6 match on Customer + Date but have different amounts. That might be a correction, not a true duplicate. The right approach depends on which columns you consider.

Method 1: COUNTIFS Formula to Flag Duplicates

Add a helper column that counts how many times each combination of Customer + Date appears:

Wrap it in an IF for a clear flag:

What the COUNTIFS duplicate flag returns

	A	B	C	E
1	Customer	Date	Amount	Status
2	Acme Corp	3/15/2026	$1,200	Duplicate
3	Bright LLC	3/16/2026	$850	Duplicate
4	Acme Corp	3/15/2026	$1,200	Duplicate
5	Chen Inc	3/17/2026	$2,400	Unique
6	Bright LLC	3/16/2026	$900	Duplicate

Method 2: Concatenation Key for Easy Filtering

Create a unique key by combining the columns you care about, then use COUNTIF on that key. The key goes in column E here; if column E already holds Method 1’s flags, use a free column and change E in the formula to match:

Method 3: Built-In Remove Duplicates

For a quick cleanup when you just want to delete duplicates:

1Select your data range (including headers). If you added a flag or key column (Methods 1 and 2), include it and leave its box unchecked in step 3, so the flags stay with their rows.

2Go to Data → Remove Duplicates

3Uncheck columns you don’t want to compare: this is the key step. Leave only the columns that define a “duplicate” checked.

4Click OK. Excel deletes duplicate rows and tells you how many were removed.

Conditional Formatting to Highlight Duplicates Visually

To highlight duplicates without modifying data:

1Select column A

2Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values

For multi-column duplicates, add a helper key column first (Method 2) and apply the conditional formatting to that column.

Common questions

Can I undo Remove Duplicates?

Ctrl+Z works immediately afterwards, but not once the file is saved and closed. Flag duplicates with a formula first, review them, then delete.

Is duplicate matching case sensitive?

No. Both COUNTIFS and the built-in Remove Duplicates treat “Acme” and “ACME” as the same value.

Which duplicate does Excel keep?

The first occurrence in the current sort order, and it discards the rest. So sort deliberately before running it, because which row survives depends entirely on that order.

Watch it done, 2:48. The same person three times in a contact list: the emails cleaned first so the copies match, then each group reviewed and one row kept, in Excel Data Cleaner.