Messy data does not always need a complicated solution. If a contact list has extra spaces, inconsistent capitalization, invalid dates, unknown categories, or possible duplicates, a small Excel workbook can help you clean it without changing the original source.
In this project, you will build a data-cleaning workbook for 20 made-up practice records. You will preserve the raw data, document each cleaning rule, create a cleaned table, and send questionable records to a separate review list instead of guessing.
By the end, you will have 18 cleaned records, 2 records that need review, and a clear check showing that all 20 source records are still accounted for.
What the finished data-cleaning workbook includes
The downloadable workbook contains four main working areas:
- Raw Data: the untouched source records.
- Transformation Map: a table showing how each source field will be cleaned.
- Clean Data: records that passed the documented rules.
- Exceptions: records that need a person to review them.
The practice example focuses on five manageable cleaning tasks:
- Remove extra spaces from names.
- Remove nonprinting characters from text.
- Convert recognizable date text into Excel dates.
- Keep only approved category values.
- Separate records with invalid or unknown values for review.
Use the accompanying Data-Cleaning Workbook to follow the project and inspect the finished setup. It includes made-up practice data, working formulas, a field-cleaning map, an exceptions sheet, instructions, documented checks, a failure-and-repair example, and version history.
Workbook note: The file uses made-up practice data that contains no real personal information. Make a separate copy before adding your own records. Review formulas, privacy, permissions, and compatibility before using the workbook with real information.
Keep the scope small enough to finish
Data cleaning can grow into a much larger project if you try to solve every possible problem at once. For this workbook, keep the first version limited to a few rules you can explain and check.
A realistic first scope is:
- Trim extra spaces.
- Remove nonprinting characters.
- Convert valid date text into real Excel dates.
- Accept only the approved categories A, B, and C.
- Move impossible dates and unknown categories to the review list.
This project does not try to identify people across different systems, fill missing values, decide whether similar names represent the same person, or make automatic corrections when the right answer is unclear.
Preserve the raw data
Open the Raw Data sheet. It contains 20 practice records with a Record ID, Raw Name, Raw Date, and Raw Category.
Do not clean the only copy of the source. Keeping the raw records unchanged gives you a reference point when a cleaned value looks wrong or when a rule needs to change.
A useful raw-data sheet should include:
- A stable record ID for every row
- The original values exactly as received
- A note describing where the data came from
- The number of source rows
- No manual cleanup inside the source columns
The practice workbook uses 20 source rows. Two contain deliberate problems: one impossible date and one category that is not on the approved list.
Document each cleaning rule
The Transformation Map explains what will happen to each field. This prevents undocumented cleanup and makes the results easier to review.
| Output field | Source field | Cleaning rule | What happens when the value fails |
|---|---|---|---|
| Clean Name | Raw Name | Remove extra spaces and nonprinting characters while preserving punctuation. | Send blank or unusable names to review. |
| Clean Date | Raw Date | Convert recognizable date text into an Excel date. | Send impossible dates to review. |
| Clean Category | Raw Category | Accept A, B, or C. | Send unknown categories to review. |
Write the rule before changing the data. If the rule is hard to explain in one or two sentences, it may be too broad for the first version.
Clean the name field
The workbook uses TRIM and CLEAN together. TRIM removes extra spaces from text, while CLEAN removes many nonprinting characters.
The formula follows this pattern:
=TRIM(CLEAN('Raw Data'!B6))
This is useful for names with spaces before or after the text. It should not remove meaningful punctuation such as apostrophes or hyphens.
For example:
Alex StonebecomesAlex Stone.JORDAN-LEEkeeps its hyphen.Sam O'Neilkeeps its apostrophe.
Capitalization is a separate decision. Do not automatically convert every name to title case unless you have checked how that rule handles initials, prefixes, acronyms, and intentionally styled names.
Convert recognizable date text
The workbook uses DATEVALUE to convert recognizable date text into an Excel date value.
The formula follows this pattern:
=DATEVALUE('Raw Data'!C6)
A date such as 2026-10-05 can be converted and then displayed with a consistent date format. An impossible value such as 2026-99-99 should not be guessed or silently replaced. It belongs on the Exceptions sheet.
Date interpretation can vary by regional settings. If your source uses formats such as 03/04/2026, confirm whether the value means March 4 or April 3 before converting it.
Control category values
The practice workbook accepts categories A, B, and C. The Raw Data sheet includes a drop-down for these values, but the cleaning process must still account for unexpected source values.
If a record contains UNKNOWN, do not force it into A, B, or C. Keep the original value and send the record to the Exceptions sheet with a reason.
This approach makes uncertainty visible. It also prevents an incorrect category from looking like a confirmed decision.
Separate clean records from exceptions
The Clean Data sheet contains the 18 records that meet the documented rules. The Exceptions sheet contains two records that need review:
| Record | Problem | Raw value |
|---|---|---|
| R019 | Impossible date | 2026-99-99 |
| R020 | Unknown category | UNKNOWN |
The review list is not a failure. It is a control that keeps uncertain values from entering the cleaned table unnoticed.
Check that every row is accounted for
The most important count check is:
Clean records + exception records = raw records
For the practice workbook:
18 clean + 2 exceptions = 20 raw records
If the count does not match, a record may have been lost, duplicated, counted twice, or excluded without explanation.
You can also compare other before-and-after measures:
- Blank names
- Invalid dates
- Unknown categories
- Possible duplicate IDs
- Total source rows
Try five practical checks
| Check | What to do | Expected result |
|---|---|---|
| Valid records | Run the 18 records that follow the rules. | All 18 appear in Clean Data. |
| Punctuation | Use a name with a hyphen or apostrophe. | The punctuation remains intact. |
| Impossible date | Use 2026-99-99 as a source date. | The record appears in Exceptions rather than Clean Data. |
| Row count | Add clean and exception records. | The result equals the 20 raw records. |
| Repeat the process | Run the same rules against the unchanged source again. | The workbook produces the same cleaned values and exceptions. |
Formula method or Power Query?
The downloadable workbook uses formulas so you can see each cleaning step directly in the cells. This is a good fit for a small learning project with a fixed number of records.
Power Query is Excel’s repeatable data-import and cleaning tool. It may be a better choice when you need to import larger files, repeat the same steps often, combine several sources, or reshape columns and rows.
The workbook does not contain a Power Query query. Power Query is presented as another method you can explore after you understand the cleaning rules.
Customize the workbook for your data
After you understand the practice example, make a copy and adapt it carefully.
- Replace the source records with a safe copy of your own data.
- Update the approved category list.
- Rewrite the field-cleaning map before adding new rules.
- Keep the raw sheet unchanged.
- Add clear reason labels to the Exceptions sheet.
- Confirm that formulas cover every intended row.
- Recheck the row counts after every change.
- Remove personal or confidential information before sharing the workbook.
The supplied formulas cover the practice rows in the workbook. If you add more source records, extend the formulas or convert the working ranges into the structure you plan to maintain.
Common mistakes to avoid
- Cleaning the only source copy: Preserve the raw records first.
- Making manual edits without a rule: A change that cannot be repeated is difficult to review.
- Guessing unknown values: Send uncertain records to the Exceptions sheet.
- Deleting possible duplicates automatically: Similar records are not always duplicates.
- Changing capitalization without checking edge cases: Names and codes may use intentional formatting.
- Reporting improvement without a starting count: Compare before-and-after measures.
- Forgetting the row-count check: Clean records and exceptions should account for every source row.
What this project helps you practice
This project gives you experience with preserving source data, documenting cleaning rules, using TRIM, CLEAN, and DATEVALUE, controlling category values, separating exceptions, and checking that record counts still match. It also shows why uncertain data should remain visible instead of being silently changed.
Continue the project workflow
- Analyze the cleaned response data when your source came from a form or survey.
- Create a reusable data-quality checklist when the same cleaning review will happen more than once.
- Present the data-cleaning workbook as a spreadsheet project if you want to explain the rules, exceptions, and before-and-after checks.