Data cleaning tool: what "clean" means, and what breaks.
A data cleaning tool finds and fixes the problems that make a spreadsheet unreliable: duplicate rows, inconsistent date or currency formats, blank required fields, mismatched keys between sheets, and stray whitespace or casing. Tools like OpenRefine or Excel's own functions can catch some of this automatically, but they still require you to pick the tool, learn its rules, and review every flagged row yourself. They also struggle on messy real-world files: merged cells from old exports, columns that switch meaning halfway down, and duplicate customer records spelled two different ways. The faster way to know if a file is usable is to run one real batch through a cleaning pass as a single paid job and check the output against what you'd expect by hand, before picking or configuring any tool.
What are the tools for data cleaning?
Most teams reach for one of four kinds of tool. Spreadsheet functions handle basics, query tools make repeatable transformations, dedicated apps help inspect messy values, and SQL works once the data is in a database. General-purpose AI can help review a bounded sample, but its behavior depends on the interface and file you use.
Spreadsheet and query tools
Excel with Microsoft Power Query is a fit when the source is a table or file and the cleanup should be repeatable. The Power Query documentation describes connecting, transforming, and combining data; check the saved query steps against your own file before relying on them.
Dedicated cleaning apps
OpenRefine's facets help explore values in a messy table, while its clustering and transformation features suit hands-on normalization. WinPure is a separate option to evaluate when duplicate matching across business records is the main problem.
Database and AI workflows
SQL is useful once the rows are already in a database and the team can express the rule. A general AI chat can help inspect a smaller sample or supported upload, but do not treat a chat response as a repeatable pipeline or as proof that every row was processed.
None of these is a single answer. Each one catches a different slice of the mess, and picking the wrong one for your file just means a different set of rows slip through uncaught.
Is SQL a data cleaning tool?
SQL can do real cleaning work once your data is already in a table: deduplicating on a key, standardizing a date column, joining two tables to catch mismatched IDs. What it does not do is decide which of two conflicting values is correct, or catch a typo that changes the meaning of a field rather than its format. That judgment call still needs a person, or a job that flags the uncertain rows instead of silently picking one.
Can ChatGPT do data cleaning?
It can help inspect a bounded sample or a supported file upload, but the result depends on the model, interface, file, and instructions. Do not infer a fixed row ceiling or a complete audit trail from a chat response. If the same rule must be applied repeatedly, use a saved query, a dedicated tool, or a controlled job and review the output.
Where spreadsheet cleaning breaks on real files
Every data cleaning tool's demo uses a tidy sample file. Real exports include the cases that sample never covers.
- Merged cells from old exports. A row that used to be one entry now spans two, and a naive cleanup script reads it as a blank followed by a duplicate.
- Columns that switch meaning halfway down. An export where "status" means one thing for the first 400 rows and something else after a system migration.
- Duplicate keys spelled two different ways. The same supplier or customer entered as "Acme Inc." in one row and "ACME INCORPORATED" in another, so simple duplicate detection misses the match.
- Inconsistent date and currency formats. A single column mixing DD/MM/YYYY and MM/DD/YYYY, or local and foreign currency figures with no marker distinguishing them.
- Blank required fields hidden in the middle of a large file. Easy to miss by eye in a 5,000-row export, and easy for a naive script to fill with a wrong default instead of flagging it.
A community discussion about which platform to use for data cleaning is a useful reminder that the file matters more than the tool name. Before choosing, inspect the columns that change meaning, the duplicate rules, and the rows that need a human decision.
Data cleaning tool vs. a single cleaning job
Picking a cleaning tool and paying for one cleaning job solve the same problem with a different risk profile.
| Manual cleanup | Cleaning tool | Single cleaning job | |
|---|---|---|---|
| What you do | Fix every row by hand | Pick a tool, learn its rules, review flags | Send one file |
| What you get | Full control, slow | A workflow you own and re-run | A tidy file plus a change log, once |
| Setup before first result | None | Hours to days learning the tool | None |
| Where it breaks | Time and attention to detail | Edge cases the tool wasn't tuned for | Limited to the batch you sent |
| Commitment | None | Time investment, sometimes a license | Pay per run |
How do named tools compare?
| Tool | Data fit | Documented workflow | Audit trail to check | Public price on fetched page | Good first use |
|---|---|---|---|---|---|
| Excel with Power Query | Tables and file-based business data | Connect, transform, and combine data with saved query steps | Review the query steps and output before refresh | Not stated on the cited documentation page | Repeatable spreadsheet cleanup |
| OpenRefine | Messy tabular data and inconsistent text | Facets, clustering, and transformations | Review the transformation history and changed values | Free and open source; no paid price shown on the cited docs | Explore and normalize one messy dataset |
| WinPure Clean & Match | Business records and duplicate matching | Deterministic or fuzzy matching features described by the vendor | Review match decisions and rejected matches | Not stated on the fetched product page | Evaluate duplicate-heavy CRM data |
| Pitstop, our service | One CSV or spreadsheet export | One scoped cleanup run, with the offer limited to up to 5,000 rows | The service page describes the output and review process | Request page; no public price shown in the fetched page | Check one difficult file before adopting a tool |
Prices and feature pages change. The product rows above record what the cited pages exposed when fetched on July 15, 2026; verify the current offer before choosing a tool.
Free tools such as OpenRefine are a good route once the same messy export lands on your desk repeatedly and it is worth learning the rules once. A single job makes sense when you have one messy file right now and want to assess it before investing in a tool. Pitstop's spreadsheet cleanup job is an offer for one CSV or spreadsheet export up to 5,000 rows; the service page defines the delivered artifact and review flags.
A good first test file is not your tidiest export. Send the one you've been avoiding: the supplier list with three years of manual edits, the lead export merged from two CRMs, the ops sheet where someone renamed a column last quarter. That file tells you more in one run than a clean sample ever would, because requesting a scoped AI job to clean it is cheaper to test than a tool subscription you might not need.
Can Excel do data cleaning on its own?
Excel's built-in functions (Remove Duplicates, TRIM, Find & Replace, Text to Columns) handle a real slice of the work, especially formatting and exact duplicates. They do not catch near-duplicates spelled differently, a column that quietly changes meaning partway through the file, or a mismatched key between two sheets that should reference the same entity. Those are judgment calls that a formula can't make on its own, which is why most manual cleanup passes in Excel still miss rows.
FAQ: data cleaning tool
What are the tools for data cleaning? Spreadsheet functions in Excel or Sheets, dedicated apps such as OpenRefine or WinPure, SQL queries against a database, and general-purpose AI models such as ChatGPT for smaller files. Each catches a different slice of the mess and none of them catch everything on its own.
Can ChatGPT do data cleaning? It can help inspect a bounded sample or supported upload, but behavior depends on the model, interface, file, and instructions. It should not be treated as a repeatable pipeline or a complete audit trail.
Can Excel do data cleaning on its own? Excel's built-in functions handle duplicates, trimming and basic find-and-replace, but they do not catch cases such as the same company spelled two different ways or a column that quietly switches meaning halfway down, which is where most manual cleanup passes miss.
Do I need to keep a cleaning tool after trying it on one file? No. A single cleaning job gives you an output and a change log for the batch you sent, with no software to install or subscription to keep.
If this job comes back every month, the spreadsheet cleanup service page covers what one run costs to request. The same test-one-batch-first approach applies to other paperwork jobs in the catalog, including invoice OCR.
Written by Tileo, who runs these micro-services on his own portfolio every day.