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

```excel
=COUNT(E2:E101)
```

86 numeric amounts in the XLSX file. CSV import may already convert text to numbers, so use XLSX for this check.

## 2. Remove exact duplicate rows

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

Select A1:F101, choose Data > Remove Duplicates, select all six columns and confirm the header. Keep 94 rows; remove 6. The duplicate audit identifies each retained source row.

## 3. Normalize record IDs

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

```excel
=UPPER(TRIM(A2))
```

Keep the REC- identifier and its digits unchanged. Verify uniqueness after cleanup; do not delete conflicting IDs without inspection.

## 4. Clean names and cities

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

```excel
=PROPER(TRIM(B2))
```

Use the same expression with C2 for cities. Preserve the comma when a surname is present. Compare all rows with the reference; case normalization is not reliable for every real name.

## 5. Convert amounts explicitly

Turn numeric text into numbers using the source decimal convention.

```excel
=NUMBERVALUE(TRIM(E2),".",",")
```

Fill down over the retained rows. All 94 amounts should be numeric. The total after deduplication is 189996.09.

## 6. Resolve mixed dates

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

```excel
=IF(MID(D2,5,1)="-",DATE(LEFT(D2,4),MID(D2,6,2),RIGHT(D2,2)),DATE(RIGHT(D2,4),MID(D2,4,2),LEFT(D2,2)))
```

The slash branch explicitly treats the first pair as day and the second as month. Format the resulting numeric cells as yyyy-mm-dd. This expression is for the two known valid source shapes, not arbitrary dates.

## 7. Normalize status values

Remove spaces and normalize the four allowed status labels.

```excel
=PROPER(TRIM(F2))
```

Allowed outputs are Paid, Pending, Overdue and Refunded. A value outside that list needs investigation rather than a silent replacement.

## 8. Split optional surnames

Add First Name and Last Name without inventing missing information.

```excel
=IFERROR(PROPER(TRIM(MID(B2,FIND(",",B2)+1,LEN(B2)))),"")
```

This formula returns the last name or blank. For First Name use =PROPER(TRIM(IFERROR(LEFT(B2,FIND(",",B2)-1),B2))). Keep the cleaned full name as well.

## 9. Reconcile the finished table

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

The result has 94 rows and eight columns including split names. Amount total: 189996.09. The duplicate audit accounts for all 100 input rows. Checksums alone do not prove every value is correct.

## Expected checks
- Source rows: 100
- Numeric amounts before cleanup (XLSX): 86
- Retained rows: 94
- Exact duplicates removed: 6
- Numeric amounts after cleanup and deduplication: 94
- Amount total after deduplication: 189996.09
- Blank surnames retained: 84

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