Data Cleaning in Excel: How to Remove Blank Rows, Spaces & Duplicates

Dirty data does not announce itself. A double space after "Mumbai" hides from the eye but breaks a VLOOKUP and splits a Pivot Table into two cities. This is weeks 5–6 of the Data Analyst Roadmap — the gate between raw files and trustworthy pivot tables and dashboards. You will also reuse the backup habit from interface basics.

What issues should you hunt before any analysis?

Run a 60-second scan: filter each column for blanks, sort A → Z to surface odd casing, and count duplicates via COUNTIF. Common culprits in a presidents or sales dataset: inconsistent casing ("SUNIL" vs "Sunil"), stray spaces, "Republican" vs "Republicans", currency stored as text ("₹1,200"), and dates as 13/04/2025 in one row and Apr 13 in the next.

Each of these creates a branching error: filters miss rows, lookups return #N/A, and pivots show two totals for one group.

How do you fix text without touching raw data?

Work in a helper column, then freeze.

=TRIM(A2)
=PROPER(TRIM(A2))
=LOWER(A2)
=UPPER(A2)

TRIM strips leading/trailing and reduces inter-word gaps to one Microsoft Support: TRIM. PROPER capitalizes each word for names and cities Microsoft Support: PROPER — perfect after TRIM. Use LOWER on emails before deduping so SUNIL@EXAMPLE.COM and sunil@example.com match.

For categories with typos ("Republican" vs "Republicans"), filter the column to reveal variants, replace or group them, then apply data validation to prevent recurrence.

How do you remove duplicates safely?

Select the Table (Convert to Table first per filter hygiene), then Data > Remove Duplicates and check all columns that define uniqueness Microsoft Support: Find and remove duplicates. Excel reports how many were removed — sanity-check that count against COUNTIF before you accept.

Keep a Raw sheet untouched. Dedupe is destructive; you cannot undo it tomorrow without the backup.

Why must you Paste as Values before deleting helpers?

Formulas point to the source. Copy → Paste as Values via paste options freezes the cleaned text Microsoft Support: Paste options. Now it is safe to delete the raw column. Skip this and every =TRIM(A2) becomes #REF!.

' 1. Helper: B2 =TRIM(A2)  → clean values appear
' 2. Copy B → Paste as Values onto B (freeze)
' 3. Delete column A safely
' 4. For numbers: set format to Number/Currency after cleaning symbols

The same freeze applies to dates: standardize to Short Date via number formatting so Python and SQL imports do not reinterpret 04/13 as text.

How to Remove Blank Rows in Excel Without Corrupting Tables

Blank rows interrupt data contiguousness, breaking AutoSum (Alt + =), sorting boundaries, and dynamic charts. Understanding how to remove blank rows in excel safely protects valid adjacent records:

Method 1: The Go To Special Technique

1. Select your target key column (e.g. A2:A500). Never select the entire sheet.
2. Press Ctrl + G (or F5) $\rightarrow$ Click Special...
3. Select Blanks $\rightarrow$ Click OK. Excel highlights only the empty cells in that column.
4. Right-click any highlighted cell $\rightarrow$ Delete... $\rightarrow$ Entire row.

For comprehensive edge-case guidance (including Filter by Blanks and Power Query bulk deletions), read our full tutorial on how to delete blank rows in Excel.

Method 2: Data Cleaning via Deduplication

Once blank rows are removed, eliminate duplicate records using the ribbon's Data > Remove Duplicates tool or modern dynamic array functions. See our in-depth guide on how to remove duplicates in Excel.

What about Text to Columns and Power Query?

Use Text to Columns (Data tab) for one-off splits on comma or fixed width (e.g., "Mumbai, India" → two columns). Use Flash Fill (Ctrl + E) only when the pattern is obvious and you will not repeat it. For weekly extracts with the same mess, promote to Power Query (see formulas vs Power Query): steps are recorded and refresh cleanly.

Entry-level analysts at ₹5–10 LPA are evaluated on the sheet they deliver, not the raw file they received. A cleaned, validated table that pivots and looks up without error is the first proof of analyst judgment.

Quick Reference

Next: conditional highlighting on clean data, then pivot and chart with confidence.