Solo Ops Workshop
Workshop›Episode 01 · Excel

Clean Messy Data in Excel: A Five-Step Workflow

Make a rough list usable without losing track of what changed. This lesson uses fictional orders with extra spaces, text dates and amounts, inconsistent categories, and one verified duplicate.

Written guide + practice filesSample workbook includedFictional data
Start here

Work through the cleanup from start to check.

Video coming soonThe written guide and practice files are ready below.
Written guide available now

Follow the written steps and use the sample workbook to check your result.

Worked example

What changes—and what should not.

The sample workbook has 9 order rows. One exact copy of order O-103 is removed after review, leaving 8 unique rows. The order value falls by exactly $240, from a normalized $1,255 to $1,015. The table below shows a few representative rows, not the whole workbook.

Fictional order list · excerpt

Middle dots below show spaces in raw text; they are not characters in the workbook.

Order IDContactCategoryOrder dateAmount (USD)Notes
O-101··Maya Stone··workshop2026/10/07 (text)$120.00 (text)·Intro session·
O-103ALMA CHENconsultingOct 09, 2026 (text)·240· (text)Planning call
O-103ALMA CHENconsultingOct 09, 2026 (text)·240· (text)Planning call
O-108Sam LeeConsulting2026-10-14 (text)160.00 (text)Implementation
9 order rows in full sheet$1,255 after amounts are converted1 verified exact duplicate
Why reconcile?

“Remove Duplicates” deletes rows based on the columns you select. A matching order ID alone is not proof that two entire orders are the same. Review the complete records first, then explain every row and total change.

The repeatable process

Five steps to a trustworthy list.

  1. Save a working copy and make a Table

    Keep the untouched export and record its 9 data rows. Work on a copy. Click inside the working range, press Ctrl+T (or choose Home → Format as Table), confirm all six columns and “My table has headers,” and inspect the first and last rows. A Table makes filtering easier; it does not clean values by itself.

  2. Trim ordinary spaces and standardize known categories

    Use a helper column with =TRIM(cell) for ordinary extra spaces, fill it down, inspect the results, then paste values only when satisfied. Map reviewed category variants to Workshop, Consulting, and Training. Preserve the original column until the mapping is checked. The fictional ALMA CHEN display name is changed only after review; real names may have deliberate capitalization.

    Excel’s TRIM does not remove every kind of space, including a nonbreaking space. Investigate a stubborn value instead of applying the same formula repeatedly.

  3. Convert text dates and amounts into real values

    A cell that looks like a date or dollar amount may still be text. For dates Excel recognizes, use its conversion option or DATEVALUE, then apply a date format. For numeric text, use “Convert to Number” or VALUE, then apply currency formatting. Check samples across the list; date parsing can depend on locale, and ambiguous dates need human interpretation.

  4. Review the duplicate before removing anything

    Filter or highlight candidate rows. Both O-103 rows in this sample match across all six fields: ID, contact, category, date text, amount text, and note. A repeated ID alone is not proof that two orders are identical. If any field differs, investigate rather than delete.

  5. Remove the confirmed copy and reconcile

    In Data → Remove Duplicates, choose all six relevant columns for this sample and inspect the result. One approved O-103 row goes away. Confirm 9 → 8 rows and $1,255 − $240 = $1,015 after amounts are parsed. Explain every changed row and total; keep the Before tab as the audit copy.

Before you trust the result

Quality checks and common mistakes.

Removing duplicates by ID without looking at the rest of the row

Two rows can share an ID and still contain different information. Compare all relevant fields and decide what “duplicate” means for this dataset.

Formatting text instead of converting it

Changing the display format alone may leave an amount or date stored as text. Verify with a calculation or sort, not appearance alone.

Assuming a clean sheet is an accurate sheet

This workflow catches known structure and consistency problems. It cannot validate every business fact; sample-check against the source.

Practice files

Download and practice.

Sample Excel workbookBefore, Cleaned, and Read Me sheets · .xlsxCaptionsSubtitle text · .srt
Product references

Check the current Excel steps.

Next lesson · Episode 02Organize Gmail With Labels and Filters