How to Clean Messy Data in Excel: Step-by-Step Guide

Why Data Cleaning Matters

Data analysts spend 60–80% of their time on data preparation and cleaning. That is not a complaint — it is reality. Raw data from CRMs, ERPs, survey tools and exported spreadsheets rarely arrives in a form that is ready for analysis. Duplicate records inflate counts. Text-formatted numbers break SUM formulas. Inconsistent category names like "north", "NORTH" and "North " produce three separate groups in a PivotTable instead of one.

Every error in your source data is an error in your analysis. No formula or chart can correct bad source data — it can only hide it temporarily. This guide walks you through 13 steps that transform a realistic messy dataset into one that is ready for analysis.

What Messy Data Looks Like

Here is a sample of the kind of messy customer sales data you will encounter in real work:

Problems visible in this small sample: duplicate row (OrderID 1001 appears twice), leading space in "sreemathy sampath", inconsistent region capitalisation (north, SOUTH, East, West ), extra spaces ("Priya S"), wrong date formats (four different formats across six rows), number stored as text ('9200), negative Amount (-500), and a typo ("Nrth" instead of "North"). A real dataset of 5,000 rows will have all of these problems and more.

Step 1 Backup the Raw Data

Before touching anything: right-click the sheet tab, select Move or Copy, check "Create a copy", and put it in the same workbook as "RAW_DATA – Do Not Edit". Lock the sheet if possible (Review → Protect Sheet).

This is not optional. Data cleaning steps are irreversible. You will make mistakes. You need a reference copy to check against or revert to.

Step 2 Remove Duplicates

Go to Data → Remove Duplicates. Select the columns that together define a unique record. For our sample, OrderID alone is the unique key — select only OrderID. Click OK. Excel tells you how many duplicates were removed and how many unique rows remain.

Be careful with "select all columns" in Remove Duplicates — two rows that are truly duplicate are identical across all columns. If even one column differs (e.g., a timestamp with milliseconds), Excel will not treat them as duplicates even if they represent the same business event.

After removing duplicates, use =COUNTIF($A$2:$A$1000,A2) on a helper column to verify — any value greater than 1 means that OrderID still appears more than once.

Step 3 Find and Fix Blank Cells

Select your data range. Press Ctrl+G → Special → Blanks → OK. Excel selects all blank cells. Now decide: fill with a default value ("Unknown", "N/A"), delete those rows, or flag them for follow-up.

To fill all blanks with "Unknown": with all blanks selected, type "Unknown" and press Ctrl+Enter — Excel fills all selected cells simultaneously.

Use =COUNTBLANK(A2:A1000) to check how many blanks exist in a column before and after cleaning.

Step 4 Remove Extra Spaces — TRIM and CLEAN

Extra spaces are invisible and break lookup formulas and PivotTable grouping. Apply TRIM to every text column.

In a helper column next to CustomerName:

=TRIM(CLEAN(B2))

CLEAN removes non-printable characters (common in data pasted from PDFs or web pages). TRIM removes leading, trailing and double-internal spaces.

After checking the helper column looks correct: copy it, then Paste Special (Ctrl+Shift+V) → Values Only over the original column. Delete the helper column.

Apply the same process to every text column: Region, Status, CustomerName.

Step 5 Fix Capitalisation

Use PROPER for names and most text: =PROPER(B2) → "Sreemathy Sampath"

Use UPPER for codes and categories where all-caps is the standard: =UPPER(F2) → "COMPLETED"

Use LOWER when the standard is lowercase: =LOWER(F2) → "completed"

Decide on a standard and apply it consistently across the dataset.

Step 6 Standardise Category Values

After TRIM and PROPER, check your Region column for remaining inconsistencies. In our sample, "Nrth" is a typo that TRIM and PROPER cannot fix — it needs a Find and Replace.

Press Ctrl+H. In "Find what": Nrth. In "Replace with": North. Click Replace All.

For more complex standardisation, create a mapping table on a separate sheet:

Then use XLOOKUP or VLOOKUP against this mapping table to replace messy values with standard ones in a helper column.

Step 7 Fix Numbers Stored as Text

A number stored as text sits left-aligned in its cell (numbers are right-aligned by default) and has a small green triangle in the corner. SUM on a column of text-numbers returns 0.

In our sample, Amount has '9200 — the apostrophe forces Excel to treat it as text.

Fix options:

• In a helper column: =VALUE(E2)
• Use Data → Text to Columns → Finish (for a whole column at once)
• Select the column, click the green triangle warning, choose "Convert to Number"
• Multiply by 1 in a helper column: =E2*1

After converting, verify with =ISNUMBER(E2) — should return TRUE for all cells.

Step 8 Fix Dates Stored as Text

Our sample has four different date formats across six rows. This is extremely common when data comes from multiple sources or is entered manually.

Dates stored as text are left-aligned. Proper Excel dates are right-aligned and have a numeric value underneath (Excel stores dates as sequential numbers — 45900 is September 15, 2026).

Fixes:

• Consistent text format like "15-09-2026": use =DATEVALUE("15-09-2026") or Data → Text to Columns → Date → DMY
• Mixed formats: standardise with Find & Replace to get all dates into one format, then convert
• For Power Query users: use Transform → Data Type → Date — Power Query detects most formats automatically

After conversion, format the column as Date (Ctrl+1 → Number → Date) and verify all cells show dates, not numbers.

Step 9 Split Combined Columns

Sometimes a single column contains data that belongs in two columns — "FirstName LastName" in one cell, or "City, State" combined.

Text to Columns: Select the column → Data → Text to Columns → Delimited → choose the separator (space, comma) → Finish. This splits the column in place.

Flash Fill: Type the desired result in the first cell of a new column (e.g., "Sreemathy" in a column next to "Sreemathy Sampath"). Press Ctrl+E. Excel detects the pattern and fills the rest of the column.

Formula approach: =LEFT(B2,FIND(" ",B2)-1) for first name, =MID(B2,FIND(" ",B2)+1,100) for last name.

Step 10 Remove Unnecessary Columns and Rows

Delete columns you will not use in the analysis. Empty columns in the middle of a dataset confuse Excel's table detection and slow down operations.

Delete rows that are subtotals, headers repeated mid-table, or notes that were embedded in the data (a common problem with data exported from accounting software).

Check for and remove any completely blank rows with Ctrl+G → Special → Blanks → select entire rows → delete.

Step 11 Check for Outliers and Impossible Values

Use MIN and MAX to check the range of numeric columns:

=MIN(E2:E1000)   → Minimum Amount
=MAX(E2:E1000)   → Maximum Amount

In our sample, Amount = -500 is suspicious for a sales amount. Is a negative order valid (return/refund)? Or is it a data entry error? Flag these for review:

=IF(E2<0,"CHECK: Negative Amount","OK")

For dates, check that all order dates fall within the expected range. Dates in the future or before the business was founded are impossible values.

Step 12 Add Data Validation

Data validation prevents bad data from entering the sheet in the future. Select the Region column → Data → Data Validation → Allow: List → Source: North,South,East,West.

Now if someone types "NRTH" or "Nrth", Excel shows an error. The fix is upstream — at the point of entry — rather than in the cleaning step.

Add validation to: Status (dropdown: Completed, Pending, Cancelled), Region (dropdown), OrderDate (Date range: between 2020-01-01 and today), Amount (Whole number: greater than or equal to 0).

Step 13 Document Your Changes

Create a "Cleaning Log" sheet. Record: what you found, what you changed, how many rows were affected, and the date. This is professional practice — it makes your work auditable and reproducible.

Trainer's Practical Advice

Common Mistakes

Frequently Asked Questions