How to Perform Data Cleaning in Excel: Step-by-Step Guide

If you need to clean data in Excel but don't know where to start, you need a systematic approach. 74% of Excel users perform data cleaning regularly but lack a structured process.

By the end of this guide, you'll know how to perform data cleaning in Excel—using built-in features, functions, and tools to clean, standardize, and prepare your data efficiently.

Quick Summary

Use Excel functions - TRIM, CLEAN, SUBSTITUTE for text cleaning
Remove duplicates - Data > Remove Duplicates feature
Standardize formats - Format Cells and Text to Columns
Handle missing values - Find & Replace and conditional formatting

Common Data Cleaning Tasks in Excel

Remove duplicates - Eliminate duplicate rows
Trim spaces - Remove extra whitespace
Standardize text - Fix case inconsistencies
Clean special characters - Remove unwanted characters
Convert data types - Text to numbers, dates
Handle missing values - Find and fill blanks
Standardize formats - Dates, numbers, text
Split columns - Separate combined data
Merge columns - Combine data
Validate data - Check for errors

Step-by-Step: Perform Data Cleaning in Excel

Step 1: Remove Duplicates

Eliminate duplicate rows from your dataset.

Identify Duplicates

Check for duplicates:

Select data range (including headers)
Data > Remove Duplicates
Preview shows duplicate count
Click Cancel to see first

Or use conditional formatting:

Select data range
Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values
Duplicates highlighted in color

Remove Duplicates

Method: Remove Duplicates Feature

Select data range (including headers)
Data > Remove Duplicates
Choose columns to check:
- Check all columns = exact duplicates
- Check key columns = duplicates by identifier
Click OK
Excel removes duplicates and shows count

Example:

Check "Email" column for duplicate customers
Check all columns for exact duplicate rows

Step 2: Clean Text Data

Remove extra spaces and standardize text.

Remove Extra Spaces

Use TRIM function:

=TRIM(A2)

Removes leading, trailing, and extra spaces.

Apply to column:

Insert new column next to data
Enter formula: =TRIM(A2)
Copy formula down entire column
Copy formula column
Paste Special > Values over original column
Delete formula column

Remove Non-Printable Characters

Use CLEAN function:

=CLEAN(A2)

Removes non-printable characters (line breaks, tabs, etc.).

Combine with TRIM:

=TRIM(CLEAN(A2))

Removes both spaces and non-printable characters.

Standardize Text Case

Convert to lowercase:

=LOWER(A2)

Convert to uppercase:

=UPPER(A2)

Convert to title case:

=PROPER(A2)

Apply:

Insert helper column
Enter formula
Copy down
Paste as values over original
Delete helper column

Step 3: Handle Missing Values

Find and handle blank cells and missing data.

Find Missing Values

Method 1: Go To Special

Select data range
Press F5 (Go To)
Click Special
Choose Blanks
Click OK
All blanks selected

Method 2: Filter

Select header row
Data > Filter
Click dropdown on column
Uncheck all except (Blanks)
Blanks filtered

Fill Missing Values

Fill with value:

Select blanks (using Go To Special)
Type value (e.g., "N/A" or 0)
Press Ctrl+Enter
All blanks filled

Fill with formula:

Select blank cell
Enter formula referencing above cell
Copy formula to all blanks

Fill with average:

Calculate average in separate cell
Select blanks
Paste average value
Press Ctrl+Enter

Step 4: Convert Data Types

Convert text to numbers, dates, etc.

Convert Text to Numbers

Method 1: Text to Columns

Select text number column
Data > Text to Columns
Choose General or Number
Click Finish
Text converted to numbers

Method 2: VALUE function

=VALUE(A2)

Converts text to number.

Method 3: Multiply by 1

=A2*1

Forces conversion to number.

Convert Text to Dates

Method 1: Text to Columns

Select date column
Data > Text to Columns
Choose Date format
Select format (MDY, DMY, YMD)
Click Finish
Dates converted

Method 2: DATEVALUE function

=DATEVALUE(A2)

Converts text date to Excel date.

Then format:

Select date column
Right-click > Format Cells > Date
Choose format
Click OK

Step 5: Standardize Date Formats

Ensure all dates use consistent format.

Detect Date Issues

Check date formats:

Select date column
Look for inconsistencies:
- Some as dates (numbers)
- Some as text
- Different formats

Standardize Dates

Convert all to dates:

Use Text to Columns (if text dates)
Format consistently:
- Right-click > Format Cells > Date
- Choose format (e.g., YYYY-MM-DD)
- Click OK

Apply to all:

Select entire date column
Apply format
All dates display consistently

Step 6: Standardize Number Formats

Apply consistent number formatting.

Format Currency

Apply currency format:

Select number column
Right-click > Format Cells
Choose Currency
Select symbol ($, €, £)
Set decimal places (usually 2)
Click OK

Format Percentages

Apply percentage format:

Select percentage column
Right-click > Format Cells
Choose Percentage
Set decimal places
Click OK

Note: Excel multiplies by 100, so 0.15 becomes 15%

Format Numbers

Apply number format:

Select number column
Right-click > Format Cells
Choose Number
Set decimal places
Check Use 1000 Separator if needed
Click OK

Step 7: Remove Special Characters

Clean unwanted characters from data.

Remove Specific Characters

Use SUBSTITUTE function:

=SUBSTITUTE(A2, "#", "")

Removes # character.

Remove multiple:

=SUBSTITUTE(SUBSTITUTE(A2, "#", ""), "@", "")

Removes both # and @.

Remove Line Breaks

Remove line breaks:

=SUBSTITUTE(SUBSTITUTE(A2, CHAR(10), " "), CHAR(13), " ")

Replaces line breaks with spaces.

Find & Replace

Remove characters:

Press Ctrl+H (Find & Replace)
Find: Enter character to remove
Replace: Leave blank
Click Replace All
Characters removed

Step 8: Split and Combine Columns

Separate or merge column data.

Split Columns

Text to Columns:

Select column to split
Data > Text to Columns
Choose delimiter:
- Delimited - Comma, space, etc.
- Fixed width - Specific positions
Set delimiter or positions
Preview split
Click Finish
Data split into columns

Example: Split "John Doe" into "John" and "Doe"

Combine Columns

Use CONCATENATE or &:

=A2&" "&B2

Combines with space.

=CONCATENATE(A2, " ", B2)

Same result.

Apply:

Insert new column
Enter formula
Copy down
Paste as values
Delete original columns

Step 9: Validate Data

Check data for errors and inconsistencies.

Data Validation Rules

Create validation:

Select cells
Data > Data Validation
Choose criteria:
- List - Dropdown choices
- Whole number - Numeric range
- Date - Date range
- Text length - Character limit
Set rules
Click OK

Find Errors

Check for errors:

Select data range
Home > Find & Select > Go To Special
Choose Formulas
Check Errors
Click OK
All errors selected

Conditional Formatting for Validation

Highlight errors:

Select data range
Home > Conditional Formatting > Highlight Cells Rules
Choose rule (e.g., "Duplicate Values", "Text that Contains")
Set format
Click OK
Issues highlighted

Step 10: Final Review and Save

Verify cleaning and save cleaned data.

Review Changes

Check cleaned data:

Duplicates removed
Text cleaned (spaces, case)
Missing values handled
Data types correct
Formats standardized
Special characters removed
Data validated
No errors

Save Cleaned Data

Save as new file:

File > Save As
Choose location
Enter filename (e.g., "data_cleaned.xlsx")
Click Save
Original file preserved

Or save as CSV:

File > Save As
File type: CSV (Comma delimited)
Save
Cleaned CSV ready

Real Example: Cleaning Data in Excel

Before (Messy Data):

Dataset: 1,000 customer records

Issues:

50 duplicate rows
Inconsistent text case
Extra spaces
Mixed date formats
Numbers as text
Missing email addresses

Cleaning Process:

Step 1: Remove duplicates

Removed 50 duplicate rows
950 records remaining

Step 2: Clean text

Applied TRIM to all text columns
Standardized to Title Case
Removed extra spaces

Step 3: Handle missing values

Filled missing emails with "unknown@email.com"
Documented 5% missing rate

Step 4: Convert data types

Converted text numbers to numeric
Converted text dates to date format

Step 5: Standardize formats

Dates: YYYY-MM-DD
Numbers: 2 decimal places
Text: Title Case

Result:

Clean dataset: 950 records
All formats standardized
Ready for analysis

Excel Cleaning Checklist

Use this checklist when cleaning data in Excel:

Removed duplicates
Cleaned text (TRIM, CLEAN, case)
Handled missing values
Converted data types
Standardized date formats
Standardized number formats
Removed special characters
Split/combined columns (if needed)
Validated data
Reviewed cleaned data
Saved cleaned file

Mini Automation Using RowTidy

You can automate Excel data cleaning using RowTidy, then use Excel for review and validation.

The Problem:
Cleaning data in Excel manually is time-consuming:

Applying functions to each column
Removing duplicates
Standardizing formats
Handling missing values

The Solution:
Use RowTidy for automated cleaning, Excel for review:

Upload file to RowTidy - Drag and drop
AI cleans data - Removes duplicates, standardizes formats
Download cleaned file - Get Excel file
Review in Excel - Validate and make final adjustments

Workflow:

RowTidy: Automated cleaning (fast)
Excel: Review and validation (control)

Time saved: 2-4 hours manual Excel cleaning → 5 minutes RowTidy + 30 minutes Excel review

Use RowTidy for initial cleaning, then Excel for final review and validation. Try RowTidy's automated cleaning →

FAQ

1. How do I clean data in Excel?

Use Excel functions (TRIM, CLEAN), Remove Duplicates feature, Text to Columns, Find & Replace, and formatting tools to clean data systematically.

2. What Excel functions are best for data cleaning?

TRIM (spaces), CLEAN (non-printable), SUBSTITUTE (characters), VALUE (text to number), DATEVALUE (text to date), UPPER/LOWER/PROPER (case).

3. How do I remove duplicates in Excel?

Select data range, Data > Remove Duplicates, choose columns to check, click OK. Excel removes duplicates and shows count.

4. How do I standardize text in Excel?

Use TRIM to remove spaces, CLEAN for non-printable characters, UPPER/LOWER/PROPER for case. Apply formulas, then paste as values.

5. How do I convert text to numbers in Excel?

Use Text to Columns (Data > Text to Columns, choose Number), or VALUE function, or multiply by 1. Text to Columns is easiest.

6. How do I handle missing values in Excel?

Use Go To Special (F5 > Special > Blanks) to find, then fill with values, formulas, or averages. Or use Find & Replace.

7. Should I use Excel or RowTidy for data cleaning?

Use RowTidy for speed and automation, Excel for review and validation. Best workflow: RowTidy cleans, Excel reviews.

8. How long does Excel data cleaning take?

Depends on dataset size: small (1000 rows) = 1-2 hours, medium (10,000 rows) = 2-4 hours, large (100,000 rows) = 4-8 hours. RowTidy cleans in minutes.

9. Can I automate Excel data cleaning?

Partially. Use Power Query for reusable workflows, or VBA for automation. RowTidy provides full automation without coding.

10. What's the best Excel data cleaning workflow?

(1) Remove duplicates, (2) Clean text, (3) Handle missing values, (4) Convert types, (5) Standardize formats, (6) Validate, (7) Review, (8) Save. Or use RowTidy first, then Excel for review.

Related Guides

Conclusion

Performing data cleaning in Excel requires using built-in features (Remove Duplicates, Text to Columns), functions (TRIM, CLEAN, VALUE), and formatting tools systematically. Follow a structured process: remove duplicates, clean text, handle missing values, convert types, standardize formats, validate, and review. Use RowTidy for automated cleaning, then Excel for final review and validation.

Try RowTidy — automate data cleaning, then use Excel for review and validation to ensure data quality.