Thursday, August 04, 2005

Display duplicate values in a range


Often when working with a list of data one needs to know whether or not there are any duplicate values in the list.
Heres a conditional formatting way of doing it
I am assuming that there is a list of data in column A and that we want to check for duplicate values in it.

Steps
  • Select any cell in the range and then click Format | Conditional Fomatting.

  • In Condition 1 select Formula Is and type =COUNTIF($A:$A,A5)>1 where A5 is the selected cell. Note the use of relative and absolute references. Column A is selected absolutely ($A:$A) while the selected cell is selected relative to itself with no $ signs.

  • Now choose a format option for the duplicate cells by setting the Font, Border or Patterns as required.

  • Click OK.

  • Now copy the cell and then select the whole column and Paste Special As Formats.


    • All the duplicate cells should now display the chosen formatting.


    Posted by: Jethro | 9:30 PM |