Cleaning Roster Datafor Union Campaign Success
Module 5 · Stripping Formatting and Deduplicating › 5.2 Paste Values and helper columns
Subsection 5.2

Paste Values and helper columns

~5 min

Reading

The Paste Values move is two clicks and saves you hours. Then we build a helper column that powers everything in the next three lessons.

Step 1 · Make a backup of the source tab

  1. Right-click the tab at the bottom of the workbook.
  2. Duplicate. A copy appears.
  3. Rename the original to source_raw. Rename the copy to working.

Step 2 · Strip formatting in working

  1. Click the corner cell (the gray square above row 1 and to the left of column A) to select the entire sheet.
  2. Edit › Copy (or Cmd/Ctrl + C).
  3. Edit › Paste special › Values only (or Cmd/Ctrl + Shift + V).

Every fill color, border, conditional format, and merged cell is gone. The data is still there.

Step 3 · Build the normalizer helper column

In the first empty column to the right of your data (say column M), write a normalizer formula in cell M2:

=LOWER(TRIM(SUBSTITUTE(A2, ".", "")))

Read it: lowercase the value, trim spaces, and remove any periods. Maria Vasquez and M. Vasquez and maria vasquez all collapse to maria vasquez.

Drag down the entire roster. This is the column the next three lessons depend on.

Action: Mark this page complete when you have finished the activity above.