Author: Oscar Cronquist Article last updated on October 02, 2018

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.

Download excel sample file for this tutorial.

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

Recommended blog post
Filter duplicate rows in excel 2007
Automatically filter unique distinct row records
Highlight duplicate rows in excel 2007

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

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

Returns the smallest number in a set of values. Ignores logical values and text