How to Clean Messy Data in Excel Fast

Every data analyst knows the reality: 80% of the work is cleaning data, 20% is actual analysis. Messy data — duplicates, blanks, inconsistent formatting, extra spaces, mixed cases — can derail any analysis. Here are the fastest techniques to clean it up.

1. Remove Duplicates

The quickest win. Select your data, go to Data → Remove Duplicates. Choose which columns to check for duplicates. Excel will tell you how many duplicates were found and removed.

Pro tip: Before removing, use conditional formatting to highlight duplicates first (Home → Conditional Formatting → Highlight Cell Rules → Duplicate Values). Review them before deleting — sometimes duplicates are legitimate.

2. Fix Extra Spaces

Hidden spaces are the #1 cause of failed VLOOKUP formulas. The TRIM function removes all leading, trailing, and double spaces:

=TRIM(A2)

For non-breaking spaces (common in web data), use:

=TRIM(SUBSTITUTE(A2, CHAR(160), " "))

3. Standardise Text Case

"john smith", "JOHN SMITH", and "John Smith" are three different values to Excel. Fix it with:

• =UPPER(A2) — JOHN SMITH
• =LOWER(A2) — john smith
• =PROPER(A2) — John Smith

4. Find and Replace

Ctrl+H opens Find and Replace. Powerful uses:

• Replace "N/A" with blank
• Replace old product codes with new ones
• Remove specific characters (search for the character, leave "Replace with" empty)
• Use wildcards: * matches any characters, ? matches one character

5. Split Data with Text to Columns

Got "First Last" in one column? Select the column → Data → Text to Columns:

• Delimited — Split by comma, space, tab, or custom delimiter
• Fixed width — Split at specific character positions

In Microsoft 365, use the TEXTSPLIT function instead for more flexibility.

6. Handle Blank Cells

To find and fill blanks:

1. Select your range
2. Press Ctrl+G → Special → Blanks
3. All blank cells are now selected
4. Type a value or formula and press Ctrl+Enter to fill all at once

7. Fix Numbers Stored as Text

If you see numbers left-aligned with a green triangle, they're stored as text. Fix:

• Select the cells → click the warning icon → "Convert to Number"
• Or: create a helper column with =A2*1 or =VALUE(A2)
• Or: select all → Data → Text to Columns → Finish (without changing settings)

8. Extract Data with Formulas

Common extraction tasks:

• Extract domain from email: =MID(A2, FIND("@",A2)+1, 100)
• Extract first name: =LEFT(A2, FIND(" ",A2)-1)
• Extract numbers from text: Use TEXTSPLIT or a combination of MID and ROW

9. Use Flash Fill

Excel's Flash Fill (Ctrl+E) is magic for pattern-based cleaning. Type the desired result for 2-3 rows, then press Ctrl+E — Excel detects the pattern and fills the rest. Works for:

• Name reformatting ("Smith, John" → "John Smith")
• Extracting parts of strings
• Combining data from multiple columns
• Reformatting phone numbers and dates

10. Data Cleaning Checklist

Check	Tool/Method
Duplicates	Remove Duplicates / Conditional Formatting
Extra spaces	TRIM function
Inconsistent case	UPPER/LOWER/PROPER
Blank cells	Go To Special → Blanks
Numbers as text	Text to Columns / VALUE
Date formats	Format Cells → Date
Spelling errors	Find & Replace with wildcards
Column splitting	Text to Columns / Flash Fill