Most datasets arrive with problems: extra spaces, mixed date formats, duplicates and blanks. Cleaning them before you analyse saves you from wrong totals later. Here is a step-by-step checklist for Excel.
1. Keep a copy of the raw data
Never clean the original sheet. Copy it to a new sheet and work there, so you can always check against the source.
2. Remove extra spaces and fix case
Use TRIM to remove extra spaces and PROPER or UPPER to make text consistent. “Bangalore ” and “bangalore” will otherwise count as two cities.
3. Fix dates and numbers stored as text
Check that dates sort correctly and numbers add up. Use Text to Columns or DATEVALUE to convert them.
4. Remove duplicates carefully
Use Remove Duplicates only after deciding which columns define a real duplicate. Two orders from the same customer on the same day may both be real.
5. Decide what to do with blanks
Leave, fill or remove each blank, and write down your choice in a notes sheet.
6. Check the totals
Compare the row count and the main total with the raw sheet. Explain any difference.
Next step: see our data analytics course at ScholarLogic, or book a free demo class. A counsellor will share the fee and the next batch dates.
