This article describes how to highlight duplicate records and filter duplicate records. There is one record in each column, cell range B2:E5. See picture below.

The array formula  and conditional formatting formula in this article contain "Countifs", a function introduced in excel 2007. "Countifs" applies criteria to cells across multiple ranges and counts the number of times all criteria are met.

### Highlight duplicate records

Conditional formatting formula:

=COUNTIFS(\$B\$2:\$E\$2, B\$2, \$B\$3:\$E\$3, B\$3, \$B\$4:\$E\$4, B\$4, \$B\$5:\$E\$5, B\$5)>1

The conditional formatting formula uses absolute and relative cell references.

How to use conditional formatting formula:

1. Select cells A1:C30
2. Click "Home" tab
3. Click "Conditional Formatting" button
4. Click "New Rule.."
5. Click "Use a formula to determine which cells to format"
6. Type =COUNTIFS(\$B\$2:\$E\$2, B\$2, \$B\$3:\$E\$3, B\$3, \$B\$4:\$E\$4, B\$4, \$B\$5:\$E\$5, B\$5)>1 in "Format values where this formula is TRUE" window.
7. Click "Format.." button
8. Click "Fill" tab
9. Select a color for highlighting cells.
10. Click "Ok"
11. Click "Ok"
12. Click "Ok"

### Filter duplicate records (array formula)

Array formula in cell B9:

=INDEX(\$B\$2:\$E\$5, ROW(A1), SMALL(IF(COUNTIFS(\$B\$2:\$E\$2, \$B\$2:\$E\$2, \$B\$3:\$E\$3, \$B\$3:\$E\$3, \$B\$4:\$E\$4, \$B\$4:\$E\$4, \$B\$5:\$E\$5, \$B\$5:\$E\$5)>1, COLUMN(\$B\$2:\$E\$2)-MIN(COLUMN(\$B\$2:\$E\$2))+1), COLUMN(A1)))

Copy cell B9 and paste it to the right as far as needed. Then copy cells and paste them down as far as needed.

filter duplicate columns.xlsx
(Excel 2007 Workbook *.xlsx)

### Functions used in this formula:

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

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

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

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text