How to Use SUMIFS to Analyse Data Like a Pro
If you’re still using SUM and IF separately to crunch numbers in Excel, it’s time to level up. SUMIFS is one of Excel’s most powerful functions for analysing data — and it’s surprisingly eas...
Read MoreWhether you're importing sales reports, copying data from a PDF, or inheriting a chaotic spreadsheet, dirty data is a fact of life for Excel users. The good news? Excel has powerful tools for cleaning, shaping, and standardising data — if you know where to look.
1. Start with TRIM, CLEAN, and SUBSTITUTE
TRIM() removes extra spaces — not just at the beginning and end, but also those sneaky double spaces in the middle.
=TRIM(A2)CLEAN() strips out non-printable characters that show up when importing from weird systems:
=CLEAN(A2)Need to replace unwanted characters (like dashes, question marks, or extra commas)? Use SUBSTITUTE():
=SUBSTITUTE(A2, "-", "")2. Split Data into Columns
If you have full names or addresses jammed into a single cell, use:
- Text to Columns (Data tab → Text to Columns)
- LEFT, MID, RIGHT functions for custom slicing
- TEXTSPLIT() (Excel 365) to split by delimiters dynamically
3. Convert Numbers Stored as Text
You’ve seen this error: “Number stored as text”. Select the column, use Text to Columns, or multiply by 1:
=A2*1Or even use =VALUE(A2). Either method restores numeric integrity.
4. Remove Duplicates — the Right Way
Don’t just delete rows manually. Use:
- Remove Duplicates (Data tab)
- Advanced Filter → Unique records only
- Power Query → Remove duplicates with full control
5. Use FIND/SEARCH + LEN to Discover Issues
Want to find rogue characters, hidden spaces, or inconsistent lengths?
=LEN(A2) // find if some values are longer than expected=FIND("?", A2) // locate strange punctuation6. Use Flash Fill for Smart Extraction
Flash Fill (Ctrl + E) guesses your pattern and fills in the rest. Extract first names, fix capitalisation, or combine fields — its fast and surprisingly accurate.
7. Power Query: The Data Cleaning Super Tool
If you’re doing repetitive cleaning, Power Query is your best friend. It can:
- Trim and clean all columns
- Split by delimiter or position
- Remove rows with nulls, blanks, or errors
- Set data types and standardise formats
And the best part? It remembers every cleaning step — no macros needed.
Final Thoughts
Cleaning dirty data isn’t glamorous, but it’s critical. Whether you’re prepping for a dashboard, audit, or pivot table, clean data = reliable results. Learn the tools, build habits, and let Excel do the grunt work.
Need a downloadable cleaning checklist or a sample file? Reach out and we’ll send you one.
