# How to highlight duplicate values

#### Table of Contents

- How to highlight duplicate values
- Highlight the smallest duplicate number
- Highlight more than once taken course in any given day
- Highlight duplicates in two columns
- Highlight duplicate columns
- Highlight duplicates with same date, week or month
- Highlight duplicate records/rows
- Highlight duplicate values in a cell range
- Highlight duplicates in a filtered Excel Table

## 1. How to highlight duplicate values

The picture above shows duplicate values in column B, only the second or more duplicates are colored and easily identified.

To sort all duplicates to the bottom of the list for removal, creating a unique distinct list. See this blog post

How to create a unique list using conditional formatting in excel 2007

To color duplicate cells I use conditional formatting in excel. The conditional formatting formula in B3:B11:

### 1.1 Explaining CF formula

#### Step 1 - Expanding cell reference

The first argument in the COUNTIF function expands as the CF moves to cells below, this makes it easy to spot duplicate values as their count is 2 or larger.

Cell |
First argument |

B3 | $B3:$B$3 |

B4 | $B4:$B$3 |

B5 | $B5:$B$3 |

#### Step 2 - Absolute cell reference

The second argument changes to the current cell.

Cell |
Second argument |

B3 | B3 |

B4 | B4 |

B5 | B5 |

#### Step 3 - Count current value in expanding cell range

COUNTIF($B3:$B$3, B3)

becomes

COUNTIF("VV", "VV")

and returns 1

Cell |
COUNTIF |
Evaluates to |
Result |

B3 | COUNTIF($B3:$B$3, B3) | COUNTIF("VV", "VV") | 1 |

B4 | COUNTIF($B4:$B$3, B4) | COUNTIF({"VV","AA"}, "AA") | 1 |

B5 | COUNTIF($B5:$B$3, B5) | COUNTIF({"VV","AA","DD"}, "DD") | 1 |

#### Step 4 - Check if value is larger than 1

COUNTIF($B3:$B$3, B3)>1

becomes

1>1

and returns FALSE. Cell B3 is not highlighted.

**1.2 How to highlight duplicate values occurring the second time or more using conditional formatting in Excel**

- Select the range (B3:B10)
- Press with left mouse button on the "Home" tab on the ribbon
- Press with left mouse button on "Conditional formatting"
- Press with left mouse button on "New rule..."
- Press with left mouse button on "Use a formula to determine which cells to format"
- Press with left mouse button on "Format values where this formula is true" window.
- Type COUNTIF($B3:$B$3, $B3)>1
- Press with left mouse button on "Format" button
- Go to tab "Fill"
- Pick a color
- Press with left mouse button on OK button
- Press with left mouse button on OK button again to return to Excel

### 1.3 Get Excel example file

highlight-duplicates-using-conditional-formatting.xls

(Excel 97-2003 Workbook *.xls)

## 2. Highlight the smallest duplicate number

**Question:** How do I highlight the smallest duplicate value in a column using conditional formatting?

**Answer:**

Conditional formatting formula in A2:

**2.1 How to apply conditional formatting**

- Select cell range
- Go to "Home" tab
- Press with left mouse button on Conditional formatting
- Press with left mouse button on "New Rule.."
- Press with left mouse button on "Use a formula to determine what cells to format"
- Copy the above conditional formatting formula to "Format values where this formula is true:"
- Press with left mouse button on Format button
- Select a formatting you like.
- Press with left mouse button on OK
- Press with left mouse button on OK

### 2.2 Explaining CF formula in cell A2

#### Step 1 - Identify duplicates

The COUNTIF function counts values based on a condition or criteria.

COUNTIF($A$2:$A$11, $A$2:$A$11)>1

becomes

COUNTIF({6;4;6;12;18;12;10;3;11;8}, {6;4;6;12;18;12;10;3;11;8})>1

becomes

{2;1;2;2;1;2;1;1;1;1}>1

and returns

{TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE}.

#### Step 2 - Extract duplicate numbers

The IF function uses a logical expression in order to determine which argument to return.

IF(COUNTIF($A$2:$A$11, $A$2:$A$11)>1, $A$2:$A$11, "")

becomes

IF({TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE}, $A$2:$A$11, "")

becomes

IF({TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE}, {6;4;6;12;18;12;10;3;11;8}, "")

and returns

{6;"";6;12;"";12;"";"";"";""}.

#### Step 3 - Identify the smallest number in array

The MIN function extracts the minimum value in a cell range or array.

MIN(IF(COUNTIF($A$2:$A$11, $A$2:$A$11)>1, $A$2:$A$11, ""))

becomes

MIN({6;"";6;12;"";12;"";"";"";""})

and returns 6.

#### Step 4 - Compare smallest number to current cell

MIN(IF(COUNTIF($A$2:$A$11, $A$2:$A$11)>1, $A$2:$A$11, ""))=A2

becomes

6=A2

becomes

6=6

and returns TRUE. Cell A2 is highlighted.

### Get Excel *.xls file

highlight-smallest-duplicate-value-in-a-column.xls

## 3. Highlight more than once taken course in any given day

**Question:**My scenario is tracking employees who complete online training. I capture their name, id, class taken, and date. Any employee is free to take a plethora of different courses and they could take them all on the same day if they want. What I am trying to do is see if any employee has taken any course more than once in any given day?

**Answer:**

Conditional formatting formula:

### Explaining Conditional formatting formula

#### Step 1 - Count record

The next COUNTIF function counts values based on a condition or criteria.

COUNTIF($A2, $A$2:$A$35)*COUNTIF($B2, $B$2:$B$35)*COUNTIF($C2, $C$2:$C$35)

becomes

{1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 1; 1; 0; 0; 0; 0; 0; 0; 0; 1; 0; 1; 0; 0; 0; 0; 0; 1; 0; 0}*COUNTIF($B2, $B$2:$B$35)*COUNTIF($C2, $C$2:$C$35)

becomes

{1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 1; 1; 0; 0; 0; 0; 0; 0; 0; 1; 0; 1; 0; 0; 0; 0; 0; 1; 0; 0}*{1; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}*COUNTIF($C2, $C$2:$C$35)

and returns

{1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 1; 1; 0; 0; 0; 0; 0; 0; 0; 1; 0; 1; 0; 0; 0; 0; 0; 1; 0; 0}*{1; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}*{1; 0; 1; 0; 1; 0; 0; 0; 0; 0; 0; 0; 1; 1; 0; 1; 0; 0; 1; 0; 1; 0; 1; 1; 1; 0; 0; 0; 0; 1; 0; 0; 0; 0}

#### Step 2 - Multiply arrays

All three arrays must return 1 in order to indicate a row as duplicate, to accomplish this we need to use AND logic. AND logic is performed using the asterisk charcater between arrays, in other words, arrays are multiplied.

COUNTIF($A2, $A$2:$A$35)*COUNTIF($B2, $B$2:$B$35)*COUNTIF($C2, $C$2:$C$35)

becomes

{1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 1; 1; 0; 0; 0; 0; 0; 0; 0; 1; 0; 1; 0; 0; 0; 0; 0; 1; 0; 0}*{1; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}*{1; 0; 1; 0; 1; 0; 0; 0; 0; 0; 0; 0; 1; 1; 0; 1; 0; 0; 1; 0; 1; 0; 1; 1; 1; 0; 0; 0; 0; 1; 0; 0; 0; 0}

and returns

{1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}.

#### Step 3 - Sum numbers in array

The SUM function adds numbers in a cell range or array and returns a total.

SUM(COUNTIF($A2, $A$2:$A$35)*COUNTIF($B2, $B$2:$B$35)*COUNTIF($C2, $C$2:$C$35))

becomes

SUM({1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0})

and returns 1.

#### Step 4 - Check if record is a duplicate

If total is larger than 1 then we know it is a duplicate, the larger than character allows us to compare the total to a given condition, the returned value is a boolean value, TRUE or FALSE.

SUM(COUNTIF($A2, $A$2:$A$35)*COUNTIF($B2, $B$2:$B$35)*COUNTIF($C2, $C$2:$C$35))>1

becomes

1>1 and returns FALSE. Cell A2 is not highlighted.

### Get Excel *.xls file

Highlight duplicate online classes using conditional formatting

## 4. Highlight duplicates in two columns

**Question:**

**Answer: **

The following animated image shows you two lists in column B and D.

**Conditional formatting formula:**

#### How to apply the conditional formatting formulas:

- Select cell range
- Press with left mouse button on "Home" tab on the ribbon
- Press with left mouse button on "Conditional formatting"
- Press with left mouse button on "New rule..."
- Press with left mouse button on "Use a formula to determine which cells to format"
- Press with left mouse button on "Format values where this formula is true" window.
- Type =(COUNTIF($B$3:$B3,B3)+COUNTIF($D$3:$D3,B3))>1
- Press with left mouse button on Format button
- Press with left mouse button on "Fill" tab
- Pick a color
- Press with left mouse button on OK!
- Press with left mouse button on OK!

### Explaining formula

#### Step 1 - Check if current value B3 exists in cell range $B$3:$B3

The first argument in the COUNTIF function is a cell reference that expands downwards.

COUNTIF($B$3:$B3,B3)

Cell |
COUNTIF |
Result |

B3 | COUNTIF($B$3:$B3, B3) | 0 |

B4 | COUNTIF($B$3:$B4, B4) | 0 |

B5 | COUNTIF($B$3:$B5, B5) | 0 |

#### Step 2 - Check if current value B3 exists in cell range $D$3:$D3

The first argument in the COUNTIF function is a cell reference that expands downwards.

COUNTIF($D$3:$D3,B3)

Cell |
COUNTIF |
Result |

B3 | COUNTIF($D$3:$D3, B3) | 0 |

B4 | COUNTIF($D$3:$D4, B4) | 0 |

B5 | COUNTIF($D$3:$D5, B5) | 0 |

#### Step 3 - Add results

COUNTIF($B$3:$B3,B3)+COUNTIF($D$3:$D3,B3)

Cell |
COUNTIF |
Result |

B3 | COUNTIF($B$3:$B3,B3)+COUNTIF($D$3:$D3,B3) | 0 |

B4 | COUNTIF($B$3:$B4,B4)+COUNTIF($D$3:$D4,B4) | 0 |

B5 | COUNTIF($B$3:$B5,B5)+COUNTIF($D$3:$D5,B5) | 0 |

#### Step 4 - Check if result is larger than 1

The parentheses makes sure that the order odf calculatiuon is correct.

(COUNTIF($B$3:$B3,B3)+COUNTIF($D$3:$D3,B3))>1

becomes

(0 (zero) + 0 (zero))>1

0>1

and returns FALSE. Cell B3 is not highlighted.

#### Get excel *.xlsx file

Highlight duplicates in two lists using conditional formatting.xlsx

## 5. Highlight duplicate columns

This article describes how to highlight duplicate records arranged into a column each, if you are looking for records entered in a row each then go to this article: Highlight duplicate records. The image above shows one record in each column, cell range B2:E5.

The array formula and conditional formatting formula in this article contain the COUNTIFS function, a function introduced in excel 2007. The COUNTIFS function evaluates conditions on cells across multiple ranges and counts the number of times all are met.

Conditional formatting formula:

### How to add conditional formatting formula

- Select cells A1:C30
- Press with left mouse button on "Home" tab
- Press with left mouse button on "Conditional Formatting" button
- Press with left mouse button on "New Rule.."
- Press with left mouse button on "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.

- Press with left mouse button on "Format.." button
- Press with left mouse button on "Fill" tab
- Select a color for highlighting cells.
- Press with left mouse button on "Ok"
- Press with left mouse button on "Ok"
- Press with left mouse button on "Ok"

### Explaining Conditional Formatting Formula in cell B2

#### Step 1 - Count columns that match the current column

In order to explain how this formula works you need to understand absolute and relative cell references. The dollar sign makes the cell reference locked, however a cell reference has both a column and row part and both may have a dollar sign depending on what you want to happen.

The COUNTIFS function allows you to counts duplicate records, make sure you create a condition for each row in your record. There are four rows in this example, the first argument is $B$2:$E$2 and doesn't change when the conditional formatting moves on to the next column.

The second argument is B$2 and is locked to row 2, however, the column is a relative cell reference (not locked) and changes when the CF moves to next column.

If there are columns that match all four conditions B$2, B$3, B$4 and B$5 then the COUNTIFS function returns the number of rows that match.

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)

returns 1.

#### Step 2 - Check if the number is larger than 1

If there is more than one column matching we know the record is a duplicate.

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

becomes

1>1

and returns FALSE. Cell B2 is not highlighted.

## 6. Highlight duplicates with same date, week or month

The image above demonstrates a conditional formatting formula that highlights duplicate items based on date. The first instance is not highlighted, only subsequent duplicates are highlighted.

Conditional formatting formula:

### 6.1 Explaining Conditional formatting formula

#### Step 1 - Concatenate cell values

The cell references $C3 and $D3 are locked to each column, however, they change when CF move to the next row. The ampersand sign concatenates the values in these cells.

$C3&"-"&$D3

becomes

40184&"-"&1150

and returns

"40184-1150"

#### Step 2 - Concatenate values using expanding cell references

These cell references expand as the CF moves to cells below, the formula keeps track of previous values so it can detect duplicate values.

$C3:$C$3&"-"&$D3:$D$3

becomes

40184&"-"&1150

and returns

"40184-1150".

#### Step 3 - Compare concatenated values

The equal sign lets you compare values, the result is always TRUE or FALSE.

$C3&"-"&$D3=$C3:$C$3&"-"&$D3:$D$3

becomes

"40184-1150" = "40184-1150"

and returns TRUE.

#### Step 4 - Convert boolean values

The SUMPRODUCT function can't work with boolean values, we must convert it (them) to their numerical equivalents. TRUE = 1 and FALSE equals 0 (zero).

--($C3&"-"&$D3=$C3:$C$3&"-"&$D3:$D$3)

becomes

--(TRUE)

and returns 1.

#### Step 5 - Sum values

The SUMPRODUCT function sums the values in the array in this step.

SUMPRODUCT(--($C3&"-"&$D3=$C3:$C$3&"-"&$D3:$D$3))

becomes

SUMPRODUCT(1)

and returns 1.

#### Step 6 - Check if duplicate

This steps checks if the sum is larger than 1, if it is then TRUE is returned and the cell is highlighted.

SUMPRODUCT(--($C3&"-"&$D3=$C3:$C$3&"-"&$D3:$D$3))>1

becomes

1>1

and returns FALSE. Cell C3 is not highlighted.

### 6.2 Get Excel *.xlsx file

Highlight-duplicates-within-same-date-week-month-year.xlsx

### 6.3 Highlight duplicates on same week

Conditional formatting formula:

### 6.4 Highlight duplicates on same month

Conditional formatting formula:

### Get excel sample file for this article.

Highlight-duplicates-within-same-date-week-month-year.xls

(Excel 97-2003 Workbook *.xls)

## 7. Highlight duplicate records/rows

This section shows you how to easily identify duplicate rows or records in a list.

### 7.1. Conditional Formatting formula - Excel 2007 and later versions

Conditional formatting formula:

### 7.2. Conditional Formatting formula - Excel 2003 and previous versions

The COUNTIFS function was introduced in Excel 2007, here is a formula for previous versions:

### 7.3. How to create a conditional formatting formula

- Select the cell range you want to highlight duplicate rows for.
- Go to the "Home" tab on the ribbon.
- Press with left mouse button on the "Conditional Formatting" button. A popup menu appears.
- Press with left mouse button on "New Rule.." on the popup menu.
- Press with left mouse button on "Use a formula to determine which cells to format", see the image below.
- Type =COUNTIFS($B$3:$B$15,$B3,$C$3:$C$15,$C3,$D$3:$D$15,$D3)>1 in "Format values where this formula is TRUE" window.

- Press with left mouse button on "Format.." button
- Press with left mouse button on "Fill" tab
- Select a color for highlighting cells.
- Press with left mouse button on "Ok"
- Press with left mouse button on "Ok"
- Press with left mouse button on "Ok"

### 7.4. How the conditional formatting formula works

#### Step 1 - Count rows that match the current row

The COUNTIFS function allows you to counts duplicate records, make sure you create a condition for each column in your record. There are three columns in this example, the first argument is $B$3:$B$15 and doesn't change when the conditional formatting moves on to the next row.

The second argument is $B3 and is locked to column B, however, the row is a relative cell reference (not locked) and changes when the CF moves to the next row.

If there are rows that match all three conditions $B3, $C3 and $D3 the COUNTIFS function returns the number of rows that match.

COUNTIFS($B$3:$B$15, $B3, $C$3:$C$15, $C3, $D$3:$D$15, $D3)

returns 1 because the first row is counted as well.

#### Step 2 - Check if the number is larger than 1

If there is more than one row matching we know the record is a duplicate.

COUNTIFS($B$3:$B$15, $B3, $C$3:$C$15, $C3, $D$3:$D$15, $D3)>1

becomes

1>1

and returns FALSE. Cell B3 is not highlighted.

### 7.5. Do not highlight the first duplicate

The following CF formula highlights duplicates except the first instance, see image above.

### Recommended blog post

Automatically filter unique distinct row records

## 8. Highlight duplicate values in a cell range

The following conditional formula highlights only the second instance or more of a value in a cell range.

Conditional formatting formula:

**How to apply conditional formatting**

- Select your range B2:E5.
- Go to "Home" tab
- Press with left mouse button on Conditional formatting
- Press with left mouse button on "New Rule.."
- Press with left mouse button on "Use a formula to determine what cells to format"
- Copy and paste the above conditional formatting formula to "Format values where this formula is true:"
- Press with left mouse button on Format button
- Select a formatting you like. For example, cells filled with yellow.
- Press with left mouse button on OK
- Press with left mouse button on OK

### 8.1 Explaining CF formula in cell B2

There are two parts in this formula, one part determines if a value is a duplicate in the first column. The second part of the formula determines if a value is a duplicate in the remaining columns.

The reason the formula looks like this is because of the order of how Excel calculates cells.

IF(*logical_expression*, *first_part*, *second_part*)

#### Step 1 - Check if first column is being evaluated

The COLUMNS function counts columns in a cell reference. $A$1:A1 is an expanding cell reference, it grows because A1 is a relative cell reference that changes between cells.

COLUMNS($A$1:A1)=1

becomes

1=1 and returns TRUE.

#### Step 2 - Count cells based on a condition

The IF function changes the calculation based on the logical expression in the first argument. The second argument is calculated if the logical expression returns TRUE, the third argument is calculated if the logical expression returns FALSE.

The COUNTIF function makes sure that duplicates are not highlighted, only the first instance of each value. However this works only in the first column, the remaining columns need a different formula in order to do correct calculations.

IF(COLUMNS($A$1:A1)=1,COUNTIF($B$2:B2,B2),COUNTIF($B$2:B2,B2)+COUNTIF(OFFSET($B$2:$E$5,,,4,COLUMNS($A$1:A1)-1),B2))>1

becomes

IF(TRUE,COUNTIF($B$2:B2,B2),...)>1

becomes

IF(TRUE,COUNTIF(0,0),...)>1

becomes

1>1

and returns FALSE. Cell B2 is not highlighted.

#### Step 3 - Calculations in remaining columns

If we move to cell C2 the IF function behaves differently.

IF(COLUMNS($A$1:B1)=1, COUNTIF($B$2:C2, C2), COUNTIF($B$2:C2, C2)+COUNTIF(OFFSET($B$2:$E$5, ,,4, COLUMNS($A$1:B1)-1), B2))>1

becomes

IF(2=1, COUNTIF($B$2:C2, C2),COUNTIF($B$2:C2, C2)+COUNTIF(OFFSET($B$2:$E$5, , , 4, COLUMNS($A$1:B1)-1), C2))>1

becomes

IF(FALSE, COUNTIF($B$2:C2, C2), COUNTIF($B$2:C2, C2)+COUNTIF(OFFSET($B$2:$E$5, , , 4, COLUMNS($A$1:B1)-1), C2))>1

becomes

IF(FALSE, ~~COUNTIF($B$2:C2, C2)~~, COUNTIF({0,6}, 6)+COUNTIF(OFFSET($B$2:$E$5, , , 4, COLUMNS($A$1:B1)-1), C2))>1

The OFFSET function returns an expanding cell reference that grows as the CF moves from column to column.

IF(FALSE, ~~COUNTIF($B$2:C2, C2)~~, 1+COUNTIF(OFFSET($B$2:$E$5, , , 4, 1), C2))>1

becomes

IF(FALSE, ~~COUNTIF($B$2:C2, C2)~~, 1+COUNTIF($B$2:$B$5, C2))>1

becomes

IF(FALSE, ~~COUNTIF($B$2:C2, C2)~~, 1+COUNTIF({0;11;14;16},6))>1

becomes

IF(FALSE, ~~COUNTIF($B$2:C2, C2)~~, 1+0)>1

becomes

1>1 and returns FALSE. Cell C2 is not highlighted.

**8.2 Highlight all duplicates**

To highlight all duplicates is much easier, the formula simply counts how many times the current value exists in the cell range.

**Get excel *.xlsx file**

highlight duplicate values in a range.xlsx

## 9. Highlight duplicates in a filtered Excel Table

The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has been filtered to show only records for January 2012.

There is a built-in function in Excel that lets you highlight duplicates, however, that won't work properly if you have filtered the data.

The image above shows the built-in tool highlighting duplicate items. But one item exists only once in the filtered data, however, it is highlighted as a duplicate.

The reason is that there is another item not showing in the filtered table. You need to rely on a custom conditional formatting formula if you want it to compare only filtered visible values.

### 9.1 Build an Excel Table

The reason I am using an Excel Table is that the conditional formatting adjusts automatically if you add or delete records. It also uses structured references so there is no need to change cell references in the formula.

The disadvantage is that you have to use the INDIRECT function each time you reference the Excel Table in the CF formula, it is not a big deal but it is worth noting.

- Select any cell in your data set.
- Press short cut keys CTRL + T to open the "Create Table" dialog box.

- Enable the checkbox if the columns in the data set have header names.
- Press with left mouse button on OK button to apply.

### 9.2 Create a new conditional formatting rule

- Select table column "Description".
- Go to the "Home" tab on the ribbon.
- Press with left mouse button on the "Conditional formatting" button.
- Press with left mouse button on "New Rule..".
- Press with left mouse button on "Use a formula to determine which cells to format".

- Copy this conditional formatting formula:
=SUM(COUNTIF(INDIRECT("Table2[@Description]"), IF(SUBTOTAL(3, OFFSET(INDIRECT("Table2[Description]"), MATCH(ROW(INDIRECT("Table2[Description]")), ROW(INDIRECT("Table2[Description]")))-1, 0, 1)), INDIRECT("Table2[Description]"), "")))>1
- Paste to "Format values where this formula is true:". I will explain the formula later in this article.
- Press with left mouse button on the "Format..." button.
- Go to tab "Fill".

- Pick a color.
- Press with left mouse button on OK button.
- Press with left mouse button on OK button again.

### 9.3 Explaining CF formula in cell C211

This Conditional Formatting formula highlights cells that have a duplicate in a filtered Excel Table, note that it will not be highlighted if a duplicate exists outside the filtered values (not visible).

#### Step 1 - Create an array from 1 to n

The INDIRECT function makes it possible to use a reference to an Excel Table inside a Conditional Formatting formula. This article explains it in greater detail: How to use an Excel Table name in Data Validation Lists and Conditional Formatting formulas

The ROW function converts a cell reference to the corresponding row numbers which is useful when you want to create an array.

The MATCH function is utilized in this example to create an array that contains a sequence that begins with 1 and increments with one up to the number of values in the array.

MATCH(ROW(INDIRECT("Table2[Description]")), ROW(INDIRECT("Table2[Description]"))

becomes

MATCH(ROW(C206:C227), ROW(C206:C227))

becomes

MATCH({3; 4; ... 383; 384}, {3; 4; ... 383; 384})

and returns

{1; 2; 3; ... 380; 381}.

The Excel Table has 381 records and the size of the array matches that number.

#### Step 2 - Modify array

This workaround makes it possible to use an array of values in a SUBTOTAL function, the OFFSET function splits the array into smaller arrays with only one value in each array.

OFFSET(*reference*,*rows*,*columns*,[*height*],[*width*])

It returns error values, however, the SUBTOTAL function can calculate these values anyway.

OFFSET(INDIRECT("Table2[Description]"), MATCH(ROW(INDIRECT("Table2[Description]")), ROW(INDIRECT("Table2[Description]")))-1, 0, 1)

becomes

OFFSET(INDIRECT("Table2[Description]"), {1; 2; 3; ... 380; 381}-1, 0, 1)

becomes

{0; 1; 2; ... ; 379; 380}

and returns

{#VALUE!; #VALUE!; #VALUE!; ... ; #VALUE!; #VALUE!}

#### Step 3 - Identify visible values

The first argument in the SUBTOTAL function is 3 and represents the COUNTA function meaning it will count cells that are not empty.

The SUBTOTAL function will actually return an array with this setup which is very handy in this situation.

SUBTOTAL(3, OFFSET(INDIRECT("Table2[Description]"), MATCH(ROW(INDIRECT("Table2[Description]")), ROW(INDIRECT("Table2[Description]")))-1, 0, 1))

becomes

SUBTOTAL(3, {#VALUE!; #VALUE!; #VALUE!; ... ; #VALUE!; #VALUE!})

and returns {0; 0; 0; ... ; 0; 0}. 0 (zero) represents a hidden value and 1 is visible.

#### Step 4 - Create an array containing visible values

The IF function replaces 0 (zero) with nothing "" and 1 with the actual corresponding value.

IF(SUBTOTAL(3, OFFSET(INDIRECT("Table2[Description]"), MATCH(ROW(INDIRECT("Table2[Description]")), ROW(INDIRECT("Table2[Description]")))-1, 0, 1)), INDIRECT("Table2[Description]"), "")

becomes

IF({0; 0; 0; ... ; 0; 0}, INDIRECT("Table2[Description]"), "")

and returns {""; ""; ""; ... ; ""; ""}.

#### Step 5 - Check the number of times the current value exists across visible values

The COUNTIF function will return an array with this setup which we then can use to calculate a total. The reason we don't change the arguments with each other is that the *range* argument will not accept an array based on calculations.

COUNTIF(*range*, *criteria*)

COUNTIF(INDIRECT("Table2[@Description]"), IF(SUBTOTAL(3, OFFSET(INDIRECT("Table2[Description]"), MATCH(ROW(INDIRECT("Table2[Description]")), ROW(INDIRECT("Table2[Description]")))-1, 0, 1)), INDIRECT("Table2[Description]"), ""))

becomes

COUNTIF(INDIRECT("Table2[@Description]"), {""; ""; ""; ... ; ""; ""})

becomes

COUNTIF("Chez Quiz", {""; ""; ""; ... ; ""; ""})

and returns {0; 0; 0; ... ; 0; 0}. The entire array is not shown, however, it contains a few 1's as well.

#### Step 6 - Calculate a total

The SUM function adds all numbers in the array and returns a total.

SUM(COUNTIF(INDIRECT("Table2[@Description]"), IF(SUBTOTAL(3, OFFSET(INDIRECT("Table2[Description]"), MATCH(ROW(INDIRECT("Table2[Description]")), ROW(INDIRECT("Table2[Description]")))-1, 0, 1)), INDIRECT("Table2[Description]"), "")))

becomes

SUM({0; 0; 0; ... ; 0; 0})

and returns 2.

#### Step 7 - Check if value is larger than 1

The > larger than character is a logical operator that lets you compare values, it will return a boolean value TRUE or FALSE based on the outcome. The Conditional Formatting formula uses the boolean function to determine if the cell values should be highlighted or not.

SUM(COUNTIF(INDIRECT("Table2[@Description]"), IF(SUBTOTAL(3, OFFSET(INDIRECT("Table2[Description]"), MATCH(ROW(INDIRECT("Table2[Description]")), ROW(INDIRECT("Table2[Description]")))-1, 0, 1)), INDIRECT("Table2[Description]"), "")))>1

becomes

2>1

and returns True. Cell C211 is highlighted.

### Recommended reading

### Built-in conditional formatting

Data Bars Color scales Icons### Highlight cells rule

Highlight cells containing stringHighlight a date occuring

Conditional Formatting Basics

Highlight unique/duplicates

### Top bottom rules

Highlight top 10 valuesHighlight top 10 % values

Highlight above average values

### Basic CF formulas

Working with Conditional Formatting formulasFind numbers in close proximity to a given number

Highlight empty cells

Highlight text values

### Search using CF

Highlight records – multiple criteria [OR logic]Highlight records [AND logic]

Highlight records containing text strings (AND Logic)

Highlight lookup values

### Unique distinct

How to highlight unique distinct valuesHighlight unique values and unique distinct values in a cell range

Highlight unique values in a filtered Excel table

Highlight unique distinct records

### Duplicates

How to highlight duplicate valuesHighlight duplicates in two columns

Highlight duplicate values in a cell range

Highlight smallest duplicate number

Highlight more than once taken course in any given day

Highlight duplicates with same date, week or month

Highlight duplicate records

Highlight duplicate columns

Highlight duplicates in a filtered Excel Table

### Compare

Highlight missing values between to columnsCompare two columns and highlight values in common

Compare two lists of data: Highlight common records

Compare tables: Highlight records not in both tables

How to highlight differences and common values in lists

Compare two columns and highlight differences

### Min max

Highlight smallest duplicate numberHow to highlight MAX and MIN value based on month

Highlight closest number

### Dates

Advanced Date Highlighting Techniques in ExcelHow to highlight MAX and MIN value based on month

Highlight odd/even months

Highlight overlapping date ranges using conditional formatting

Highlight records based on overlapping date ranges and a condition

Highlight date ranges overlapping selected record [VBA]

How to highlight weekends [Conditional Formatting]

How to highlight dates based on day of week

Highlight current date

### Misc

Highlight every other rowDynamic formatting

Advanced Techniques for Conditional Formatting

Highlight cells based on ranges

Highlight opposite numbers

Highlight cells based on coordinates

### Excel categories

### 15 Responses to “How to highlight duplicate values”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

For those wanting to know how to do this in versions of Excel prior to XL2007, here is the Conditional Formatting formula to use. Select the cells in Columns A, B and C from Row 1 down to the last row you want to conditionally format and use this Conditional Formatting formula...

.

.

=SUMPRODUCT(--($A$1:$A$30&"X"&$B$1:$B$30&"X"&$C$1:$C$30=$A1&"X"&$B1&"X"&$C1))>1

.

.

Those embedded X's just need to be a character that is guaranteed not to be in any of the cells being conditionally formatted. These characters ensure no accidental matches occur during the concatenations; for example, without them, an accidental match could occur like this...

"12"&"3"&"4" = "1"&"23"&"4"

both equating to "1234" meaning the equality check would be true; with the X's in place, you get this...

"12"&"X"&"3"&"X"&"4" = "1"&"X"&"23"&"X"&"4"

with the first equating to "12X3X4" and the second equating to "1X23X4" and the equality check would be false.

Here is another conditional formatting formula, excel 2003:

=SUMPRODUCT(COUNTIF($A1, $A$1:$A$30)*COUNTIF($B1, $B$1:$B$30)*COUNTIF($C1, $C$1:$C$30))>1

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!

this formula is not working for me.....

Deepak,

what does your formula look like?

Remember, you must understand how relative and absolute cell references work.

[...] Here is a post where I use this technique: Highlight duplicate rows [...]

Love you for this formula. Thank you for posting....

Mohasin,

Thank you for commenting!

[…] has some incredible tools for highlighting cells, rows, dates, comparing data and even series in line charts. A technique using the secondary […]

[…] Here is a post where I use this technique: Highlight duplicate rows […]

[…] Highlight duplicates values […]

Hello, this formula works great, I am wondering if I can change it slightly for what I need.

I have rows of lists, 6 in each row. They are all names. The names will be in different order, but I want to not have duplicates of the same lists. I cannot fully sort them as the first name is unique, and needs to be in that spot. I will give you and example:

John Bill James Ron Joe Mike

Bill John James Joe Ron Mike

James Bill John Joe Mike Ron

All three of these list are the same, but when they are in a different order, this conditional formatting does not show them as duplicates. Any suggestions?

Thank you in advance,

Marsh

Hi,

I'm trying to find a way to highlight cells with text/number (column B) if reoccurred 3 times or more within the last 3 days (dates in column A)

I hope someone can help

Hello,

I'm doing this for a spreadsheet where I don't want a blank cell (in the second column) to match with another blank cell. How can I modify the formula accordingly? For instance, in my work I drag down the dates in column A before entering the rest of the data. I'm looking for a match between column A&E between rows. But at the moment it does this, but also highlights all the prepped rows at the end where I've dragged down the date. I want it not to consider the same date in A and a blank in E to be a match with another same date A and blank E.

Thanks!