Bad data is one of the biggest hidden problems in any organisation. Duplicate records inflate your totals. Blank cells break your formulas. Dates stored as text refuse to sort. Numbers with spaces cannot be calculated. And inconsistent spellings — "Mumbai", "mumbai", "MUMBAI" — scatter your pivot tables into chaos.
The good news: Excel has powerful built-in tools to fix all of it. In this article, we cover the 8 most important data cleaning techniques every Excel user should know — with step-by-step instructions, real formulas, and practical examples.
Quick Reference — All 8 Techniques
Click any technique name to jump to its full explanation:
| # | Technique | What It Fixes | Key Tool / Formula |
|---|---|---|---|
| 1 | Remove Duplicates | Duplicate rows inflating counts and totals | Data → Remove Duplicates |
| 2 | Handle Blank Cells | Empty cells breaking formulas and Pivot Tables | Go To Special → Blanks |
| 3 | Fix Text Formatting | Inconsistent case, extra spaces, hidden characters | TRIM, CLEAN, PROPER, UPPER |
| 4 | Convert Text to Numbers | Numbers stored as text — SUM returns 0 | VALUE(), Paste Special × 1 |
| 5 | Fix Text Dates | Dates that won't sort, group, or calculate | DATEVALUE(), Text to Columns |
| 6 | Split & Combine Columns | Full name in one cell, address all in one column | Text to Columns, TEXTJOIN, LEFT/MID/RIGHT |
| 7 | Find & Replace + SUBSTITUTE | Inconsistent values, wrong entries across thousands of rows | Ctrl+H, SUBSTITUTE() |
| 8 | Validate & Flag Bad Data | Out-of-range values, wrong formats, future data entry errors | Data Validation, IFERROR, Conditional Formatting |
🗑️ Remove Duplicate Rows
The problem: Duplicate rows appear when data is copied from multiple sources, exported twice, or pasted more than once. They inflate totals, double-count customers, and make every report inaccurate.
The fix — Built-in tool:
- Select any cell in your data range
- Data tab → Remove Duplicates
- Choose which columns define a "duplicate" — check ALL for exact row match, or just Email/ID for logical duplicates
- Click OK — Excel tells you how many duplicates were removed and how many unique values remain
The fix — COUNTIF to find duplicates first (safer approach):
=COUNTIF($A$2:$A$100, A2) ' Count how many times this value appears
=IF(COUNTIF($A$2:A2, A2)>1, "Duplicate", "Unique") ' Flag from 2nd occurrence onward
Filter by "Duplicate" to review before deleting. This is safer than Remove Duplicates because you can verify each one first.
⬜ Handle Blank Cells
The problem: Blank cells in data cause formulas to error, Pivot Tables to show "(blank)" as a category, AVERAGE to skip rows, and sorting to push empty rows to the bottom (or top).
Method 1 — Find all blanks at once:
- Select your data range (e.g., A1:F500)
- Press Ctrl+G (Go To) → click Special → select Blanks → OK
- All blank cells are now selected (highlighted in blue)
- Type your fill value (e.g., 0, "N/A", "Unknown") → press Ctrl+Enter to fill ALL selected blanks at once
Method 2 — Formula to handle blanks:
=IF(A2="", "Not Provided", A2) ' Replace blank with text
=IF(A2="", 0, A2) ' Replace blank with zero
=IFERROR(A2/B2, 0) ' Handle divide-by-blank errors
=COUNTA(A2:A100) ' Count non-blank cells only
Method 3 — Delete entire rows with blanks in key column:
- Filter the key column (e.g., Customer ID) to show blanks only
- Select all visible rows → right-click → Delete Row
- Clear the filter → blank rows are gone
🧹 Fix Text Formatting — TRIM, CLEAN, PROPER
The problem: Text data imported from other systems often has: leading/trailing spaces (" Delhi "), extra spaces between words ("New Delhi"), non-printable hidden characters (from copy-paste or system exports), and inconsistent capitalisation ("MUMBAI", "mumbai", "Mumbai").
The core formula — always start with this:
=TRIM(CLEAN(A2))
' CLEAN removes non-printable characters
' TRIM removes extra spaces (leading, trailing, between words)
' Together: the ultimate text cleaner
Fix capitalisation:
=PROPER(TRIM(CLEAN(A2))) ' Capitalises First Letter Of Each Word → "New Delhi"
=UPPER(TRIM(CLEAN(A2))) ' ALL CAPS → "NEW DELHI"
=LOWER(TRIM(CLEAN(A2))) ' all lowercase → "new delhi"
After writing formula — paste as values:
- Copy the formula column → right-click original column → Paste Special → Values only
- Now delete the formula column — you have clean data in the original column
🔢 Convert Text to Numbers
The problem: Numbers look like numbers but behave like text — they align left instead of right, SUM returns 0, AVERAGE skips them, and a green triangle appears in the corner. This happens when data is imported from CSV, copied from a web page, or exported from another system.
How to identify text numbers:
- Numbers aligned to the LEFT = stored as text (real numbers align right)
- Green triangle in top-left corner of cell
- =SUM(A1:A10) returns 0 even though values are visible
- =ISNUMBER(A1) returns FALSE
Fix Method 1 — VALUE() formula:
=VALUE(A2) ' Converts text "50000" to number 50000
=VALUE(TRIM(A2)) ' Also removes spaces before converting
=VALUE(SUBSTITUTE(A2,",","")) ' Remove commas first: "1,000" → 1000
Fix Method 2 — Paste Special (fastest for many cells):
- Type 1 in any empty cell → Copy it (Ctrl+C)
- Select all your text-number cells
- Paste Special (Ctrl+Alt+V) → Values → Multiply → OK
- Multiplying by 1 converts text to numbers instantly — no formula needed
Fix Method 3 — Green triangle shortcut:
- Select cells with green triangles
- Click the yellow warning diamond that appears → Convert to Number
📅 Fix Dates Stored as Text
The problem: Dates imported from other systems are often stored as text — "01/04/2026" as a text string instead of a real Excel date. Text dates: do not sort correctly, cannot be grouped in Pivot Tables, cannot be used in date calculations (DATEDIF, NETWORKDAYS), and appear left-aligned instead of right-aligned.
How to identify text dates:
- Dates align to the LEFT = text. Real dates align RIGHT.
- Grouping in Pivot Table is greyed out
- =ISNUMBER(A2) returns FALSE for a date cell
- Sorting gives wrong order (01/01/2026, 01/02/2026 sort as text, not chronologically)
Fix Method 1 — DATEVALUE() formula:
=DATEVALUE(A2) ' Converts text date to Excel serial number
' Then format the cell as Date (Ctrl+1)
=DATEVALUE(TRIM(A2)) ' Also handles spaces in the text date
Fix Method 2 — Text to Columns (fastest for whole column):
- Select the date column
- Data tab → Text to Columns → Delimited → Next → Next
- Step 3: select "Date" and choose the format (DMY, MDY, YMD)
- Click Finish — entire column converts to real dates instantly
Fix Method 3 — Formula for specific formats:
' Text date "15-Apr-2026" → real date
=DATE(RIGHT(A2,4), MATCH(MID(A2,4,3), {"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}, 0), LEFT(A2,2))
' Text "20260415" (YYYYMMDD format) → real date
=DATE(LEFT(A2,4), MID(A2,5,2), RIGHT(A2,2))
✂️ Split and Combine Columns
The problem: Data often comes with information crammed into one cell — "Rahul Sharma" in one column when you need First and Last Name separately, or an address "123, MG Road, Mumbai, Maharashtra" when you need city and state as separate columns for analysis.
Split: Text to Columns (fastest for delimiters):
- Select the column to split
- Data tab → Text to Columns → Delimited
- Choose delimiter: Comma, Space, or Custom character
- Preview shows how the split will look → Finish
- Excel splits into adjacent columns automatically
Split: Formula approach (more flexible):
' Split "Rahul Sharma" into First and Last Name
' First Name:
=LEFT(A2, FIND(" ", A2) - 1) ' Everything before the space
' Last Name:
=MID(A2, FIND(" ", A2) + 1, LEN(A2)) ' Everything after the space
' Extract city from "123, MG Road, Mumbai, Maharashtra"
=TRIM(MID(A2, FIND("~~", SUBSTITUTE(A2, ",", "~~", 3)) + 1, LEN(A2)))
Combine: Join columns back together:
' Combine First + Last Name with space
=A2 & " " & B2
' Combine with separator
=TEXTJOIN(", ", TRUE, A2, B2, C2) ' "Delhi, India, 110001"
' Combine date into sentence
="Report for " & TEXT(A2, "DD-MMM-YYYY")
🔁 Find & Replace + SUBSTITUTE
The problem: Inconsistent values scattered across thousands of rows — "Mumbai", "Bombay", "mumbai", "MUMBAI" all meaning the same city. Wrong codes like "N/A", "NA", "#N/A" mixed with actual zeros. Old product names that have been renamed. Currency symbols mixed with numbers.
Find & Replace (Ctrl+H) — for quick bulk replacement:
- Press Ctrl+H to open Find & Replace dialog
- Find What: type the wrong value (e.g., "Bombay")
- Replace With: type the correct value (e.g., "Mumbai")
- Options → Match Case if needed (to avoid changing "bombay" differently)
- Replace All → done across the entire sheet in one click
- Use "Match entire cell contents" to avoid partial replacements
SUBSTITUTE() — formula approach for specific patterns:
=SUBSTITUTE(A2, "Bombay", "Mumbai") ' Replace specific text
=SUBSTITUTE(A2, " ", "") ' Remove ALL spaces
=SUBSTITUTE(A2, "₹", "") ' Remove currency symbol
=SUBSTITUTE(A2, ",", "", ) ' Remove all commas
=SUBSTITUTE(A2, CHAR(10), " ") ' Replace line break with space
' Nested SUBSTITUTE — remove multiple characters:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "(", ""), ")", ""), "-", "")
Replace blank-looking cells that are not actually blank:
' Cells showing "N/A" or "NA" as text:
Ctrl+H → Find: N/A → Replace: (leave empty) → Replace All
' Or with formula:
=IF(OR(A2="N/A", A2="NA", A2="-"), "", A2)
✅ Validate & Flag Bad Data
The problem: Cleaning existing data is reactive — you fix problems after they occur. Validation is proactive — it prevents bad data from entering in the first place. Flagging identifies bad data that already slipped through so you can deal with it before it corrupts your reports.
Data Validation — prevent bad entries:
- Select the cells where you want rules
- Data tab → Data Validation → Settings tab
- Allow: Whole Number, Decimal, List, Date, Text Length, or Custom formula
- Input Message: hint shown when user clicks the cell
- Error Alert: Stop (block entry), Warning (allow with warning), Information
' Custom validation formula examples:
=AND(A2>=0, A2<=100) ' Only values between 0 and 100
=ISNUMBER(A2) ' Only numbers allowed
=LEN(A2)=10 ' Exactly 10 characters (phone numbers)
=COUNTIF($B$2:B2, B2)=1 ' No duplicates in column B
=NOT(ISBLANK(A2)) ' Cell cannot be left blank
Flag existing bad data with formulas:
' Flag negative values that should not be negative
=IF(A2<0, "⚠️ Negative — Check", "OK")
' Flag dates in the future (data entry error)
=IF(A2>TODAY(), "⚠️ Future Date", "OK")
' Flag values outside expected range
=IF(OR(A2<1000, A2>100000), "⚠️ Check Amount", "OK")
' Flag text where number expected
=IF(NOT(ISNUMBER(A2)), "⚠️ Not a Number", "OK")
' Flag duplicates
=IF(COUNTIF($A$2:A2, A2)>1, "⚠️ Duplicate", "OK")
Conditional Formatting to highlight bad data visually:
- Select the data range → Home → Conditional Formatting → New Rule
- Use a formula: =A2="" highlights blank cells in red
- =A2<0 highlights negative values in orange
- =COUNTIF($A$2:$A$100,A2)>1 highlights all duplicates in yellow
Bonus: Quick Data Cleaning Checklist
Before any analysis or reporting, run through this checklist on your dataset:
| Check | How to Verify | Fix If Found |
|---|---|---|
| No duplicate rows | Data → Remove Duplicates (check count) | Remove Duplicates or flag with COUNTIF |
| No blank rows | Ctrl+End — data should end at last real row | Ctrl+G → Special → Blanks → Delete rows |
| No blank cells in key columns | =COUNTBLANK(A2:A1000) | Fill with 0, "Unknown", or delete row |
| Numbers are real numbers | =ISNUMBER(A2), check alignment | VALUE(), Paste Special × 1 |
| Dates are real dates | =ISNUMBER(A2), check alignment | DATEVALUE() or Text to Columns |
| No extra spaces in text | =LEN(A2) vs =LEN(TRIM(A2)) | =TRIM(CLEAN(A2)) |
| Consistent text values | Pivot Table on text column — look for duplicates | Find & Replace, SUBSTITUTE, PROPER |
| No merged cells in data | Select all → Format Cells → look for Merge | Unmerge All Cells |
| Column headers in Row 1 | Visual check | Delete blank rows above headers |
| No total rows inside data | Visual check / sort by column | Delete subtotal rows from within data |
🎯 Summary — 8 Techniques Every Excel User Should Know
- Remove Duplicates — Data → Remove Duplicates or COUNTIF to flag first
- Handle Blank Cells — Ctrl+G → Special → Blanks → Ctrl+Enter to fill all
- Fix Text Formatting — =PROPER(TRIM(CLEAN(A2))) cleans almost everything
- Convert Text to Numbers — VALUE() formula or Paste Special × 1
- Fix Text Dates — Text to Columns → Date format or DATEVALUE()
- Split & Combine Columns — Text to Columns for splits, & and TEXTJOIN for combining
- Find & Replace + SUBSTITUTE — Ctrl+H for bulk, SUBSTITUTE() for formula-based
- Validate & Flag Bad Data — Data Validation to prevent, Conditional Formatting to highlight
Frequently Asked Questions
Want to master Excel from scratch?
Our free Excel for Beginners course covers all the fundamentals — formulas, formatting, tables, and more — in 30 practical step-by-step lessons.