Workflow

The analysis is easy. The file is the problem

Analysts have told every survey for a decade that most of their time goes to preparing data rather than using it. Six spellings of one customer, dates in three formats, a column that contains a name and an address, and 400 rows that are nearly duplicates. Describe the fix and SheetAI applies it as a rule you can read, on a copy you can revert.

Try it on your sheet See real workflows

Audit-ready trailNo data trainingReversible per action

~45%

Of data work spent loading and cleaning

Anaconda's 2020 State of Data Science survey of 2,360 practitioners found data loading took 19% of their time and data cleaning 26%, close to half the total.

Source: InfoWorld, reporting Anaconda's 2020 State of Data Science

94%

Of audited workbooks had errors

Dartmouth researchers audited 50 operational spreadsheets and found errors in all but three, a 94% rate that matches Panko's earlier review of field audits.

Source: Powell, Baker and Lawson: Errors in Operational Spreadsheets (2009)

67%

Don't fully trust data for decisions

In a Precisely and Drexel University LeBow survey of more than 550 data and analytics leaders, 67% said they do not completely trust the data their organisation uses for decision-making.

Source: Precisely and Drexel LeBow: 2025 Data Integrity Trends and Insights

Data Cleaning: illustration for SheetAI's data cleaning use case

Poor data quality costs the average organisation around $12.9 million a year.

That is Gartner's estimate, and it is a modest one next to IBM's often-quoted $3.1 trillion figure for the annual cost of bad data to the US economy. Neither number is about exotic failures. They are about decisions made from files that nobody had time to clean.

Source: Gartner: Data Quality Research

This page is about the unglamorous half of every analysis: the deduplication, standardisation, splitting, joining, type-fixing, and outlier-hunting that has to happen before a pivot table means anything. SheetAI reads the actual contents of your columns, tells you what is wrong with them, and applies the fix as a documented change, usually as a standard formula in a new column rather than a silent overwrite of your source data. It shows you what it is about to do before it does it on anything destructive, and every step can be reverted. What it will not do is decide, on your behalf, which of two conflicting records is the true one.

Where the hours actually go

Four numbers explaining why the boring part is the expensive part.

Practitioner time spent loading and cleaning data

~45%

Anaconda's surveys have held roughly steady on this for years. It is the single largest line item in an analyst's week, and the one least visible to whoever commissioned the analysis.

Anaconda State of Data Science

Average annual cost of poor data quality per organisation

$12.9M

Gartner's figure covers rework, misdirected spend, and decisions made on wrong inputs. The cost is diffuse, which is exactly why it never gets a budget line.

Gartner

Customer and prospect records believed inaccurate

~30%

Experian's data management research finds organisations routinely estimate a quarter to a third of their records are wrong, and continue to run campaigns and forecasts against them.

Experian Data Quality

Spreadsheets found to contain at least one error

~90%

Field audits collected by the European Spreadsheet Risks Interest Group; the rate holds across industries and across decades of studies.

EuSpRIG

The pattern: cleaning is not a preliminary to the work, for most people it is the majority of the work. Automating it does not just save time; it makes the cleaning rules explicit and reviewable instead of living in whatever the analyst remembered to do.

Anatomy of cleaning one real file

The actual sequence on a customer list that arrived from three systems. Nothing here is unusual and all of it takes a day.

Phase 1: Find out what is actually in there

›Check each column's real type versus its intended type
›Count blanks, distincts, and obvious junk values
›Spot the columns holding two things at once

⚠ You cannot fix what you have not profiled, and profiling by scrolling is how the surprise in row 4,000 stays hidden until Thursday.

Phase 2: Standardise the formats

›Dates into one format; numbers out of text
›Trim whitespace, fix casing, strip stray characters
›Normalise country, currency, and unit conventions

⚠ Every source system has its own conventions and the join fails silently on the difference between 'UK' and 'United Kingdom'.

Phase 3: Deduplicate

›Find exact duplicates on the key
›Find near-duplicates: spelling, punctuation, legal suffixes
›Decide which record survives a merge

⚠ Exact duplicates are trivial. Near-duplicates are the actual problem, and the merge decision is a judgement no tool should be making unsupervised.

Phase 4: Split, join, and derive

›Split combined fields into their components
›Join to the reference table and check the match rate
›Derive the categories the analysis needs

⚠ A 94% match rate looks fine and hides the fact that the missing 6% is one whole region.

Phase 5: Validate and document

›Range checks, totals reconciliation, sanity tests
›Record what was changed and why
›Hand over something a colleague can trust

⚠ The documentation step is always dropped, so next quarter the whole thing is redone from memory, differently.

Cautionary tale

What a data-entry mistake actually costs

In 2020, Public Health England lost close to 16,000 positive COVID-19 test results because case data was being collated in an older Excel file format whose worksheets cap out at 65,536 rows. When the file hit the limit, the additional records were simply not read. The consequence was not an accounting embarrassment: roughly 16,000 cases went unreported for several days, and thousands of the contacts who should have been traced were not contacted in time. Nobody wrote a wrong formula. The pipeline silently ran past a limit of the file format it was using.

The lesson: Data problems do not usually announce themselves with an error message; they present as a number that looks reasonable. The defence is to make the data preparation step explicit and inspected: profile the file before trusting it, check row counts against the source, and let the tool tell you what it found rather than quietly proceeding. SheetAI reports what it sees in your columns before it changes anything, and every transformation it applies stays visible as a rule.

Source: BBC: Covid: 16,000 cases missed in daily figures after IT error

Who it's for

If your analysis keeps getting delayed by the file it depends on, start here.

Data Analysts

Spend nearly half of every project on preparation that nobody credits and nobody documents.

Operations Managers

Merge exports from systems that disagree about names, dates, units, and identifiers.

CRM & Marketing Ops

Own a database where the same customer exists four times under four spellings.

Finance Teams

Reconcile ledgers where the join key is a description field somebody typed by hand.

Researchers

Combine datasets that were each collected with different conventions and no codebook.

Real cleaning workflows

The prompt, the rule it applies, and what changes in your sheet.

data-cleaning_workbook.xlsx - SheetAI

You ask

“Profile every column in Raw!A:AC. For each one: detected type, blank count, distinct count, min and max where numeric, the five most common values, and anything that looks wrong: mixed types, hidden whitespace, impossible dates, or values that appear once. Do not change anything yet.”

SheetAI does

✓Reads the sheet's schema and computes per-column statistics.
✓Detects the actual stored type versus the apparent one.
✓Surfaces low-frequency values, which is where typos live.
✓Writes a profile sheet without modifying the source.

Formula written

=SUMPRODUCT(--(LEN(A2:A5000)<>LEN(TRIM(A2:A5000))))

Result

Twenty-nine columns profiled in one pass. Four columns storing numbers as text, one date column with two formats mixed, 312 cells with trailing whitespace, and a country column containing eleven spellings of two countries.

Everything you need to make a file trustworthy

Profile first, change second, and keep the rule visible. Cleaning steps arrive as readable formulas in new columns rather than silent overwrites of your source data.

✓Full-column profiling and type detection

✓Fuzzy duplicate clustering with review

✓Date, number, and text standardisation

✓Confident-only splitting with flagged exceptions

✓Join diagnostics before you rely on a join

✓Reversible changes with a visible rule

Plays well with your stack

CSV and TSV imports of any origin
Excel (.xlsx) workbooks with multiple sheets
Google Sheets via the add-on
CRM and ERP exports
Database query result dumps
Survey and form response exports

What preparing one merged dataset looks like

A representative analyst consolidating three system exports into one reportable table, before and after the preparation moves into prompts.

Before SheetAI

~19 hours

Mon
Open each export, scroll, discover the format inconsistencies
~4h
Tue
Standardise dates and numbers; fix the text columns by hand
~5h
Wed
Hunt near-duplicates with sorting and eyeballing
~5h
Thu
Join, discover the match rate is bad, work out why
~3h
Fri
Sanity-check totals, redo the two steps that broke
~2h

✓

With SheetAI

~4 hours

Mon
Profile all three files; get the problem list up front
~1h
Tue
Standardisation applied as rules; review the ambiguous rows
~1h
Wed
Duplicate clusters reviewed and merged with judgement retained
~1h
Thu
Join diagnosed before use; the root cause fixed once
~1h

~79% reduction in preparation hours on a representative three-source consolidation.

What SheetAI will not do

Cleaning is destructive by nature, so the limits are about where a machine should stop and ask.

Merge duplicate records unsupervised

Finding the clusters is mechanical; deciding which record survives and which fields win is a business decision with consequences downstream in every system that consumes the data. SheetAI clusters and presents; you approve. The one thing worse than a duplicate is a merge that silently discarded the correct address.

Overwrite your source data by default

Cleaning steps land in new columns beside the originals unless you explicitly ask otherwise, because the original is the only evidence you have that the transformation was right. When you do ask for an in-place change on a destructive operation, it says what it is about to do first, and it stays reversible.

Guess which of two conflicting values is true

If one system says the customer is in Manchester and another says Leeds, no amount of processing resolves that; the information required is not in the file. It flags the conflict and leaves it. A tool that picks one is not cleaning your data, it is fabricating a resolution.

Fix a broken pipeline upstream

If a source system is producing malformed exports every month, cleaning them every month is a treatment rather than a cure. The profile report is genuinely useful evidence to take to whoever owns that system, but it does not repair it, and no spreadsheet tool should pretend otherwise.

“

The thing that changed was the profiling. We used to find the problems halfway through the analysis, when the numbers looked odd. Now we get the list of everything wrong with the file in the first ten minutes, before anyone has built anything on top of it. Half our reworks just stopped happening.

Analytics Lead, logistics operator (three-system consolidation, monthly)

Frequently asked

What people want to know before letting a model near their source file.

Will it overwrite my original data?

›

Not by default. Cleaning results are written into new columns beside the originals so you can compare before committing, because the original file is your only evidence that a transformation was correct. If you explicitly ask for an in-place change, destructive operations are described before they run and remain reversible from the history.

How does it find duplicates that are not exact matches?

›

It normalises the comparison key (case, punctuation, whitespace, legal suffixes like Ltd or GmbH, common abbreviations) and clusters rows whose normalised keys are close rather than identical. Crucially it stops there and shows you the clusters side by side. The merge decision is yours, because survivorship rules are a business decision, not a string-similarity threshold.

Can it handle a hundred thousand rows?

›

Yes. It reads ranges on demand rather than loading the whole file into context, and for heavy transformations the Python tool runs the work server-side and writes the result back. The practical limit is your spreadsheet application's own, not the tool's.

What about ambiguous dates?

›

It uses surrounding evidence (other rows, the sequence, the locale of the source) to infer the format, and where a value is genuinely ambiguous, like 05/06/2026, it flags it rather than picking. That flag list is usually short and always worth reading, because a silently mis-parsed date column is one of the hardest errors to detect downstream.

Does it write formulas or just values?

›

It prefers formulas. A cleaning rule expressed as a formula in a column is inspectable, re-runnable when the source updates, and explicable to a reviewer. Values are written where a formula would not make sense, the output of a research step, for instance. Either way, the rule that produced the column is stated.

Can I see what it changed?

›

Yes. Every action is logged with the prompt, the affected range, and a reversible diff, and cleaning steps that add columns leave the before-and-after visible side by side. If a reviewer asks why row 812 changed, that is a lookup rather than an investigation.

Will it work on data from any source?

›

It works on what is in the sheet, so anything you can import: CSV, TSV, Excel, a database dump, a CRM or survey export. It has no opinion about origin. The profiling step is particularly worth running on unfamiliar sources, since that is where the surprises are.

Is this a replacement for a proper ETL pipeline?

›

No, and it is worth being clear about it. If the same file arrives every week and needs the same fifteen transformations, that belongs in a scheduled pipeline with tests. This is the right tool for the one-off, the exploratory, the file that arrived by email, and the long tail your pipeline does not cover, which for most teams is the majority of the actual work.

Sources & further reading

Every benchmark and statistic on this page is drawn from publicly available research. We cite our sources because we read theirs.

Get started

Stop cleaning by hand.
Start with a profile.

Open your messiest file and ask what is wrong with it. You will get the list in a couple of minutes, before you build anything on top of it.

All use cases

Pay as you go, no subscription required