How to Cleanse Data in Excel Like a Pro: Step-by-Step Guide

Step 1: Remove Duplicate Records in Excel

Duplicate entries are one of the most common data quality issues. They distort totals, skew calculations, and create false insights.

Excel provides multiple ways to eliminate duplicates.

Method 1: Remove Duplicates tool

Go to:

Data → Remove Duplicates

Then:

1. Select the appropriate columns.
2. Confirm the selection.
3. Remove duplicate rows instantly.

Use this when you trust the dataset and want fast cleanup.

Method 2: Highlight duplicates visually

For a safer preview approach:

1. Select the dataset.
2. Go to Home → Conditional Formatting → Highlight Cell Rules → Duplicate Values.

This method highlights duplicates without deleting them.

Method 3: Filter unique values

Use:

Data → Advanced Filter → Unique records only

This keeps one copy of each value while filtering others out.

Step 2: Fix Common Formatting and Structural Errors

Formatting issues are subtle but destructive. They affect formulas, sorting, and data modeling. Luckily, Excel has strong tools to fix them.

1. Remove extra spaces

Extra spaces break matching and lookup formulas. The TRIM() function removes them instantly.

Example:
=TRIM(A2)

2. Standardize text case

Inconsistent text case creates multiple versions of similar values.

Use:

• =LOWER() for lowercase
• =UPPER() for uppercase
• =PROPER() for sentence case

3. Remove non-printable characters

Imported files often include invisible characters that break formulas.

Use:

=CLEAN(A2)

4. Convert text numbers into real numbers

Text numbers cause calculation errors.

Use:

• Text to Columns
• Multiply by 1 (=A2*1)
• Add 0 (=A2+0)

5. Standardize date formats

Dates often appear as:

• DD/MM/YYYY
• MM-DD-YY
• Text-based dates

Convert them using:

Home → Number Format → Date

6. Use Text to Columns for restructuring

Great for splitting values like:

• Full name → First + Last
• Address → Street + City
• Codes → Category + ID

Go to:

Data → Text to Columns

Choose:

• Delimited
• Or fixed width

This makes unstructured text usable.

Step 3: Use Excel’s Data Validation Tools to Prevent Future Errors

Data validation ensures your data stays clean over time. It restricts invalid entries and reduces future cleanup workloads.

Benefits include:

• More accurate entries
• Standardized values
• Fewer formatting issues
• Greater team compliance

Data validation can reduce data entry errors by 30% or more, according to several workflow studies.

Examples of data validation rules:

1. Restrict numbers

Set min or max values.

2. Create dropdown lists

Dropdowns prevent spelling errors or inconsistent labels.

Use:

Data → Data Validation → List

3. Validate date ranges

Ensure dates fall within acceptable boundaries.

4. Highlight invalid data

Validation alerts users immediately when errors occur.

Step 4: Handle Missing or Incomplete Data Like a Pro

Missing data is unavoidable but manageable. First, identify the gaps. Then decide how to handle them based on context.

How to find missing data:

• Use filters
• Use COUNTBLANK()
• Apply conditional formatting

Strategies for handling missing data:

1. Replace missing values

Use default values such as:

• “N/A”
• 0
• “Unknown”

This keeps datasets consistent.

2. Use statistical replacements

Use:

• =AVERAGE()
• =MEDIAN()

This approach works well for numeric datasets.

3. Delete incomplete rows

Only delete rows when the missing data makes them unusable.

4. Leave the data blank

Sometimes blanks are intentional and meaningful.

Choose the right approach based on your data’s purpose.

Step 5: Use Find & Replace for Bulk Corrections

Excel’s Find & Replace tool fixes recurring errors across large datasets within seconds. It’s perfect for cleanup tasks involving repeated patterns.

Common cleanup tasks using Find & Replace:

• Correcting misspellings
• Standardizing abbreviations
• Converting inconsistent naming
• Removing symbols or characters
• Updating outdated codes

Using wildcards for advanced cleanup:

• * — matches multiple characters
• ? — matches a single character

Example:

Find: *Inc
Replace: Inc.

This updates company names consistently.

Step 6: Split, Merge, or Restructure Data Using Advanced Functions

When data arrives from CRM systems or imports, values often get combined into a single column. Excel’s functions can restructure them with precision.

Useful functions include:

1. TEXTSPLIT()

Available in Excel 365.

Splits text based on delimiters.

2. LEFT(), RIGHT(), MID()

Extracts specific parts of text.

Examples:

• LEFT(A2, 5)
• RIGHT(A2, 4)
• MID(A2, 3, 2)

3. CONCAT() or TEXTJOIN()

Joins multiple values into a single cell.

4. Flash Fill

Excel can recognize patterns and fill data automatically.
Use Ctrl + E after typing a sample pattern.

Step 7: Automate Data Cleansing Using Excel’s Power Query

Power Query is a game changer for cleansing large datasets. It automates transformation tasks that normally take hours. You can clean data once and refresh it forever.

Many organizations reduce manual cleanup by up to 70% after adopting Power Query workflows.

Common Power Query transformations:

• Remove duplicates
• Split columns
• Unpivot data
• Replace values
• Remove errors
• Change data types
• Standardize text formats

Why Power Query is essential:

• It creates repeatable cleanup steps
• It handles huge datasets efficiently
• It eliminates human error
• It integrates with databases and online sources

Excel professionals consider Power Query a must-have for scalable data management.

Step 8: Run a Final Quality Check on Your Dataset

Before exporting or analyzing data, run a final quality assessment. This ensures your dataset is consistent, accurate, and ready for decision making.

Quality check tools include:

1. Summary functions

Use:

• COUNT()
• COUNTA()
• COUNTBLANK()
• UNIQUE()

These help confirm structure and completeness.

2. Conditional formatting

Use visual checks for:

• Outliers
• Duplicates
• Unexpected values
• Formatting errors

3. Data profiling in Power Query

This feature shows:

• Error counts
• Value distribution
• Column quality
• Valid vs invalid values

This final review ensures nothing critical gets overlooked.

Top 5 Pro-Level Tips to Maintain Clean Data in Excel

Once your data is clean, the next goal is to keep it clean. These professional tips reduce repeated cleanup work.