Your spreadsheet data is messier than you think — here's how to fix it

Most business spreadsheets look fine on the surface. The columns are labeled, the data is there, it opens without errors. But underneath, they're carrying months or years of inconsistency that quietly causes problems every time someone tries to use the data for anything serious.

Merging with another dataset fails because one uses "United States" and the other uses "US". A pivot table double-counts because "John Smith" and "john smith" are treated as different people. An import into your CRM skips half the rows because dates are formatted three different ways.

These aren't edge cases — they're the normal state of any spreadsheet that has been maintained by more than one person, or used for more than six months. Here's how to diagnose and fix the most common problems.

The seven most common data quality problems

1. Inconsistent date formats

Dates are the most reliable source of spreadsheet chaos. A single column might contain all of the following:

Any tool that reads this column will either fail or silently misinterpret some of the values. The fix is to standardise every date to a single format — ISO 8601 (YYYY-MM-DD) is the safest choice because it sorts correctly and is understood universally.

2. Extra whitespace

Leading spaces, trailing spaces, and double spaces between words are invisible in most spreadsheet views but wreak havoc on lookups and matching. "Acme Corp" and " Acme Corp" are different strings. This is one of the most common causes of failed VLOOKUP operations.

3. Inconsistent capitalisation

"new york", "New York", and "NEW YORK" are the same city but three different values to any software that needs to group, filter, or merge on that column. Pick a convention — title case for names and places, lowercase for codes and tags — and apply it everywhere.

4. Duplicate rows

Duplicates accumulate in any spreadsheet that gets appended to over time. They're particularly common in data that's been exported from one system and imported to another multiple times. The dangerous ones aren't the obvious complete duplicates — they're partial duplicates where the same entity appears twice with slightly different data in some columns.

5. Mixed data types in one column

A "phone number" column that contains some numbers, some strings with dashes, some with country codes, and some blank cells will cause problems in any system that expects consistent formatting. Similarly, a "revenue" column that mixes numbers and text like "N/A" or "TBC" will break any formula that tries to sum it.

6. Blank cells that mean different things

A blank in a "last contacted" column might mean never contacted, not tracked, or unknown — and these are meaningfully different. Similarly, a blank in a "discount" column might mean zero discount or an unentered value. When blank cells have ambiguous meaning, you'll make wrong assumptions downstream.

7. Column headers that aren't consistent

"First Name", "first_name", "FirstName", and "fname" all mean the same thing but will be treated differently by any tool that needs to map columns. Standardising headers — ideally to lowercase with underscores — makes every downstream import and analysis easier.

The cleaning order matters

If you're cleaning manually, do it in this order:

1. Remove completely blank rows — they can interfere with range detection
2. Standardise headers — so you can reliably reference columns in formulas
3. Trim whitespace — before deduplication, otherwise duplicates won't match
4. Standardise case — same reason
5. Standardise date formats — so sorting and filtering work correctly
6. Remove or flag duplicates — after everything else is standardised
7. Handle blanks — replace with explicit null values or appropriate defaults

When to automate data cleaning

Manual cleaning works for a one-off job. It doesn't work for data that arrives regularly — weekly exports from your CRM, monthly reports from your accountant, quarterly customer lists from your billing system.

For recurring data, the right approach is to define the cleaning rules once and run them automatically every time new data arrives. Tools that do this consistently — applying the same transformations in the same order — produce far more reliable results than manual cleaning, which varies based on who does it and how much time they have.

A quality score helps here: a number from 0–100 that measures how clean a dataset is across multiple dimensions. It makes it obvious when a dataset needs attention before it gets used, and tracks improvement over time as data entry processes get better.