FormulaSmith · Tasks only

Messy Excel Data for Practice — File, Tasks & Answers

Version 2026-10-08.1 · 100 source rows
https://formulasmith.com/en/sample-data/messy-data/

Open this HTML file in your browser, then choose Print or Save as PDF. No sign-in, script or upload is needed. This file contains questions and writing space, without the answer key.

Before you start

Use the unchanged default 100-row source file from the page above. Other file sizes require different ranges and totals.

  1. Inspect the raw file

    Count numeric cells in E2:E101 before changing anything.

  2. Remove exact duplicate rows

    Work on a copy. Remove duplicates using every original column, not just a name or ID.

  3. Normalize record IDs

    Remove surrounding ordinary spaces and use uppercase IDs in a helper column.

  4. Clean names and cities

    Trim ordinary spaces and normalize the simple synthetic names and cities. Use helper columns before replacing values.

  5. Convert amounts explicitly

    Turn numeric text into numbers using the source decimal convention.

  6. Resolve mixed dates

    Convert ISO text and DD/MM/YYYY text without guessing the computer locale.

  7. Normalize status values

    Remove spaces and normalize the four allowed status labels.

  8. Split optional surnames

    Add First Name and Last Name without inventing missing information.

  9. Reconcile the finished table

    Check row counts, identifiers, date types and the amount total; then compare every row with the reference.