Topics/Tables, Pivots, Formatting and Data Tools

Remove Duplicates & Find/Replace

Premiumadvanced 15 min4 tasks0/78 pts

Messy data has duplicates and inconsistent text. You can spot duplicates with conditional formatting (Duplicate values), list each value once with UNIQUE, and clean text with SUBSTITUTE (the formula version of Find & Replace) which swaps one piece of text for another.

Syntax

UNIQUE(array) · SUBSTITUTE(text, old_text, new_text)

Common mistakes

Deleting rows by hand and missing one of the duplicates.
Find & Replace on the wrong columns changes data you meant to keep.

Sorting and Custom Sort Data Validation & Dropdown Lists