Data cleaning in Excel is one of the most important skills for students, data analysts, accountants, business professionals, and job seekers.
Before creating dashboards, reports, or analysis — your data must be clean, structured, and error-free.
In this detailed guide from AI Excel Unlocked, you will learn:
- ✅ Essential Excel data cleaning formulas
- ✅ Advanced cleaning techniques
- ✅ Spreadsheet examples
- ✅ Real-world scenarios
- ✅ Combination formulas for automation
📌 What is Data Cleaning in Excel?
Data cleaning means correcting or removing:
- Extra spaces
- Wrong text formatting
- Duplicates
- Errors (#N/A, #VALUE, #DIV/0!)
- Blank cells
- Mixed numbers & text
- Special characters
Clean data = Accurate reports + Better decision-making.
🧾 Example of Raw Data (Before Cleaning)
| Name | Phone Number | Salary | Department |
|---|---|---|---|
| hARkEsH kuMaR | 98765-43210 | 25000 | sales |
| riYa shARma | 91234-56789 | text30000 | MARKETING |
| AMIT SINGH | 88888-99999 | 40000 | hr |
Problems in this sheet:
- Extra spaces
- Incorrect capitalization
- Text in numeric field
- Inconsistent department case
Let’s fix it step by step 👇
🔹 1. TRIM + CLEAN (Best Combination Formula)
Problem:
Extra spaces + hidden characters.
Formula:
=TRIM(CLEAN(A2))
Why Combine?
- TRIM removes extra spaces
- CLEAN removes non-printable characters
This is the most recommended cleaning formula.
🔹 2. PROPER Function – Fix Name Formatting
Problem:
Names like → hARkEsH kuMaR
Formula:
=PROPER(A2)
Result → Harkesh Kumar
Best practice:
=PROPER(TRIM(A2))
🔹 3. UPPER & LOWER – Standardize Data
If department names are inconsistent:
sales, MARKETING, hr
Convert All to Upper Case:
=UPPER(D2)
Convert All to Lower Case:
=LOWER(D2)
This helps in filtering & pivot tables.
🔹 4. VALUE Function – Convert Text Salary to Number
Problem:text30000
Formula:
=VALUE(B2)
If numbers are mixed with text, use:
=VALUE(SUBSTITUTE(C2,"text",""))
Now salary becomes numeric.
🔹 5. SUBSTITUTE – Clean Phone Numbers
Phone format:98765-43210
Remove dash:
=SUBSTITUTE(B2,"-","")
You can also replace multiple characters.
🔹 6. TEXTSPLIT – Separate First & Last Name (Excel 365)
Full Name in one cell:Harkesh Kumar
Formula:
=TEXTSPLIT(A2," ")
Result:
| First Name | Last Name |
|---|---|
| Harkesh | Kumar |
🔹 7. LEFT, RIGHT & MID – Extract Specific Text
Extract First 5 Digits of Phone:
=LEFT(B2,5)
Extract Last 5 Digits:
=RIGHT(B2,5)
Extract Middle Characters:
=MID(B2,2,4)
Very useful for ID numbers, GST, Invoice codes.
🔹 8. IFERROR – Handle All Errors Professionally
Instead of showing #VALUE or #DIV/0:
=IFERROR(A2/B2,"Invalid Data")
This makes reports clean & professional.
🔹 9. Remove Blank Cells Using Formula
Check if cell is blank:
=IF(A2="","Missing",A2)
Or count blanks:
=COUNTBLANK(A1:A100)
🔹 10. Remove Duplicates (Excel Tool)
Steps:
- Select data
- Go to Data Tab
- Click Remove Duplicates
- Choose column
Very important before analysis.
🔹 11. Find & Replace (Quick Cleaning Method)
Shortcut: Ctrl + H
Use for:
- Removing symbols
- Replacing text
- Standardizing entries
Example:
Replace Sales and sales with SALES
🔹 12. Flash Fill – Smart Cleaning Tool
Shortcut: Ctrl + E
Example:
If email format is inconsistent, type one correct example → Flash Fill automatically completes rest.
🔹 13. Advanced Combination Formula (Professional Level)
Clean + Proper + Remove Dash + Convert to Number:
=VALUE(SUBSTITUTE(TRIM(CLEAN(A2)),"-",""))
This is powerful automation formula.
📊 Final Cleaned Data Example
| Clean Name | Phone | Salary | Department |
|---|---|---|---|
| Harkesh Kumar | 9876543210 | 25000 | SALES |
| Riya Sharma | 9123456789 | 30000 | MARKETING |
| Amit Singh | 8888899999 | 40000 | HR |
Now this data is ready for:
- Pivot Tables
- Charts
- Dashboards
- Reports
🚀 When to Use Power Query Instead of Formulas?
If your dataset has:
- 10,000+ rows
- Imported CSV files
- Repeated cleaning tasks
Use Power Query (Get & Transform Data).
Benefits:
✔ One-time setup
✔ Auto-refresh
✔ Faster than formulas
🎯 Real-World Use Cases
✔ HR Data Cleaning
✔ Sales Report Preparation
✔ Customer Database Management
✔ Banking & Finance Reports
✔ GST & Invoice Data Preparation
📈 Why Data Cleaning Skills Are Important for Jobs?
Interview questions often include:
- How do you remove extra spaces in Excel?
- How do you fix text formatting?
- How do you remove duplicates?
- What is TRIM function?
If you master these, you increase job opportunities.
🏆 Pro Tips from AI Excel Unlocked
✔ Always keep original raw data sheet
✔ Create separate “Clean Data” sheet
✔ Use combination formulas
✔ Convert data into Table (Ctrl + T)
✔ Validate data before reporting
📌 Conclusion
Data cleaning is not optional — it is mandatory for professional Excel users.
By mastering:
- TRIM
- CLEAN
- SUBSTITUTE
- VALUE
- TEXTSPLIT
- IFERROR
- Flash Fill
You can transform messy spreadsheets into professional datasets.
Keep practicing and improve your productivity with AI Excel Unlocked 🚀