Episode 1: Practical Excel Data Cleaning | Data Analyst Bootcamp, by Abdikarim Mataan on YouTube. 59 min 57 s.
- Video length
- 59 min 57 s
- Time to do it
- 1 hour
- Difficulty
- Medium
- Steps
- 9
What you need
Steps
Make a copy of the raw file and keep it untouched. Do all cleaning on the copy so you can always compare back to the original data.
Create a cleaning log sheet. Write down each problem you find, the fix you applied and the number of rows affected.
Remove exact duplicate rows. Select the data, open Data then Remove Duplicates, tick every column and confirm how many rows were deleted.
Find fake nulls. Use Find and Replace or a filter to catch values like N/A, null, NULL and single spaces, then standardise them as blanks.
Standardise text formats. Trim extra spaces with TRIM, fix inconsistent capitalisation and split or merge columns so each field holds one value.
Repair broken dates. Convert text dates with Date then Text to Columns, or use DATEVALUE, and set one date format for the whole column.
Check number formats. Remove currency symbols and commas from amount columns with Find and Replace so Excel treats them as real numbers.
Review outliers with business context. Filter or sort the amount column and decide case by case whether a large value is an error or a real transaction.
Run a final check. Use COUNT, COUNTIF and filters to confirm no blanks, duplicates or odd formats remain, then save the clean file separately.
Tips and common mistakes
Never delete rows from the raw file. Keep the original so you can prove what changed.
A single space looks like an empty cell but breaks formulas, so check for it with LEN or TRIM.
Write the cleaning log as you go. It is the habit that makes your work easy to review and repeat.
Questions
Do I need Power Query for this?
No. The workflow uses standard Excel features like Remove Duplicates, Find and Replace, Text to Columns and filters. Power Query comes later in the series.
How do I know which outliers to keep?
Use the business meaning of the column. A very large mobile money transfer may be real, while a negative or zero amount may be an error.
Can I reuse this workflow on other datasets?
Yes. The same order works for most messy tables: copy, log, deduplicate, fix nulls, standardise formats, repair dates, review outliers and verify.
This guide was drafted from the video's details and general task knowledge. It has not been checked against the full video. Check the video and relevant product instructions before following a procedure. Report a correction.
