## Highlight duplicate values in a cell range

*Article updated on December 22, 2017*

**Question: **How do I highlight duplicate values in a range (A1:D4) using conditional formatting?

**Answer:**

**Conditional formatting formula:**

**Highlight all values having duplicates**

**Named ranges**

tbl (G5:J8)

What is named ranges?

**How to implement conditional formatting formula to your workbook in excel 2007**

For example, your range is D4:G9. Change named range *tbl *to D4:G9.

- Select your range D4:G9.
- Go to "Home" tab
- Click Conditional formatting
- Click "New Rule.."
- Click "Use a formula to determine what cells to format"
- Change A1 in the above conditional formatting formula to D4. Your range is D4:G9, remember?.
- Copy the above conditional formatting formula to "Format values where this formula is true:"
- Click Format button
- Select a formatting you like. For example, cells filled with yellow.
- Click OK
- Click OK

**Download excel *.xlsx file**

highlight-duplicate-values-in-a-range1.xlsx

### Functions in this article

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

**ROW(**reference**)**

returns the row number of a reference

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

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

**MAX(**number1,[number2],**)
**Returns the largest value in a set of values. Ignores logical values and text.

**MATCH(**lookup_value;lookup_array; [match_type]

Returns the relative position of an item in an array that matches a specified value

**COLUMN(**reference**)** Returns the column number of a reference

**MIN(**number1,[number2]**)**

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

### 2 Responses to “Highlight duplicate values in a cell range”

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

When you modiy to move to anu other column apart from 'A' the the first instance of the duplicate also highlights.

David Gordon,

You are right, I believe the new formula I have added to this post is more useful.

Thanks for commenting!