The Excel Data Cleaning Process I Use on Every File, by MyOnlineTrainingHub on YouTube. 11 min 10 s.
- Video length
- 11 min 10 s
- Time to do it
- 20 minutes
- Difficulty
- Easy
- Steps
- 8
What you need
Steps
Open your raw export in Excel and select any cell inside the data. Press Ctrl+T to convert the range to a Table and confirm the header row. Tables keep formulas and references locked to the right cells.
Add a helper column and use =TRIM(A2) to remove leading, trailing and double spaces. Wrap it in CLEAN, as in =TRIM(CLEAN(A2)), to strip tabs and non-breaking characters.
Select the date column and check the alignment. Left-aligned dates are text. Use Data > Text to Columns, click Next twice, set the column to Date with the right order, then Finish.
For numbers stored as text, select the column and use Data > Text to Columns with Finish, or multiply by 1 in a helper column. Real numbers will right-align and can be summed.
Press Ctrl+H to open Find & Replace. Fix typos and naming variants one at a time, using wildcards like *Dept* to catch variations before replacing them with one standard name.
Use =LEFT(A2,3) to pull a fixed-length code out of a combined field. For variable separators, use TEXTSPLIT or Data > Text to Columns with the delimiter.
Select the table and use Data > Remove Duplicates, choosing the invoice number column only. Then filter each column for blanks and fill or flag the gaps.
Build a quick PivotTable from the cleaned table and check row counts and totals against the original file. If the numbers match, the data is ready for reports.
Tips and common mistakes
Work on a copy of the raw file so you can always compare against the original.
Delete the helper columns only after you paste their results as values.
Watch for non-breaking spaces from web exports; TRIM alone will not remove them, so use CLEAN too.
Check a few dates manually after conversion, since day and month order can flip.
Questions
Why convert the data to a Table first?
A Table expands automatically and keeps formulas, references and PivotTables pointed at the full dataset. It also makes filtering and removing duplicates safer.
How do I know if my dates are really text?
Text dates sit on the left of the cell by default and real dates sit on the right. You can also check with =ISNUMBER on the cell.
Can I undo Remove Duplicates?
Only with Ctrl+Z right after the action. Otherwise work on a copy, because the duplicate rows are deleted permanently.
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.
