How to highlight cell if value duplicate in same column for google spreadsheet?

user3331309 picture user3331309 · Feb 20, 2014 · Viewed 863.8k times · Source

I am looking for formula for google spreadsheet highlight cell if value duplicate in same column

can anyone please assist me for this query?

Answer

zolley picture zolley · Apr 28, 2014

Try this:

  1. Select the whole column
  2. Click Format
  3. Click Conditional formatting
  4. Click Add another rule (or edit the existing/default one)
  5. Set Format cells if to: Custom formula is
  6. Set value to: =countif(A:A,A1)>1 (or change A to your chosen column)
  7. Set the formatting style.
  8. Ensure the range applies to your column (e.g., A1:A100).
  9. Click Done

Anything written in the A1:A100 cells will be checked, and if there is a duplicate (occurs more than once) then it'll be coloured.

For locales using comma (,) as a decimal separator, the argument separator is most likely a semi-colon (;). That is, try: =countif(A:A;A1)>1, instead.

For multiple columns, use countifs.