[ ] Extra spaces (leading, trailing, multiple)

[ ] Inconsistent capitalization

[ ] Text stored as numbers (or vice versa)

[ ] Dates not recognized as dates

[ ] Empty cells where there shouldn't be

[ ] Invalid entries and typos

[ ] Merged cells (the enemy of analysis)

## Removing Duplicates

### Quick Method: Remove Duplicates Tool

1. Select your data range (including headers)

2. Go to Data > Remove Duplicates 3. Check the columns that define "duplicate" 4. Click OK

Excel shows how many duplicates were removed and how many unique values remain.

### Formula Method: Identify Before Deleting

Sometimes you want to see duplicates before removing them. Use COUNTIF:

=COUNTIF($A$2:$A$1000, A2) > 1

This returns TRUE for duplicate values. Filter to show only TRUE, then decide what to delete.

## Fixing Text Issues

### Remove Extra Spaces: TRIM

The TRIM function removes:

Leading spaces

Trailing spaces

Multiple spaces between words (leaving single spaces)

=TRIM(A2)

### Standardize Capitalization

=UPPER(A2)      → JOHN SMITH
=LOWER(A2)      → john smith
=PROPER(A2)     → John Smith

### Remove Non-Printable Characters: CLEAN

=CLEAN(A2)

Removes ASCII characters 0-31 (non-printable characters that sometimes sneak in from imports).

### The Ultimate Cleanup Combo

Combine TRIM and CLEAN:

=TRIM(CLEAN(A2))

## Extracting and Splitting Data

### Text to Columns

When names are "LastName, FirstName" and you need separate columns:

1. Select the column 2. Go to Data > Text to Columns 3. Choose Delimited 4. Select comma as delimiter 5. Finish

### Formula Extraction

Extract first name from "FirstName LastName":

=LEFT(A2, FIND(" ", A2) - 1)

Extract last name:

=RIGHT(A2, LEN(A2) - FIND(" ", A2))

With TEXTSPLIT (Excel 365):

=TEXTSPLIT(A2, " ")

## Fixing Date Problems

### Dates Stored as Text

When dates look like dates but Excel treats them as text:

Method 1: Multiply by 1

=A2 * 1

Method 2: DATEVALUE

=DATEVALUE(A2)

Method 3: Text to Columns 1. Select the date column 2. Data > Text to Columns 3. Next > Next 4. Choose Date format (MDY, DMY, etc.) 5. Finish

### Inconsistent Date Formats

When you have "1/5/2024" and "January 5, 2024" mixed:

Create a helper column with:

=TEXT(DATEVALUE(A2), "YYYY-MM-DD")

## Find and Replace Magic

Ctrl + H opens Find and Replace. It's more powerful than most realize.

### Remove All Spaces

Find: (one space) Replace: (empty)

### Standardize Abbreviations

Find: Street Replace: St

Find: Avenue Replace: Ave

### Remove Line Breaks

Find: Ctrl + J (inserts line break character) Replace: (space or empty)

### Use Wildcards

Enable "Use wildcards" option:

- * matches any sequence of characters

? matches any single character

~ escapes special characters

Find: Mr.*

Replace: (empty) Removes "Mr." followed by anything.

## Handling Blank Cells

### Find and Select Blanks

1. Select your data range 2. Press F5 (or Ctrl+G) > Special 3. Choose "Blanks" 4. All blank cells are selected

Now you can:

Delete rows: Right-click > Delete > Entire Row

Fill with value: Type a value and press Ctrl + Enter

Fill from above: Press Ctrl + D

### Fill Blanks with Value Above

1. Select data range

2. Go to (F5) > Special > Blanks 3. Type = and click the cell above (or press Up Arrow) 4. Press Ctrl + Enter

## Number Issues

### Text to Numbers

When numbers have the green triangle warning: 1. Select the range 2. Click the warning icon 3. Choose "Convert to Number"

Or use formula:

=VALUE(A2)

### Remove Number Formatting Symbols

Clean currency, percentages, etc.:

=VALUE(SUBSTITUTE(SUBSTITUTE(A2, "$", ""), ",", ""))

## Power Query: The Professional Choice

For complex or recurring cleaning tasks, Power Query is the answer.

### Access Power Query

1. Select your data 2. Go to Data > From Table/Range 3. Power Query Editor opens

### Common Power Query Transformations

- Remove duplicates: Right-click column header

Split column: Right-click > Split Column

Replace values: Right-click > Replace Values

Change type: Click column type icon

Trim/Clean: Add Column > Format > Trim/Clean

### Why Power Query?

1. Repeatable: Steps are recorded and can be applied to new data

2. Non-destructive: Original data remains unchanged 3. Powerful: Handles transformations formulas can't easily do 4. Connected: Can pull from multiple sources

## Data Validation: Prevent Future Messes

After cleaning, prevent new errors:

### Dropdown Lists

1. Select target cells 2. Data > Data Validation 3. Allow: List 4. Source: Your list of valid options

### Number Ranges

1. Data > Data Validation 2. Allow: Whole number (or Decimal) 3. Set minimum and maximum

### Date Ranges

Restrict to valid date ranges to prevent typos.

## Automation with Macros

If you clean similar data repeatedly, record a macro:

1. Go to View > Macros > Record Macro 2. Perform your cleaning steps 3. Stop recording 4. Run the macro on new data

## Final Steps

After cleaning:

1. Spot check: Manually review a sample 2. Validate: Use COUNTBLANK, COUNTIF to verify 3. Document: Note what you did (for next time) 4. Backup: Keep original data in a separate sheet

## Conclusion

Data cleaning is where real analysis begins. A well-cleaned dataset reveals insights that messy data hides. Master these techniques, and you'll spend less time fighting your data and more time finding answers in it.

Start with the basics: TRIM, Remove Duplicates, Find & Replace. Graduate to Power Query for complex transformations. Your future self—and anyone else who uses your spreadsheets—will thank you.

How to Clean Messy Data in Excel

Found this helpful?