Formula in cell G3:

=IF(COUNTIFS($B$3:B3, B3, $C$3:C3, C3, $D$3:D3, D3, $E$3:E3, E3)>1, "Duplicate", "") + ENTER

Copy cell G3 and paste down as far as needed.

How the formula works in cell G13

Step 1 - Understand how relative and absolute cell references work

The formula uses absolute and relative cell references. In cell G3 the formula is:

=IF(COUNTIFS($B$3:B3, B3, $C$3:C3, C3, $D$3:D3, D3, $E$3:E3, E3)>1, "Duplicate", "")

In cell G13 the formula has changed to:

=IF(COUNTIFS($B$3:B13, B13, $C$3:C13, C13, $D$3:D13, D13, $E$3:E13, E13)>1, "Duplicate", "")

Relative and absolute cell references

Step 2 - Find duplicates

=IF(COUNTIFS($B$3:B13, B13, $C$3:C13, C13, $D$3:D13, D13, $E$3:E13, E13)>1, "Duplicate", "")


=IF(COUNTIFS({"Sample0"; "Sample0"; "Sample1"; "Sample0"; "Sample0"; "Sample1"; "Sample1"; "Sample0"; "Sample1"; "Sample1"; "Sample1"}, B13, {"B"; "B"; "A"; "A"; "B"; "B"; "B"; "A"; "A"; "A"; "A"}, C13, {11; 11; 11; 10; 10; 10; 11; 11; 10; 11; 11}, D13, {"AA111";"AA110";"AA111";"AA111";"AA110";"AA111";"AA111";"AA110";"AA110";"AA110";"AA111"}, E13)>1, "Duplicate", "")


=IF(COUNTIFS({"Sample0"; "Sample0"; "Sample1"; "Sample0"; "Sample0"; "Sample1"; "Sample1"; "Sample0"; "Sample1"; "Sample1"; "Sample1"}, "Sample1", {"B"; "B"; "A"; "A"; "B"; "B"; "B"; "A"; "A"; "A"; "A"}, "A", {11; 11; 11; 10; 10; 10; 11; 11; 10; 11; 11}, 11, {"AA111";"AA110";"AA111";"AA111";"AA110";"AA111";"AA111";"AA110";"AA110";"AA110";"AA111"}, "AA111")>1, "Duplicate", "")


=IF(2>1, "Duplicate", "")


=IF(TRUE, "Duplicate", "") and returns Duplicate in cell G13

Download excel sample file

Find duplicate records.xlsx
(Excel 2007 Workbook *.xlsx)


IF(logical_test, [value_if_true], [value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

COUNTIFS(criteria_range1,criteria1, criteria_range2, criteria2...)
Counts the number of cells specified by a given set of conditions or criteria