The Excel Cheat Sheet Every Beginner Needs for Data Cleaning

If you are learning data analysis, one of the first skills you should build is data cleaning.

Before dashboards, before charts, before insights — there is always messy data.

And messy data is everywhere.

Extra spaces. Duplicate rows. Inconsistent formatting. Missing values. Broken dates. Numbers stored as text.

This is the part of data work most beginners underestimate.

People often want to jump straight into visualization or machine learning, but in real projects, clean data comes first.

Photo by Lukas Blazek on Unsplash

That is why Excel is still one of the most useful tools for beginners.

It is simple, accessible, and powerful enough to handle most basic cleaning tasks without code.

And if you know the right shortcuts, formulas, and tools, Excel becomes one of the easiest places to clean messy data fast.

This is the Excel cheat sheet every beginner should keep.

1. TRIM()

Use it to remove extra spaces

One of the most common data cleaning issues is messy spacing.

For example:

• " Jakarta"
• "Jakarta "
• " Jakarta "

These values may look the same, but Excel reads them differently.

That creates problems when filtering, matching, or grouping.

Use:

=TRIM(A2)

This removes extra spaces before, after, and between words.

It is one of the first formulas you should use when cleaning text columns.

2. CLEAN()

Use it to remove hidden non-printable characters

Sometimes copied data contains hidden characters that break formulas or formatting.

These often come from:

• copied PDFs
• websites
• exported systems

Use:

=CLEAN(A2)

This removes invisible characters Excel cannot display properly.

It is especially useful when imported text looks normal but behaves strangely.

3. PROPER(), UPPER(), LOWER()

Use them to standardize text format

Text inconsistency is one of the most common cleaning problems.

Example:

• jakarta
• JAKARTA
• Jakarta

These should usually be standardized.

Use:

• =UPPER(A2) → JAKARTA
• =LOWER(A2) → jakarta
• =PROPER(A2) → Jakarta

This is useful for names, cities, categories, and labels.

4. Remove Duplicates

Use it to delete repeated rows

Duplicate rows can distort totals, counts, and analysis.

Excel makes this easy.

Go to:

Data → Remove Duplicates

Then select the relevant columns.

This is one of the fastest ways to clean repeated records in a dataset.

Always check duplicates before analysis.

5. Find & Replace

Use it to fix repeated formatting issues quickly

This is one of the most underrated cleaning tools in Excel.

Use:

Ctrl + H

It helps quickly replace:

• N/A → blank
• - → blank
• Male/Female → M/F
• inconsistent text labels

This is much faster than manual editing.

6. Text to Columns

Use it to split combined data

Sometimes multiple values are stored in one cell.

Example:

• Jakarta, Indonesia
• John Smith

Use:

Data → Text to Columns

This helps split data by:

• comma
• space
• custom delimiter

It is useful for separating names, locations, and imported fields.

7. Flash Fill

Use it to clean patterns automatically

Flash Fill is one of Excel’s smartest beginner-friendly tools.

It detects patterns and fills the rest automatically.

Example:

• split first name from full name
• combine city + country
• reformat phone numbers

Shortcut:

Ctrl + E

It saves a lot of manual work.

8. IF()

Use it to flag missing or messy values

A simple way to identify problems in data is by flagging them.

Use:

=IF(A2="", "Missing", "OK")

This helps detect:

• blank cells
• missing entries
• incomplete fields

Very useful for quick quality checks.

9. ISNUMBER() / ISTEXT()

Use them to detect wrong data types

Sometimes numbers are stored as text.

That causes sorting, formulas, and charts to break.

Use:

• =ISNUMBER(A2)
• =ISTEXT(A2)

This helps identify type issues quickly.

Very useful for imported CSV or copied data.

10. VALUE()

Use it to convert text into numbers

If numbers are stored as text, use:

=VALUE(A2)

This converts text-based numbers into real numeric values Excel can calculate.

Very useful for fixing imported sales, prices, and quantities.

11. Filter

Use it to scan data quickly

Filters are one of the fastest ways to inspect messy data.

Go to:

Data → Filter

Use filters to quickly spot:

• blanks
• unusual values
• inconsistent categories
• outliers

This is one of the simplest but most important cleaning habits.

12. Sort

Use it to detect anomalies

Sorting helps reveal hidden problems fast.

Sort columns A–Z or smallest–largest to spot:

• inconsistent spelling
• extreme values
• missing entries
• formatting issues

Simple, but powerful.

Excel is still one of the best tools to learn that skill.

You do not need advanced tools to start cleaning data well.

You just need the right habits — and the right cheat sheet.