Finding and removing duplicates

Duplicates creep in from everywhere: the same lead submits a form twice, two exports overlap by a week, someone pastes a block in again. Left alone they inflate every count and sum.

Clean before you compare. [email protected] and [email protected] are the same person but not the same text. Run TRIM and LOWER first, in a helper column or inside the formula. Data › Data cleanup › Trim whitespace fixes spaces in place.

Flag first, delete later. In a helper column, =COUNTIF(B$2:B2, B2)>1 filled down shows TRUE on the second and later copies. The range grows one row at a time as you fill down, so the first copy stays FALSE. Filter for TRUE and look before you delete.

Remove in place. Data › Data cleanup › Remove duplicates keeps the first copy of each row and deletes the rest. Tick Data has header row, then choose which columns define a duplicate: Email alone, or Email and Date together. It tells you how many rows it removed. There's no going back except undo, so duplicate the tab first.

Or let a formula do it. =UNIQUE(B2:B200) returns each value once in a new spot and leaves the source alone. When new rows arrive, the list updates. =COUNTUNIQUE(B2:B200) tells you how many distinct values there are, which is a quick check on whether duplicates exist at all.