Automate the cleanup of an Excel file, with no macro
Every month the same export arrives with the same defects, and the same ten minutes of manual fixes. Chain the steps here once, watching the result build up: the routine is saved, and next month it applies in one click, to one file or to every file you drop.
What this tool does
- Eighteen steps to combine: clean the export, clean text, convert numbers and dates, restore leading zeros, standardise formats, split or merge columns, remove duplicates, filter and sort rows, validate IBANs, company numbers and emails, look up a reference file, unpivot or pivot, build a pivot table, delete or rename columns.
- An assistant that composes the routine from a sentence ("keep rows where Status is paid, sort by Client"): it shows the steps, you check them, nothing is guessed in silence.
- Ready-made routines for the files found everywhere (software export, bank statement, contacts for a CRM, file to import, codes and identifiers), set up from your file's columns.
- The lookup brings in, like a VLOOKUP, the columns of a reference file (the customer list, the chart of accounts); the routine remembers its name, and you just drop it along with the month's files.
- Each step applies to the result of the previous one; the preview and each step's summary update with every setting.
- The routine is kept in your browser and opens again in one click; it can also be downloaded as a small file, to share with a colleague.
- From any cleaning tool, "Continue in a routine" takes the file and the current setting along as the first step.
- Several files: the routine applies to each, and you get an archive with a summary saying which ones to check, or a single file combining all the results, or one file per branch, per sales rep, per value of a column.
- A whole folder, in Chrome and Edge: every file in the folder is processed, and the results are written to a subfolder, next to the originals; next month, the same folder is offered in one click.
- Columns are named, not numbered: a file whose columns have moved is still processed.
What it does not do: A column renamed in the source file is not guessed: the step looking for it is flagged and the file marked "to check" in the summary. The routine holds settings only, never data.
No file is uploaded. Turn off your network once this page has loaded: the tool keeps working. How to check it yourself.
Other tools
- Fix a CSV file : Broken accents, everything in one column, lost zeros: a file Excel opens right the first time.
- Remove duplicates : On the columns you choose, optionally ignoring case, spaces and accents.
- Near-duplicates : "Smith Ltd" and "SMITH LTD.": the almost-identical duplicates, reviewed one by one.
- Clean text : Extra spaces, non-breaking spaces, line breaks, invisible characters, case.
- Convert text to numbers : "1,234.56 $", "(45.00)", "12.5-": real numbers that add up.