Find duplicates visually without deleting them — three methods depending on what you need to do next.
Every value that appears more than once turns the chosen colour automatically.
This highlights duplicates visually without changing or deleting anything. The highlighting updates dynamically — add a new duplicate and it highlights immediately.
=COUNTIF($A$2:$A$100, A2) > 1
-- Returns TRUE if the value appears more than once
-- Use as a conditional formatting formula rule
-- Or in a helper column: =IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate","Unique")Gives you more control — you can filter the helper column to show only duplicates, or apply different logic (e.g. only flag values that appear 3+ times).
=COUNTIF($A$2:A2, A2) > 1
-- Note: first $ locks the start, no $ on the end
-- This counts occurrences up to and including the current row
-- Only flags the 2nd, 3rd occurrence — not the firstUseful when you want to keep one copy and highlight only the extras.
To find rows where the combination of two columns is duplicated (e.g. same name AND same date): =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1
Practice this formula yourself — type it in a real spreadsheet and get instant feedback. Free, no download needed.
Start the Excel Basics track free →