Excel Data Cleaning: The Checks That Actually Matter
Most “bad data” is not wrong — it is written differently. The list below is the set of defects that break lookups, comparisons and summaries, with the formula to detect each one.
Short answer
Before the checklist: the four functions
| Function | Removes | Does not remove |
|---|---|---|
TRIM | Leading and trailing ordinary spaces, and runs of spaces inside a value | Non-breaking spaces, line breaks, any non-space character |
CLEAN | The first 32 non-printing ASCII characters — line breaks, tabs, control characters | Non-breaking spaces, or any visible character |
SUBSTITUTE | Whatever you tell it to — including CHAR(160), the non-breaking space | Nothing, unless you name it |
VALUE | Not characters — it converts a text value that looks like a number into a number | Anything that is not a numeric value once cleaned |
The checklist
Work down the list in order. Each row gives you the check that detects the defect and the fix that resolves it.
| # | Defect | How to detect it | Fix |
|---|---|---|---|
| 1 | Title or header rows above the table | The headers are not in row 1 | Delete the rows above; the headers must be a single row at the top |
| 2 | Blank rows inside the data | Go To Special → Blanks on the key column | Delete the entire blank rows — they break sorts, filters and Table conversions |
| 3 | Merged cells | A merged range holds its value in the top-left cell only; everything else reads as blank | Unmerge everything before doing anything else |
| 4 | Trailing or leading spaces | =LEN(A2)-LEN(TRIM(A2)) | Wrap the value in TRIM |
| 5 | Non-breaking spaces | TRIM does not change the length, so the test above still reports a difference | =SUBSTITUTE(A2,CHAR(160)," ") |
| 6 | Line breaks inside a value | The value wraps in the cell when it should not | =CLEAN(A2) |
| 7 | Numbers stored as text | =ISTEXT(A2) | Clean the characters, then VALUE |
| 8 | Dates stored as text | =ISNUMBER(A2) | DATEVALUE, or re-import with the type set explicitly |
| 9 | Leading zeros lost | A code that should be five characters is four | Format the destination as Text before importing; TEXT(A2,"00000") if you must repair afterwards |
| 10 | Inconsistent case | The same value appears as ABC, Abc and abc | UPPER for codes; use PROPER carefully for names — it will change ABC Traders to Abc Traders |
| 11 | Inconsistent category values | Sort a distinct list and read it | Map them to one form with a lookup table, not with a series of nested IFs |
| 12 | Duplicate rows | =COUNTIF($A:$A,$A2)>1 | Mark and review; remove only after deciding which row survives |
| 13 | Negative amounts written as (1,000) | =ISTEXT(A2) | Strip the brackets with SUBSTITUTE and convert with VALUE |
| 14 | Amounts carrying a currency symbol or separators | =ISNUMBER(A2) | =VALUE(SUBSTITUTE(SUBSTITUTE(A2,"₹",""),",","")) |
| 15 | One field that should be two | “Shree Balaji Enterprises — SB/0417” in a single column | Text to Columns, or Power Query's split column by delimiter |
The order is not arbitrary
The most valuable step: audit the distinct values
Of everything on the checklist, one step catches more real problems than the rest combined: listing the distinct values in every column a person types into, with a count of how often each appears.
Build the distinct list
Sort by the count, descending
Read the short list carefully
Map to a canonical list
| Vendor as written | Count | Standard |
|---|---|---|
| Shree Balaji Enterprises | 184 | Shree Balaji Enterprises |
| SHREE BALAJI ENT. | 37 | Shree Balaji Enterprises |
| Shree Balaji Ent | 22 | Shree Balaji Enterprises |
| Shree Balaji Enterprise | 15 | Shree Balaji Enterprises |
| Shree Balaji Enterprises Pvt Ltd | 9 | Shree Balaji Enterprises Pvt Ltd |
| Shree Balaji Enterprieses | 1 | Shree Balaji Enterprises |
| Shree Balaji Enterpises | 1 | Shree Balaji Enterprises |
This defect does not look like a defect
Never clean in place
Cleaning overwrites values, and overwriting values makes errors unrecoverable. Work in a copy, or in adjacent helper columns, so the original always survives.
| Approach | When it is right | What it costs |
|---|---|---|
| Helper columns beside the data | You want to see the before and after side by side | Extra columns in a sheet other people use |
| A separate cleaned sheet | You are preparing data for a comparison or an import | The cleaned copy must be refreshed when the source changes |
| Power Query | You will do this again | A short learning curve, and the source has to be a file or a table |
Keeping the original is not sentimentality. When a cleaned figure is questioned — and it will be — the ability to show what the source actually said is the difference between an explanation and an argument.
A worked example
A vendor sends a statement of account as a spreadsheet. Nine columns, forty rows of headers and totals in awkward places, and this is what a clean version of it involves.
- 1Delete the first six rows above the header and the two total rows at the bottom.
- 2Unmerge the merged cells in the header block that were used to make the layout look tidy.
- 3Delete row 14, which is entirely blank and splits the data in two.
- 4Strip the non-breaking spaces from the invoice number column — the export came from a PDF and every key carries one.
- 5Convert the amount column from text, after removing the rupee symbol and the thousands separators.
- 6Convert the date column from text to real dates so that a date-range filter works.
- 7Trim the vendor name column and map the four spelling variants to one.
- 8Check that the total row you deleted agrees with the sum of the cleaned data — if it does not, you have removed a row that was not a total.
Step eight is the one people skip, and it is the only step that proves the cleaning did not alter the numbers. Always compare a total from the original against a total from the cleaned version.
Common mistakes
- Cleaning in the wrong order. Converting types before removing stray characters is the most common sequence error, and it produces silent failures because the conversion returns an error that people then overwrite.
- Fixing the symptom and not the source. If the same export needs the same cleaning every month, the useful fix is either a Power Query that replays it, or a conversation with the supplier about the export format.
- Assuming a cleaned column is correct because the formulas worked. A lookup that returns a result has found something — not necessarily the right thing.
- Deleting the original. Then nobody can prove what the source said.
- Using PROPER on everything. It is sensible for personal names and actively harmful for identifiers, codes and anything containing an acronym.
- Not cleaning at all before a comparison. Most “missing” records in a reconciliation are present on both sides and written differently.
Cleaning once, rather than every month
Every step above can be done with formulas in a helper column, and for a one-off task that is entirely reasonable. The problem is the second month, when the same export arrives with the same defects and the same helper columns have to be rebuilt or copied forward with the references shifted.
Power Query removes that repetition — you define the cleaning steps once and replay them. Power Query in Excel covers how. And if the cleaned data is on its way into a reconciliation, the defects worth fixing are the ones that affect the match key — which is what the Piloteq Automate workflow page is about.
If you would rather not build this by hand
Stop cleaning the same file every month
Piloteq Automate works on the files as they arrive and applies consistent matching rules, so the same defects in the same export do not have to be corrected by hand before every reconciliation.
- ✓Rules applied identically to both files
- ✓Exact, tolerance and last-digit matching
- ✓No helper columns left in the source sheets
- ✓Results with a reason recorded per record
Frequently asked questions
What is the correct order to clean data in Excel?+
Fix structure first, then text, then types, then categories. Remove header rows and blank rows, unmerge cells and unpivot anything that should be a row; then strip stray characters and standardise case; then convert numbers and dates that arrived as text; then audit the distinct values in your category columns. Doing it in the reverse order wastes work, because a conversion may fail on a value that still contains a stray character.
What is the difference between CLEAN and TRIM in Excel?+
TRIM removes leading and trailing ordinary spaces and reduces runs of spaces inside a value to a single space. CLEAN removes the first thirty-two non-printing ASCII characters, such as line breaks and control characters. Neither removes a non-breaking space, which is character 160 and arrives frequently when data is copied from a web page or a PDF — that needs SUBSTITUTE with CHAR(160).
Why does Excel treat my numbers as text?+
Because of where the data came from. Exports from PDFs, web pages and some accounting systems write numbers as text, and any value containing a stray space, a currency symbol or a comma inside a text-formatted cell stays text. The quickest test is alignment: numbers sit right-aligned by default and text sits left-aligned. The fix is to remove the offending characters and then convert with VALUE, or to clean the column in Power Query where the type change is explicit and replayed.
How do I stop Excel from removing leading zeros?+
Format the destination column as Text before you paste or import, and the zeros are preserved as characters. If the data is already in, converting with a formula requires TEXT with a fixed length — for example TEXT(A2,"00000") — but you have to know how many digits the field should be, which is why preventing the loss at import is the better solution.
How do I find inconsistent values such as the same vendor spelled several ways?+
Build a distinct-value list and count the occurrences. You can do this with a PivotTable on the column, or with the UNIQUE function in newer versions of Excel. Sort by the count and read the short list. Forty-seven spellings of four vendors is a very common finding, and it is the single most valuable cleaning step because it affects every summary you will ever produce.
Should I clean data with formulas or with Power Query?+
If you will do this again next month, use Power Query. It records the cleaning steps and replays them, so the work is done once rather than repeated. Formulas are fine for a one-off, but they leave a set of helper columns in the sheet that somebody will eventually delete, and next month you rebuild them.