How to highlight duplicate values

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:

Back to top

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.

Back to top

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

1. Select the range (B3:B10)
2. Press with left mouse button on the "Home" tab on the ribbon
3. Press with left mouse button on "Conditional formatting"
4. Press with left mouse button on "New rule..."
5. Press with left mouse button on "Use a formula to determine which cells to format"
6. Press with left mouse button on "Format values where this formula is true" window.
7. Type COUNTIF($B3:$B$3, $B3)>1
8. Press with left mouse button on "Format" button
9. Go to tab "Fill"
10. Pick a color
11. Press with left mouse button on OK button
12. 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)

Back to top

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

1. Select cell range
2. Go to "Home" tab
3. Press with left mouse button on Conditional formatting
4. Press with left mouse button on "New Rule.."
5. Press with left mouse button on "Use a formula to determine what cells to format"
6. Copy the above conditional formatting formula to "Format values where this formula is true:"
7. Press with left mouse button on Format button
8. Select a formatting you like.
9. Press with left mouse button on OK
10. Press with left mouse button on OK

Back to top

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.

Back to top

Get Excel *.xls file

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

3. Highlight more than once taken course 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:

1. Select cell range
2. Press with left mouse button on "Home" tab on the ribbon
3. Press with left mouse button on "Conditional formatting"
4. Press with left mouse button on "New rule..."
5. Press with left mouse button on "Use a formula to determine which cells to format"
6. Press with left mouse button on "Format values where this formula is true" window.
7. Type =(COUNTIF($B$3:$B3,B3)+COUNTIF($D$3:$D3,B3))>1
8. Press with left mouse button on Format button
9. Press with left mouse button on "Fill" tab
10. Pick a color
11. Press with left mouse button on OK!
12. 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

1. Select cells A1:C30
2. Press with left mouse button on "Home" tab
3. Press with left mouse button on "Conditional Formatting" button
4. Press with left mouse button on "New Rule.."
5. Press with left mouse button on "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. Press with left mouse button on "Format.." button
8. Press with left mouse button on "Fill" tab
9. Select a color for highlighting cells.
10. Press with left mouse button on "Ok"
11. Press with left mouse button on "Ok"
12. 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:

Back to top

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:

Back to top

7.3. How to create a conditional formatting formula

1. Select the cell range you want to highlight duplicate rows for.
2. Go to the "Home" tab on the ribbon.
3. Press with left mouse button on the "Conditional Formatting" button. A popup menu appears.
4. Press with left mouse button on "New Rule.." on the popup menu.
5. Press with left mouse button on "Use a formula to determine which cells to format", see the image below.
6. 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.
   
7. Press with left mouse button on "Format.." button
8. Press with left mouse button on "Fill" tab
9. Select a color for highlighting cells.
10. Press with left mouse button on "Ok"
11. Press with left mouse button on "Ok"
12. Press with left mouse button on "Ok"

Back to top

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.

Back to top

7.5. Do not highlight the first duplicate

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

Back to top

Recommended blog post

Automatically filter unique distinct row records

Back to top

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

1. Select your range B2:E5.
2. Go to "Home" tab
3. Press with left mouse button on Conditional formatting
4. Press with left mouse button on "New Rule.."
5. Press with left mouse button on "Use a formula to determine what cells to format"
6. Copy and paste the above conditional formatting formula to "Format values where this formula is true:"
7. Press with left mouse button on Format button
8. Select a formatting you like. For example, cells filled with yellow.
9. Press with left mouse button on OK
10. 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.

1. Select any cell in your data set.
2. Press short cut keys CTRL + T to open the "Create Table" dialog box.
   
3. Enable the checkbox if the columns in the data set have header names.
4. Press with left mouse button on OK button to apply.

9.2 Create a new conditional formatting rule

1. Select table column "Description".
2. Go to the "Home" tab on the ribbon.
3. Press with left mouse button on the "Conditional formatting" button.
4. Press with left mouse button on "New Rule..".
5. Press with left mouse button on "Use a formula to determine which cells to format".
   
6. 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
7. Paste to "Format values where this formula is true:". I will explain the formula later in this article.
8. Press with left mouse button on the "Format..." button.
9. Go to tab "Fill".
   
10. Pick a color.
11. Press with left mouse button on OK button.
12. 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 string
Highlight a date occuring
Conditional Formatting Basics
Highlight unique/duplicates

Top bottom rules

Highlight top 10 values
Highlight top 10 % values
Highlight above average values

Basic CF formulas

Working with Conditional Formatting formulas
Find 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 values
Highlight 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 values
Highlight 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 columns
Compare 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 number
How to highlight MAX and MIN value based on month
Highlight closest number

Dates

Advanced Date Highlighting Techniques in Excel
How 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 row
Dynamic formatting
Advanced Techniques for Conditional Formatting
Highlight cells based on ranges
Highlight opposite numbers
Highlight cells based on coordinates

Excel categories

Comments (19)