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.

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

Click 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.

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 […]

How to use absolute and relative references

Step 2 - Find duplicates

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


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


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



and returns TRUE in cell G13

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.

How to use the COUNTIFS function

Filter records based on formula

  1. Click on black arrow on cell F2
  2. Enable only value TRUE
  3. Click OK button

Download Excel file

* You will also get a weekly newsletter, unsubscribe anytime!