12 Advanced Data Cleaning Steps, by Excel Wale Sir on YouTube. 28 min 53 s.
- Video length
- 28 min 53 s
- Time to do it
- 30 minutes
- Difficulty
- Medium
- Steps
- 10
What you need
Steps
Save a copy of your file first. Work on the copy so the original messy data stays intact if something goes wrong.
Select your data range and press Ctrl+T to turn it into a table. Tables make filters, formulas and cleanup easier to manage.
Remove duplicates with Data > Remove Duplicates. Tick the columns that define a duplicate record, then confirm how many rows were deleted.
Find blanks with Ctrl+G > Special > Blanks. Either fill them with a value or type a marker such as NA so missing data is visible.
Trim extra spaces using the TRIM function in a helper column, for example =TRIM(A2). Copy the results and paste them back as values.
Fix inconsistent text with Find and Replace (Ctrl+H). Replace variants like "ltd" and "Ltd." with one standard spelling.
Split combined data with Data > Text to Columns, choosing comma, space or a fixed width. Join columns again with the & operator or TEXTJOIN.
Standardise dates and numbers with Format Cells (Ctrl+1). Pick one date format and one number format, and convert text numbers with Value or Paste Special > Multiply.
Strip unwanted characters with Find and Replace or CLEAN and SUBSTITUTE. Check for stray symbols, line breaks and non-printing characters.
Spot errors with filters and conditional formatting, such as highlighting values above or below a sensible range. Fix or flag each one before you analyse.
Tips and common mistakes
Always keep an untouched copy of the raw data so you can compare before and after.
Convert helper formula columns to values before deleting the originals, or your results will break.
Use filters to scan each column for odd entries; errors are easier to see column by column.
Save often and name versions clearly, for example data_clean_v1 and data_clean_v2.
Questions
Do I need Power Query for this?
No. All twelve tasks can be done with standard Excel tools like Remove Duplicates, TRIM, Find and Replace and Text to Columns. Power Query is optional for repeatable jobs.
How do I know which rows are duplicates?
Decide which columns should be unique, such as an ID or email. Select only those columns in Remove Duplicates so rows that merely look similar are not deleted.
What should I do with missing values?
Either fill them with a known value or mark them clearly, such as NA. Never leave blanks in numeric columns you plan to sum or chart.
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.
