Most spreadsheets you receive at work need cleaning before you can trust a single total. These techniques cover the problems you will meet most often.
Always keep the original data on its own sheet and clean a copy. If something goes wrong, you can start again.
1. Convert the range to a Table
Select the data and press Ctrl + T. Tables expand automatically, keep formulas consistent down the column and make filtering easy.
2. Remove duplicates
Data โ Remove Duplicates. Choose the columns that define a duplicate: the same order ID twice is a duplicate; the same customer on two dates is not. Check the count Excel reports.
3. Remove extra spaces
=TRIM(A2)TRIM removes leading, trailing and repeated spaces. For non-breaking spaces copied from web pages, use =TRIM(SUBSTITUTE(A2, CHAR(160), " ")).
4. Fix capitalisation
=PROPER(A2) for names, =UPPER(A2) for codes, =LOWER(A2) for emails.
5. Split columns
Data โ Text to Columns splits by a delimiter (comma, dash, space). In newer Excel, =TEXTSPLIT(A2, " ") does it with a formula. Flash Fill (Ctrl + E) learns a pattern from one or two examples you type.
6. Combine columns
=TRIM(B2 & " " & C2)
=TEXTJOIN(", ", TRUE, B2:D2)7. Convert text to numbers
Numbers stored as text (often left-aligned with a green triangle) will not sum. Fixes: the warning menu โ Convert to Number, =VALUE(A2), or multiply by 1. Remove thousands separators or currency symbols first with SUBSTITUTE.
8. Fix dates
Dates stored as text, or day and month swapped, break sorting and date maths. Use Text to Columns with the correct date format (DMY), or =DATE(year, month, day) built from parts. Check a few converted values by hand.
9. Find and handle blanks
Home โ Find & Select โ Go To Special โ Blanks selects every empty cell. Decide per column: fill with a value, fill with the value above (type = and the cell above, then Ctrl + Enter), or leave blank and note it.
10. Standardise categories
Filter a column to see all its distinct values. Fix variants ("Mumbai", "mumbai", "Bombay") with Find and Replace or a small mapping table plus XLOOKUP.
11. Highlight problems
Conditional formatting โ Highlight Duplicate Values, or rules such as "less than 0" for amounts and "greater than today" for dates, makes errors visible.
12. Data validation for future entries
Data โ Data Validation restricts input to a list or range, so new rows stay clean.
Make it repeatable with Power Query
If you clean the same kind of file every week, use Data โ Get Data โ From File (Power Query). Every step is recorded and replays on the next file with one Refresh. See the Power Query cleaning guide.
Keep a cleaning log
Write down what you changed and how many rows were affected. It makes your numbers defensible.
The same steps in code
Every technique here has an SQL and pandas version. When your files get bigger or the cleaning repeats, try the pandas cleaning set and SQL string functions.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: