Excel Data Cleaning: A Beginner's Guide

In the previous article, we learned what Excel is and why it is important for Data Analysts.

But before we can analyze data, there is one important step we need to understand:

Data Cleaning.

In the real world, data is rarely perfect. It can contain duplicate records, missing values, spelling mistakes, extra spaces, incorrect formats, and many other problems.

If we analyze dirty data, our results may be incorrect.

In this article, we will learn the basics of data cleaning in Excel and how beginners can prepare messy data for analysis.

What is Data Cleaning?

Data cleaning is the process of finding and fixing incorrect, incomplete, duplicate, or inconsistent data.

For example, imagine we have this customer dataset:

Name	City	Age	Sales
Sanka	Colombo	22	50000
Kasun	Colombo	24	45000
Nimal	Kandy		60000
Sanka	Colombo	22	50000
Amal	Galle	25	55000

At first glance, the data looks simple.

But we already have several problems:

colombo and Colombo are inconsistent.
Nimal's age is missing.
Sanka appears twice.
Amal has extra spaces.
Data may contain other hidden problems.

Before analyzing this dataset, we should clean it.

Why is Data Cleaning Important?

Data cleaning is important because bad data can produce bad results.

For example, imagine a company wants to know:

"How many customers are from Colombo?"

If some records contain:

Colombo
colombo
COLOMBO
Colombo

Excel may treat these as different values.

This can result in an incorrect analysis.

A simple rule for Data Analysts is:

Garbage In → Garbage Out

If the input data is incorrect, the final result can also be incorrect.

Common Data Problems in Excel

Some common problems you will encounter are:

Duplicate records
Missing values
Extra spaces
Inconsistent spelling
Incorrect data types
Incorrect date formats
Invalid values
Unnecessary columns
Inconsistent capitalization

Let's look at how we can fix them.

Removing Duplicate Data

Duplicate records occur when the same record appears more than once.

For example:

Customer ID	Name	City
101	Sanka	Colombo
102	Kasun	Kandy
101	Sanka	Colombo

Customer ID 101 appears twice.

If we calculate the number of customers without removing the duplicate, our result may be incorrect.

How to Remove Duplicates

In Excel:

Step 1: Select your dataset.

Step 2: Go to:

Data → Remove Duplicates

Step 3: Select the columns you want Excel to check.

Step 4: Click OK.

Excel will identify duplicate records and remove the duplicates according to your selection.

Important

Always keep a backup of your original dataset before deleting duplicates.

A good Data Analyst should never destroy the original data unnecessarily.

Handling Missing Values

Missing values are another common problem.

For example:

Name	Age	City
Sanka	22	Colombo
Kasun	24	Kandy
Nimal		Galle
Amal	25	Colombo

Nimal's age is missing.

Before analyzing the data, we need to decide what to do.

Depending on the situation, we might:

Leave it blank
Replace it with a suitable value
Calculate an estimated value
Remove the record

The correct method depends on the dataset and the purpose of the analysis.

Important

Never randomly replace missing values.

You should understand why the value is missing before deciding how to handle it.

Removing Extra Spaces

Extra spaces are easy to overlook.

For example:

Sanka
Sanka
Sanka 
Sanka

Although they look similar, the spaces can cause problems when comparing or analyzing data.

Excel provides the TRIM function to remove unnecessary spaces.

For example:

=TRIM(A2)

If A2 contains:

  Sanka

the result will be:

Sanka

This is one of the most useful functions for cleaning text data.

Fixing Inconsistent Capitalization

You may also find different capitalization styles.

For example:

colombo
Colombo
COLOMBO

These values represent the same city, but they are written differently.

Excel provides several text functions to standardize capitalization.

UPPER

Converts text to uppercase.

=UPPER(A2)

Example:

colombo → COLOMBO

LOWER

Converts text to lowercase.

=LOWER(A2)

Example:

COLOMBO → colombo

PROPER

Converts text into proper capitalization.

=PROPER(A2)

Example:

colombo → Colombo

For many datasets, PROPER() can be useful when standardizing names and locations.

Fixing Incorrect Data Formats

Data can also have incorrect formats.

For example:

Date
2026/01/10
10-01-2026
January 10, 2026

These values may represent the same date, but they are displayed differently.

Excel allows you to format dates consistently.

Select the column and use:

Home → Number Format → Date

You can also use:

Ctrl + 1

to open the Format Cells window.

Then choose the appropriate date format.

Finding and Replacing Incorrect Values

Sometimes a dataset contains spelling mistakes.

For example:

Colombo
Colombo
Colmobo
Colombo

Colmobo is probably a spelling mistake.

Excel's Find & Replace feature can help.

Use:

Ctrl + H

Then enter:

Find what:

Colmobo

Replace with:

Colombo

Click Replace All if you are certain that every occurrence should be changed.

Using Filters to Find Problems

Filters are extremely useful for data cleaning.

Suppose you have thousands of records.

You can use:

Data → Filter

Then filter the dataset to identify:

Blank values
Unexpected categories
Incorrect values
Specific dates
Specific cities
Unusual numbers

For example, if the Age column contains:

filtering the column can help you quickly identify suspicious values.

A person probably cannot have an age of -5 or 200.

These values should be investigated.

Checking Data Validation

Data Validation helps prevent incorrect data from being entered.

For example, suppose you have a column called Gender.

Instead of allowing users to enter anything, you can create a dropdown containing:

Male
Female
Other

To create a dropdown:

Data → Data Validation → Allow: List

Then specify the allowed values.

This is especially useful when creating spreadsheets that will be updated regularly.

Removing Unnecessary Columns

Sometimes datasets contain columns that are not required for your analysis.

For example:

Customer ID	Name	Age	City	Internal Code	Sales

If Internal Code is not needed for your analysis, you may remove it from the working dataset.

However, before deleting anything, make sure the column is genuinely unnecessary.

A better practice is often to keep the original dataset unchanged and create a cleaned working copy.

Checking for Incorrect Values

Data cleaning is not only about formatting.

You should also check whether the values make sense.

For example:

Product	Quantity
Laptop	5
Phone	10
Tablet	-3
Monitor	7

A quantity of -3 is suspicious.

You need to investigate why it exists.

Possible reasons could include:

Data entry error
Refund transaction
Incorrect import
Business-specific meaning

Never assume that an unusual value is automatically wrong.

Understand the data first.

A Simple Excel Data Cleaning Workflow

When you receive a new dataset, you can follow this workflow:

Step 1: Understand the dataset

↓

Step 2: Make a copy of the original data

↓

Step 3: Check for duplicates

↓

Step 4: Check for missing values

↓

Step 5: Remove unnecessary spaces

↓

Step 6: Standardize text

↓

Step 7: Fix data formats

↓

Step 8: Check for incorrect or unusual values

↓

Step 9: Validate the cleaned dataset

↓

Step 10: Start your analysis

This simple workflow can help you build good data-cleaning habits.

Practical Example

Let's take a small dataset.

Before Cleaning

Customer	City	Age	Sales
Sanka	Colombo	22	50000
Kasun	colombo	24	45000
Nimal	Kandy		60000
Sanka	Colombo	22	50000
Amal	Galle	25	55000

Problems

We can identify:

Problem 1: Sanka is duplicated.

Problem 2: colombo should be standardized.

Problem 3: Nimal's age is missing.

Problem 4: Amal has extra spaces.

After Cleaning

Customer	City	Age	Sales
Sanka	Colombo	22	50000
Kasun	Colombo	24	45000
Nimal	Kandy		60000
Amal	Galle	25	55000

Now the dataset is much cleaner and easier to analyze.

Notice that we did not simply invent Nimal's age.

That missing value should be handled based on the context of the real dataset.

Important Excel Functions for Data Cleaning

Here are some functions you should learn:

Function	Purpose
`TRIM()`	Removes unnecessary spaces
`CLEAN()`	Removes non-printing characters
`UPPER()`	Converts text to uppercase
`LOWER()`	Converts text to lowercase
`PROPER()`	Capitalizes words
`SUBSTITUTE()`	Replaces specific text
`LEFT()`	Extracts characters from the left
`RIGHT()`	Extracts characters from the right
`MID()`	Extracts characters from the middle
`LEN()`	Counts characters
`FIND()`	Finds text position
`SEARCH()`	Searches for text

You don't need to memorize all of these immediately.

Start by understanding what each function does and practice using them with real data.

Common Mistakes Beginners Should Avoid

Mistake 1: Editing the original dataset

Always keep an untouched copy of the original data.

Mistake 2: Deleting missing values immediately

A blank value does not always mean the entire row should be deleted.

Mistake 3: Removing unusual values without investigation

An unusual value might represent a legitimate business situation.

Mistake 4: Cleaning data manually

For small datasets, manual cleaning may be acceptable.

But for large datasets, learn Excel functions, Power Query, and other tools to make the process more efficient.

Mistake 5: Not documenting your cleaning process

As your projects become more advanced, you should keep track of what you changed and why.

Final Thoughts

Data cleaning is one of the most important skills for a Data Analyst.

Before creating charts, dashboards, or reports, you need to make sure that your data is reliable and consistent.

Excel provides many tools that can help you clean data, including:

Remove Duplicates
Filters
Find & Replace
Data Validation
Text Functions
Formatting
Power Query

The more datasets you practice with, the better you will become at identifying data problems.

Remember:

Clean data → Better analysis → Better insights → Better decisions.

This is only the beginning of your Excel journey.

In the next article, we will explore some of the most important Excel functions every beginner Data Analyst should know, with practical examples.

Key Takeaways

Data cleaning means finding and fixing problems in data.
Duplicate records should be identified and handled carefully.
Missing values need to be investigated before deciding what to do.
TRIM() can remove unnecessary spaces.
UPPER(), LOWER(), and PROPER() help standardize text.
Filters help identify unusual or missing values.
Find & Replace can quickly fix common data-entry mistakes.
Data Validation helps prevent incorrect future entries.
Always keep an original copy of your dataset.
Good data cleaning leads to more reliable analysis.

Keep Learning

If you are following my journey to become a Data Analyst, this is an important skill to practice.

Don't just read about data cleaning.

Download a messy dataset, open Excel, and clean it yourself.

That's how real learning happens.

Thanks for reading Analytics With Sanka!

Follow along for more articles about Excel, SQL, Power BI, Python, Data Analysis, and my journey toward becoming a Data Analyst.