Messy Excel Data for Practice — File, Tasks & Answers

Open this HTML file in your browser, then choose Print or Save as PDF. No sign-in, script or upload is needed. This file includes the worked answers.

1. Inspect the raw file

   Count numeric cells in E2:E101 before changing anything.

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

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

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

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

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

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

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

Matching these checks does not prove every row is correct. Use the reference workbook or CSV to compare the complete result. The tables below are fixed expected values, not interactive PivotTables.