## Highlight duplicate columns

*Article updated on December 21, 2017*

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:

The conditional formatting formula uses absolute and relative cell references.

How to use conditional formatting formula:

- Select cells A1:C30
- Click "Home" tab
- Click "Conditional Formatting" button
- Click "New Rule.."
- Click "Use a formula to determine which cells to format"
- 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.
- Click "Format.." button
- Click "Fill" tab
- Select a color for highlighting cells.
- Click "Ok"
- Click "Ok"
- Click "Ok"

### Filter duplicate records (array formula)

Array formula in cell B9:

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

**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

Compare two lists of data: Highlight records occurring in only one list

Overview There are two lists in this example: Sheet: List 1 Sheet: List 2 Create named ranges Select A2:A13 on […]### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

## Share this article