Then we use: ADDRESS(row, column, ) to translate the numeric row and column to a cell reference (like B3. If no parameter is provided, the current cell will be returned (this is 1-based, for example, B3 will return 3 for ROW(), and 2 for COLUMN()). ROW() and COLUMN() will return the row number and column number of the given cell respectively. If this was placed inside the cell, docs will have cried about circular dependency, but in this case, the formula is evaluated as if it was in the cell, without changing it. The second block, INDIRECT(ADDRESS(ROW(), COLUMN(), 4)), will return current cell's content. We are using a fixed range (with $ signs) so that we always view the full range. If no special operators are provided, it will compare every cell in the range with the given cell, and return the number of cells found to be matching the rule (in this case, the comparison).

In the textbox insert the given formula, adjusting the range to match step (3).ĬOUNTIF(range, criterion), will compare every cell in range to the criterion, which is processed similarly to formulas.In Format cells if, select Custom formula is on the dropdown.Under Apply to range, select the range to which the rule should be applied.

On the menu: Format > Conditional formatting.Select the whole range on which the duplicate marking is wanted.The first parameter ( $A$1:$C$50) should be replaced with the range on which you would like to highlight duplicates! Please note that in this example I will be using the range A1:C50. While zolley's answer is perfectly right for the question, here's a more general solution for any range, plus explanation: =COUNTIF($A$1:$C$50, INDIRECT(ADDRESS(ROW(), COLUMN(), 4))) > 1

Duplicacy formula in google sheet