Data Cleaning
Lessons
Practice file
⬇ Download m6-cleaning-practice.xlsxData Cleaning practice workbook
Use m6-cleaning-practice.xlsx for all five lessons. Start with Read Me. Keep original data and work on copies or in the yellow destination cells. Use Answers after your first attempt.
| Lesson | Sheets | Practice |
|---|---|---|
| 1 | Duplicates, Duplicate Answer | Detect repeats, choose Mobile as the key and retain three contacts on a copy. Compare spaced names. |
| 2 | Delimited, Fixed Width | Preserve a leading-zero identifier, convert DMY/YMD dates, skip a field and keep an intentional empty field. |
| 3 | Wildcards | Copy source values into the output columns, then remove suffixes, literal stars and line breaks. Compare whole-cell matches with substring matches. |
| 4 | Lists, Entry Practice, Entry Example | Configure rules on Entry Practice; compare with working dropdowns, dependent products, date/length/quantity rules and Stop alerts on Entry Example. |
| 5 | Sales, Visible Rows, Sorted Answer | Sort complete rows, filter Laptop sales above 40000, use SUBTOTAL and copy only visible rows. |
हिंदी: पहले खुद अभ्यास करें, फिर answer sheets से मिलाएँ। Entry Example में नियम लगे हैं; Entry Practice में आपको लगाने हैं। Category बदलने पर Product दोबारा चुनें। Copy-paste के बाद डेटा अलग से जाँचें।
The validation date range is fixed at 01-Apr-2026 through 31-Mar-2027, matching the lesson's exercise. Mobile fields use Text format and a ten-character rule; that rule alone does not verify digits. Blank unused rows are allowed. Workbook-scoped names are supplied for Categories, PayModes, Electronics and Furniture.
Excel commands such as Remove Duplicates, Text to Columns, Find & Replace, Sort and Filter must be run by the learner. The workbook includes test data and reference results; it does not run those commands automatically. Use desktop Excel for the described dialogs and shortcuts. No macros or external connections are included.