Cleaning Your Spreadsheet Data Before Migration: A Practical Guide

You open the client sheet to start the migration and scroll to the “Country” column. It contains “UK”, “U.K.”, “United Kingdom”, “england”, “GB”, and one cell that just says “see email”. There are 1,400 rows. Somewhere in there, the same client appears three times under slightly different names.

Import that as it is, and your shiny new tool will faithfully preserve every one of those problems. Clean first. It’s tedious, but it’s the cheapest moment you’ll ever have to do it.

Work on a copy, and keep the original

Before you touch anything, save a frozen copy of the original sheet and label it clearly, with the date. Do all cleaning in a separate working copy.

This sounds obvious. It’s also the step people skip, right before they realise they deleted a column that turned out to matter. You want to be able to answer “what did it say before?” for months after migration.

Add a column to your working copy called something like “Cleaning notes”, where you record anything you changed by hand that isn’t obvious. Future you will be grateful.

Remove duplicates, carefully

Duplicates are rarely exact. “Acme Ltd”, “ACME Limited” and “Acme Ltd.” are probably the same client. “Acme Ltd” in London and “Acme Ltd” in Leeds might not be.

A practical approach:

1. Find a matching key. Look for something that should be unique: an email address, a company registration number, an account ID. Match on that first.
2. Normalise before matching. Make a temporary column with names in lowercase, with punctuation and words like “ltd” or “inc” stripped out. Sort by it. Near-duplicates end up next to each other.
3. Decide the survivor. When two rows are the same client, pick one to keep. Usually it’s the most recently updated or the most complete.
4. Merge, don’t just delete. If the duplicate has a phone number the survivor lacks, copy it across before removing the duplicate.

Don’t automate the final decision unless the matching key is solid. Say you have 60 suspected duplicates. Reviewing them by hand takes an hour or two. Untangling a wrongly merged client later takes much longer.

Turn free text into picklists

A picklist (sometimes called a dropdown or single-select field) limits a field to a defined set of values. It’s one of the biggest improvements a real tool offers, and it only works if your existing data fits.

For each column that should be a picklist, like country, status, region or product type:

• Pull out every distinct value and count how often each appears.
• Decide the official list. Keep it short.
• Build a simple two-column mapping: old value on the left, official value on the right.
• Apply the mapping, then check for anything left unmatched.

For the country example, the mapping might look like this:

Resist the urge to create a value for every variation. If “Status” has 23 distinct values, the real process probably has five or six states. “Replacing the ‘Status’ Column: Designing Real Workflows with States and Transitions” explains how to find them.

Fix dates

Dates in spreadsheets are a quiet disaster. Some cells hold real dates. Others hold text that looks like a date. Some say “Q3”, “next week” or “TBC”.

Watch for:

• Mixed formats. Is 04/05/2025 the 4th of May or the 5th of April? If your team spans countries, you probably have both. Look for values where the first number is over 12 to work out which convention each person used.
• Text pretending to be a date. Sort the column. Real dates sort in order; text dates often clump at the top or bottom.
• Vague values. Decide a rule. “Q3 2025” might become the first day of the quarter plus a note, or stay blank with a flag.
• Impossible dates. A contract signed in 1925 or a renewal in 2205 is a typo. Filter for anything outside a sensible range.

Convert everything to one unambiguous format, such as year-month-day, before import.

Split merged and overloaded cells

Merged cells look tidy and break everything. Unmerge them all, then fill the gaps so every row carries its own values. A row that’s blank because “it’s the same as the one above” will import as blank.

Overloaded cells are the other problem: one cell holding several pieces of information. “Jane Smith, jane@example.com, 07700 900123” in a “Contact” column needs to become three fields. Use your spreadsheet tool’s text-splitting feature where the separator is consistent, and fix the rest by hand.

Watch for cells holding lists, such as “Projects: Alpha, Beta, Gamma”. In your new tool, that’s probably a relationship to separate project records, not a text field. “From Rows to Records: How to Think About Your Data Like an Application” covers that shift.

Decide what not to migrate

Not every row deserves a new home. Clients who left four years ago, test rows, and that tab labelled “OLD do not use” can stay in the archived original.

Agree a cut-off with the people who use the data. Archived doesn’t mean deleted; it means not cluttering the system people use every day.

Check your work

Before importing, compare your cleaned copy with the original. Record counts should match, minus the duplicates and archived rows you deliberately removed. Spot-check 20 random records against the original. Hand a sample to someone who knows the data well and ask what looks wrong.

Write the final counts down. You’ll need them on migration day, as “Migration Day Checklist: Retiring a Business-Critical Spreadsheet” describes.

Where to start this week

• Save a dated, frozen copy of your sheet and create a working copy with a “Cleaning notes” column.
• Pick the one column that most needs to become a picklist and build its mapping table.
• Run a duplicate check on your most reliable unique field and count the suspected matches.
• Agree an archive cut-off date with the data’s main users.