# Messy Excel Data for Practice — File, Tasks & Answers

Version: 2026-10-08.1

https://formulasmith.com/en/sample-data/messy-data/

Start with the unchanged six-column source file, work through nine cleanup tasks, then compare every retained row with the cleaned reference. The answer file includes a duplicate audit rather than silently discarding records.

## Before you start
- Answers match the default 100-row file only. Other row counts need new ranges and totals.
- Slash dates mean day/month/year; ISO dates mean year-month-day. Amounts use a decimal point. The reference CSV stores dates as ISO text; the reference XLSX stores numeric dates.
- Remove only identical whole source rows across all six columns, keeping the first occurrence. A repeated name is not a duplicate key. Conflicting normalized IDs require review.
- Normalize ordinary spaces and case for this synthetic dataset. A missing surname stays blank; do not invent one. This is not a universal rule for real customer names.

## 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.

## Teaching and reuse
Free for practice, teaching, demos and commercial use. No registration. Attribution is welcome, not required. These records are synthetic. Share the source page so learners can obtain the matching file version.