Auto-Clean an Excel file

Fix a spreadsheet's problems — the ones you choose, and no others

Detect Anomalies tells you what looks wrong and stops there, on purpose. Auto-Clean is the other half: the same findings, each paired with the exact value it would write instead, and a tick box. Untick something and it survives into the cleaned copy exactly as it was. Nothing is corrected behind your back, and nothing is guessed.

What it can fix

• Extra whitespace — leading and trailing spaces, double spaces, and non-breaking spaces that look like ordinary ones and break every lookup they touch
• Lookalike characters — curly quotes, en and em dashes, zero-width characters and soft hyphens, replaced with the plain ASCII twin that an import expects
• Inconsistent capitalisation — a value recased to the capitalisation the rest of its column already uses
• Inconsistent date formats — a date written the odd way out, rewritten in its column's usual layout. The day and the month are never swapped: only the layout changes
• Numbers stored as text — 1,200 or $1,200.00 converted to a real number in a column that is otherwise numeric, and the reverse where a column is otherwise text
• Duplicate rows — the repeated copies removed, the first occurrence of each kept

What it won't fix

A fix has to be derivable from the sheet, not invented. Three kinds of finding never have that, so they are reported and left alone:

• Missing values — there is nothing to fill a blank in with. Only someone who knows what belongs there can supply it.
• Outliers — an outlier is a judgement call, and often the one number in the sheet that is actually right.
• Repeated identifiers — when two rows claim the same ID, which of them is wrong is a question about your data, not about the file.

Cells holding a formula are never overwritten either. What you see in such a cell is the formula's result, so changing it means rewriting the formula — which is a different decision from cleaning a value, and yours to make.

How it works

1. Upload a .xlsx or .xlsm file and choose a sheet.
2. Scan it. Nothing is written at this stage. You get the problems grouped by kind, each with the complete list of cells it would touch and the exact value it would write.
3. Tick what you want fixed and download the cleaned copy. Your upload is untouched — the cleaned file is a new one.

Common questions

Is my original file changed?

No. The cleaned copy is a new file; your upload is read, never written, and deleted within the hour along with the copy.

Does the rest of the workbook survive?

Every sheet comes through, along with cell formatting, column widths, number formats and formulas, and an .xlsm keeps its macros. Charts, images, pivot tables and slicers do not survive the rewrite — if your workbook depends on those, clean it by hand using the scan as your list.

What happens to a name like O'Brien?

It is left alone. Recasing to title case follows word boundaries, and an apostrophe counts as one, so a possessive would come back with a capital in the middle of a word. Rather than guess, Auto-Clean skips those and tells you it did. Names with an internal capital, like MacDonald, do get recased — which is why every change is shown before you accept it.

Why is a fix I expected not on the list?

Because the finding it comes from is judged against its own column. A column that is legitimately all lowercase, or full of symbols, isn't flagged for being that way, so there is nothing to fix. Very short sheets produce fewer findings for the same reason.

Can I clean more than one sheet?

One sheet per run. Clean the second sheet by uploading the cleaned copy and running it again.

What are the limits?

.xlsx and .xlsm, up to 10 MB, one sheet at a time. Three cleaned files a month without an account — scanning to see what could be fixed is free, and only the download counts. Your file is deleted within the hour.