Messy Data to Clean
Deliberately dirty rows with padded text, mixed case, inconsistent dates, numbers stored as text and duplicate records for data-cleaning practice.
Start the 9-task practice package — files and complete answers
Try the cleanup practice project
Separate from the raw downloads: a fixed 100-row workbook with tasks, blank helper cells and a visible answer key. Formula examples are text.
Download project workbook (.xlsx)What is in the file
6 columns, 100 data rows by default, plus a header row. These are the first ten rows of the default file.
| Record ID | Customer Name | City | Signup Date | Amount | Status |
|---|---|---|---|---|---|
| REC-1000 | Adam | Austin | 2024-02-23 | 1837.25 | Refunded |
| REC-1001 | EMMA | Kraków | 2024-07-17 | 1053.26 | Overdue |
| REC-1002 | felix | Bologna | 2025-04-09 | 269.28 | Pending |
| rec-1003 | Sofia | toronto | 2025-09-01 | 3273.3 | Paid |
| REC-1004 | Jonas | Malmö | 2024-06-22 | 2489.31 | Refunded |
| REC-1005 | Mia | Berlin | 2024-11-14 | 1705.33 | Overdue |
| REC-1006 | Oliver | Austin | 2025-08-07 | 921.35 | Pending |
| REC-1007 | Liam | BERLIN | 2025-12-30 | 137.36 | Paid |
| REC-1008 | LUCAS | Lisbon | 2025-05-27 | 533.11 | Refunded |
| REC-1009 | Lena | Manchester | 2025-10-19 | 3537.13 | Overdue |
Download 100 rows
6 columns · header included · no signup
Direct files work without JavaScript. Choose another size below.
Get the matching tasks and answer filesBuilt in your browser when you click. Nothing is uploaded, no signup, free for commercial use.
About this dataset
This six-column practice table is corrupted deliberately: text has extra spaces and mixed case, some amounts are text, dates have inconsistent representations, and complete rows are duplicated. The practice package below includes the complete cleaned reference and a source-row duplicate audit. A unique Record ID alone does not certify a clean row.
This cleanup workbook deliberately keeps dates as text, including mixed formats. Identify the format and locale before converting; a display format alone does not convert text to a numeric Excel date.
File layout and import notes
- Record ID, Customer Name, City, Signup Date, Amount and Status are the six columns. Duplicate rows include the same Record ID.
- Some amounts are text in the XLSX file. CSV has no cell types: import inference may convert numeric-looking strings back to numbers.
- The date column mixes ISO dates with day/month/year text. Choose a locale explicitly when importing; changing the display format does not convert text into a date.
Free practice package · 100 source rows
Tasks, complete answers and a file to keep
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.
Version 2026-10-08.1. These answers match the default 100-row source file, not the other sizes in the download control.
1. Start with the source file
Keep an unchanged copy. Work through the tasks before opening the answers.
Tasks only (.md) — no answers2. Compare with complete answers
The workbook contains the original data, every reference row and expected checks. The CSV contains the full reference table.
All files download directly without signup or JavaScript. Markdown (.md) handouts are plain-text files you can open in a text editor. The answer sheets contain fixed reference values; formula sheets, where included, recalculate in your spreadsheet application.
The printable HTML files open in a browser. Choose Print or Save as PDF. The tasks sheet has writing space and no hidden answers; the worked-answer sheet includes formulas and expected tables.
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.
9 tasks to work through
Inspect the raw file
Count numeric cells in E2:E101 before changing anything.
Show answer and method for task 1
=COUNT(E2:E101)86 numeric amounts in the XLSX file. CSV import may already convert text to numbers, so use XLSX for this check.
Remove exact duplicate rows
Work on a copy. Remove duplicates using every original column, not just a name or ID.
Show answer and method for task 2
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.
Normalize record IDs
Remove surrounding ordinary spaces and use uppercase IDs in a helper column.
Show answer and method for task 3
=UPPER(TRIM(A2))Keep the REC- identifier and its digits unchanged. Verify uniqueness after cleanup; do not delete conflicting IDs without inspection.
Clean names and cities
Trim ordinary spaces and normalize the simple synthetic names and cities. Use helper columns before replacing values.
Show answer and method for task 4
=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.
Convert amounts explicitly
Turn numeric text into numbers using the source decimal convention.
Show answer and method for task 5
=NUMBERVALUE(TRIM(E2),".",",")Fill down over the retained rows. All 94 amounts should be numeric. The total after deduplication is 189996.09.
Resolve mixed dates
Convert ISO text and DD/MM/YYYY text without guessing the computer locale.
Show answer and method for task 6
=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.
Normalize status values
Remove spaces and normalize the four allowed status labels.
Show answer and method for task 7
=PROPER(TRIM(F2))Allowed outputs are Paid, Pending, Overdue and Refunded. A value outside that list needs investigation rather than a silent replacement.
Split optional surnames
Add First Name and Last Name without inventing missing information.
Show answer and method for task 8
=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.
Reconcile the finished table
Check row counts, identifiers, date types and the amount total; then compare every row with the reference.
Show answer and method for task 9
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.
Check your results before opening the answers
Enter the values you calculated from the unchanged 100-row file. This checks a few totals and counts, not your whole workbook.
Use a decimal point, no thousands separators and at most two decimal places: 1234.56. Round averages to two decimals. Leave checks you have not tried blank.
Your entries are checked only in this page. They are not uploaded, saved or included in analytics. Use Clear values to remove them.
Need the worked solution? Go to the expected checks and summary tables below.
Show expected checks and summary tables
Check your work
| Check | Expected |
|---|---|
| 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 |
Preview the first three reference rows
| Record ID | Customer Name | City | Signup Date | Amount | Status | First Name | Last Name |
|---|---|---|---|---|---|---|---|
| REC-1000 | Adam | Austin | 2024-02-23 | 1837.25 | Refunded | Adam | (blank) |
| REC-1001 | Emma | Kraków | 2024-07-17 | 1053.26 | Overdue | Emma | (blank) |
| REC-1002 | Felix | Bologna | 2025-04-09 | 269.28 | Pending | Felix | (blank) |
Use this in a lesson or tutorial
Share the source file and tasks-only handout first, then release the answers. Use the checks to discuss different approaches; matching one total does not prove every row is correct.
Free for practice, teaching, demos and commercial use. Attribution is welcome, not required. These are generated records, not real customer or student data. Link to this page so learners can get matching files.
Download starts and successful formula copies may be counted without sending your spreadsheet contents or a visitor identifier. The site respects Do Not Track and Global Privacy Control for these counts. Downloads are not proof of task completion.
Clean this file and reconcile the totals
Use the default 100-row Excel download on a spare sheet. Add Name cleaned in G1 and Amount numeric in H1. Fill the two helper formulas down, then compare the first rows below.
Download project workbook (.xlsx) — Optional 100-row XLSX with Tasks, Practice and Answer key sheets. Two helper columns and seven responses start blank. The visible key supplies literal answers and text formula examples; it does not score your attempt.
G2: remove normal spaces and capitalize the name
=PROPER(TRIM(B2))H2: convert an amount to a number
=VALUE(TRIM(E2))Check numeric amounts before cleanup
=COUNT(E2:E101)Check numeric amounts after cleanup
=COUNT(H2:H101)Outside A:H: original numeric-only Amount total
=SUM(E2:E101)Outside A:H: converted Amount total
=SUM(H2:H101)| Excel row | B: Original name | G: Cleaned name | H: Numeric amount |
|---|---|---|---|
| 2 | Adam | Adam | 1837.25 |
| 3 | EMMA | Emma | 1053.26 |
| 4 | felix | Felix | 269.28 |
If columns are cut off, scroll the table sideways.
| Check | Expected |
|---|---|
| Numeric cells in E2:E101 before cleanup | 86 |
| Numeric cells in H2:H101 after VALUE | 100 |
| Original numeric-only Amount total | 164994.73 |
| Converted Amount total before removing rows | 200488.10 |
| Extra Amount included after text conversion | 35493.37 |
| Rows remaining after Remove Duplicates on all six original columns | 94 |
| Identical whole rows removed | 6 |
If columns are cut off, scroll the table sideways.
- These answers apply to the default 100-row file. With another size, update the final row in every range and calculate new totals.
- Use the XLSX file for the numeric-cell check. CSV import may turn numeric-looking text into numbers before you start.
- The optional project contains Tasks, Practice and Answer key sheets. Practice preserves A1:F101 and starts both helpers blank. Make your attempt before comparing the visible literal answers; the file does not execute formula examples or score your response.
- Fourteen Amount values are text. Range SUM omits those referenced cells; conversion adds 35493.37 to the original total. Display amounts with two decimal places and sum every original row before the duplicate task. Amount has no assigned currency or refunded sign rule.
- Leave dates, City, Status and Record ID unchanged in this two-helper project. Name capitalization is mechanical and does not certify spelling or identity. There is no Email column.
- On Windows or Excel for the web, run Data > Remove Duplicates on a separate copy of A1:F101, mark the header row and select all six columns. A customer name alone is not a safe duplicate key. Mac-specific menu wording is not verified here.
- VALUE uses your spreadsheet’s numeric conventions. If a dot-decimal amount gives an error in localized Excel, set the import locale or decimal separator explicitly.
- Leave the mixed ISO and day/month/year dates unchanged for this project. Before any later conversion, verify an explicit locale; a date display format does not resolve ambiguous text.
- TRIM does not remove non-breaking spaces. PROPER does not preserve acronyms and may capitalize after apostrophes. Review those exceptions before replacing source columns.
Documentation reviewed .
Microsoft: SUMMicrosoft: COUNTMicrosoft: TRIMMicrosoft: VALUEMicrosoft: Remove duplicate values
What you can practise with it
- TRIM padded text, then PROPER-case names
- Remove Duplicates and count what disappeared
- Convert numbers stored as text back to real numbers
- Split comma-separated "First, Last" values into two columns
Try a related exercise
- Find the last matching row, not the first — Use a reverse search to return the final recorded status for a repeated ticket, then check when the last row also means the latest date.
- Split a full name into first and last — Extracting part of a text value — the task behind most spreadsheet cleanup work.
- Fix a lookup broken by invisible spaces — Two values that look identical and refuse to match. This is the most-reported and least-understood spreadsheet bug there is.
- Remove non-breaking spaces that TRIM leaves behind — Reproduce a code containing Unicode non-breaking spaces, then replace those characters before trimming ordinary spaces.
Worked examples for this data
Tools that work on this data
Free to use, share and republish — including commercially. Attribution isn't required, but it's what keeps these free to make, and there's one ready to paste below.
Questions
Is this messy data to clean free to use?
Yes — for practice, teaching, demos and commercial work alike. The rows are generated, so there is no personal data in the file. Attribution is not required; if you would like to credit it, there is a ready-made line at the bottom of this page.
Do I have to sign up or give an email address?
No. Direct links download the default-size files without JavaScript. The row-count control builds other supported sizes in your browser. Your spreadsheet contents are not uploaded and no account is involved.
Which formats can I download?
The raw dataset downloads are Excel (.xlsx), CSV and JSON with the same original rows. The separate optional cleanup project is one XLSX with Tasks, Practice and Answer key sheets; it does not come in CSV or JSON.
Can I choose how many rows I get?
Yes. The browser control offers 25, 100 data rows, plus a header. Direct source links contain 100 data rows; the preview shows the first ten of those rows.
Does this file include tasks and complete answers?
Yes. The practice package has 9 tasks, a separate tasks-only handout, a full walkthrough and a workbook with every reference row and expected checks. Answers apply only to the default 100-row source file. The workbook is a reference and formula solution, not a native Excel execution certificate or a prebuilt interactive PivotTable.