Author: Oscar Cronquist Article last updated on January 11, 2019

In this article I will demonstrate a technique to filter duplicate records. The picture below shows you a data set in columns B to E. Next step is to create an Excel defined table.

Press with left mouse button on a cell in the table, go to tab "Insert" on the ribbon. Press with left mouse button on "Insert Table" button. The following dialog box appears.

Press with left mouse button on OK button.

Type Duplicate in cell F2 and then use the following formula in cell F3:

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

How the formula works in cell F13

Step 1 - Understand how relative and absolute cell references work

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

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

In cell G13 the formula has changed to:

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

The cell ranges expand as the formula is copied to cells below. This lets you count the current record in above records.

Recommended articles

How to use absolute and relative references
What is a reference in Excel? Excel has an A1 reference style meaning columns are named letters A to XFD […]

Step 2 - Find duplicates

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

becomes

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

becomes

=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", "")

becomes

=2>1

and returns TRUE in cell G13

Recommended articles

How to use the COUNTIFS function
Checks multiple conditions against the same number of cell ranges and counts how many times all criteria are met.

Filter records based on formula

  1. Press with mouse on black arrow on cell F2
  2. Enable only value TRUE
  3. Press with left mouse button on OK button

Get the Excel file


Find-duplicate-recordsv2.xlsx