Every spreadsheet that more than one person has touched is messy in the same five ways. The customer name has a trailing space. The dates are three different formats, and some of them are numbers. Country is UK, U.K., and United Kingdom. There are two rows for Acme Corp because someone typed it twice. And a revenue column has N/A in it, which means the whole column is text and none of it will sum.
The mess is predictable. So is the cleanup — but only if you do it in the right order. Most people don’t, and the specific way it goes wrong is worth understanding before you touch anything.
Why does the order matter?
The instinct is to remove duplicates first, because duplicates are the most visible problem. This is exactly backwards.
Deduplication compares values. " Acme Corp", "acme corp", and "Acme Corp" are three different strings, so a dedupe pass sees three distinct customers and keeps all of them. You have now run the deduplication, the tool has reported success, and the duplicates are still there — but you believe they are not, which is worse than knowing they are.
Normalise the text first and all three collapse to one value, which dedupe then catches trivially. The same logic applies upward: fix the column types before you fix the dates, because a date column read as text is a different problem from a date column read as a number.
So the order is: types → text → dates → duplicates → validation.
Step 1: Fix the column types
A column has one type. If a single cell in your revenue column contains N/A or — or TBD, the whole column is text, and every sum, average, and sort over it is either wrong or refuses to run. Sorting a numeric column stored as text puts 100 before 20.
Find the offenders before you convert. The values that break a numeric column are almost always a placeholder for missing, and the fix is to decide what missing means here — empty, or zero — and use it consistently. They are not the same. An empty cell excluded from an average and a zero included in it produce different numbers, and only one of them is the number you meant.
Tablitsa infers a type for each column while it imports the file, from the values it finds there. When a column is ambiguous — or the inference got it wrong — the Detect Types button on the AI toolbar takes a closer look and proposes a type per column for you to accept or override, one column at a time. It is available on every plan, including Free.
Step 2: Normalise the text
Three passes, in this order:
- Trim whitespace. Leading and trailing spaces are invisible on screen and fatal to every comparison, join, and grouping you will ever run.
- Unify casing. Pick one — title case for names, upper for codes — and apply it to the whole column.
- Collapse the obvious variants.
UK/U.K./United Kingdomis a decision about which one is canonical, not a transformation. Make the decision once, per column, and write it down somewhere.
In Tablitsa these are column-level formatting operations from the column menu, and each one shows you a before/after preview with the number of rows it will change before it runs. Read the affected count. If you expected to fix 40 rows and it says 12,000, something in your assumption about the column is wrong and you want to know that now rather than after.
Step 3: Fix the dates
Dates are the single most reliable source of silent corruption in spreadsheet data, for a reason that is worth knowing.
Excel does not store dates as dates. It stores them as the number of days since 30 December 1899. What you see as 15/03/2025 is, in the file, 45731. The date-ness is a display format sitting on top of a number. Read that file with anything that ignores the format — a CSV export, a parser, a different tool — and the number is all that survives. This is the origin of the mystery 45678 in a column that was full of dates yesterday.
(The epoch is 30 December 1899 rather than 1 January 1900 because Excel believes 1900 was a leap year. It was not. The bug was inherited from Lotus 1-2-3 in the 1980s and has been preserved deliberately ever since, because fixing it would shift every date in every spreadsheet ever made by one day.)
Two practical rules. Set the column type to date before you touch its contents, so a parser is interpreting the value rather than a formatter is decorating it. And when a date passes between systems, use YYYY-MM-DD: it is unambiguous, and it sorts correctly as text, which nothing else does. 03/04/2025 is March in America and April in Europe, and no amount of care downstream can recover which one was meant.
Step 4: Now find the duplicates
With text normalised, exact-match deduplication does most of the work. What remains is the genuinely hard part: values that are different strings and the same thing. Acme Corp, Acme Corporation, Acme Corp., and ACME Co.
No automatic rule gets this right, because sometimes those are one company and sometimes they are two. This is why fuzzy matching should score its confidence and then ask you. Tablitsa’s data merge finds similar values across a column, groups them, and shows each group with a confidence score for review before anything is applied. High-confidence groups — whitespace and punctuation variants — you can wave through. The ones in the middle are exactly the ones a human needs to look at, and a tool that applies them silently is doing you harm at scale. Data merge is available on Pro and Team.
Step 5: Stop it happening again
Everything above is a one-off. The mess returns through the front door: data entry, next month’s import, a colleague pasting from an email.
The only durable fix is to constrain what the column will accept. A required field. A set of allowed values. A numeric range. A date format. Then a bad value is rejected at the moment it is entered, by the person who knows what it should have been, rather than discovered by you in a quarterly report four months later, when nobody can remember.
This is the actual difference between a spreadsheet and a database, and it has nothing to do with size. A spreadsheet lets any cell hold anything. A database has a schema, and the schema is what keeps the data clean while you are not looking. Tablitsa generates validation rules from the data in a column and applies them going forward — on Pro and Team, as is the data-quality analysis that reports on empty cells, duplicates, and distributions before you start.
The short version
- Types first. Everything downstream depends on them.
- Then text. Trim, case, canonicalise.
- Then dates. Set the type, then use
YYYY-MM-DD. - Then duplicates. Exact first, fuzzy with review after.
- Then validation. Or you will do all of this again in March.
If cleaning the same file every month has become a recurring appointment, import it into Tablitsa once and let the column types and validation rules hold the line. The docs cover what each AI operation does and which plan it is on.