Data Cleaning 101: How to Fix Your Messy Spreadsheet

Bad data leads to bad decisions. It's that simple.

If your spreadsheet has duplicate entries, missing values, or inconsistent formatting, any analysis you do will be unreliable. Garbage in, garbage out.

Let's fix that.

Why Data Cleaning Matters

Imagine analyzing sales data where:

• Some dates are "12/15/2025" and others are "December 15, 2025"
• Customer names appear as "John Smith", "john smith", and "JOHN SMITH"
• Some rows are duplicated
• Revenue sometimes includes currency symbols, sometimes doesn't

Any insights from this data would be misleading at best, completely wrong at worst.

The 7 Most Common Data Problems

1. Duplicate Rows

The same record appears multiple times.

How to spot: Sort your data and look for identical rows, or use a duplicate detection formula.

Excel fix:

1. Select your data
2. Data → Remove Duplicates
3. Choose which columns to check
4. Click OK

Google Sheets fix:

1. Select your data
2. Data → Data cleanup → Remove duplicates

2. Missing Values

Empty cells where there should be data.

How to spot: Use conditional formatting to highlight blank cells, or filter for blanks.

Options:

• Delete the row - If it's a small percentage
• Fill with average/median - For numeric data
• Fill with "Unknown" - For categorical data
• Leave blank - If your analysis tool handles it

Excel formula to count blanks:

=COUNTBLANK(A:A)

3. Inconsistent Text Formatting

"New York", "new york", "NEW YORK", "NY" all meaning the same thing.

How to fix:

Capitalize consistently:

=PROPER(A1)  // Capitalizes first letter of each word
=UPPER(A1)   // ALL CAPS
=LOWER(A1)   // all lowercase

Trim extra spaces:

=TRIM(A1)

4. Inconsistent Date Formats

Dates stored as text, different formats mixed together.

How to spot: Sort a date column. If it doesn't sort chronologically, you have a problem.

How to fix:

1. Create a new column
2. Use DATEVALUE() to convert text to dates
3. Format the new column as dates
4. Copy → Paste Values → Delete old column

Formula:

=DATEVALUE(A1)

5. Numbers Stored as Text

Numbers with currency symbols, commas, or formatted as text.

How to spot: Numbers align left instead of right. SUM() returns 0.

How to fix:

Remove currency/commas:

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

Quick fix in Excel:

1. Select the column
2. Data → Text to Columns
3. Click Finish

6. Outliers

Values that are clearly wrong or extremely unusual.

Examples:

• Age: 250 years old
• Revenue: -$50,000 (negative when it shouldn't be)
• Quantity: 10,000,000 (typo?)

How to spot:

• Sort data ascending/descending and check extremes
• Use conditional formatting for values outside expected range
• Create a box plot

How to handle:

• Verify if it's real or a typo
• Correct if it's wrong
• Remove or flag if it's corrupting analysis

7. Inconsistent Categories

"Electronics", "electronics", "Electronic", "Electornics" (typo)

How to find:

1. Create a pivot table on that column
2. See all unique values
3. Spot variations

How to fix: Use Find & Replace (Ctrl+H) to standardize:

• Find: "electronics" → Replace: "Electronics"
• Find: "Electornics" → Replace: "Electronics"

A Simple Data Cleaning Checklist

Before any analysis, run through this:

• [ ] Remove duplicates
• [ ] Check for blanks - Decide how to handle each
• [ ] Standardize text - Consistent capitalization
• [ ] Fix dates - All in same format, sorted correctly
• [ ] Fix numbers - No text, no currency symbols in data
• [ ] Check outliers - Min/max values make sense
• [ ] Standardize categories - No typos or variations

Time-Saving Tips

Tip 1: Clean Once, Use Template

If you regularly get messy data in the same format:

1. Create a cleaning template
2. Document your steps
3. Reuse for each new file

Tip 2: Use Find & Replace Aggressively

Ctrl+H is your friend. Common replacements:

• Double spaces → Single space
• "N/A" → (blank)
• Various misspellings → Correct spelling

Tip 3: Work on a Copy

Always keep your original data intact. Clean a copy so you can start over if needed.

Tip 4: Use AI Tools

Modern AI tools can:

• Detect anomalies automatically
• Suggest data quality fixes
• Standardize formats in seconds

What takes hours manually might take minutes with the right tool.

When Good Enough Is Good Enough

Perfection isn't always necessary. Ask yourself:

• Will this issue affect my analysis significantly?
• Is fixing it worth the time?
• Can I note it as a limitation instead?

A few missing values in 10,000 rows? Probably fine. Inconsistent categories in a small dataset? Fix it.

Start Cleaning

Pick your messiest spreadsheet. Run through the checklist. You'll be surprised how much better your data looks - and how much more trustworthy your analysis becomes.

Clean data isn't exciting, but it's the foundation of every good decision.

---

Related Articles

• 7 Spreadsheet Mistakes That Are Costing You Money
• How to Turn Raw Data into Actionable Insights
• How to Analyze CSV Files Without Coding

---