Google-sheets – How to highlight cell if value duplicate in same column for google spreadsheet


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

can anyone please assist me for this query?

Best Solution

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.