Some years into my Huawei job, a regional team sent me a transmission workbook to audit, and it was the kind of file that looks fine until you touch it. Merged cells stretched across the top like a banner, two header rows argued over the same columns, one city was spelled three different ways, and half the dates sorted like dates while the other half sorted like words. I did what most people do. I started fixing whatever my eye landed on first, a merge here, a spelling there, and two hours later the file was worse than when I opened it, because my fixes were fighting each other and I had no untouched copy to go back to.
That file changed how I work. I have spent six years inside corporate Excel now, planning microwave transmission links and auditing other people's workbooks, and in the evenings I clean client spreadsheets on Upwork where I am Top Rated with a 100% Job Success score. Different industries, same messy spreadsheet disease, and the cure had almost nothing to do with clever functions.
Cleaning is not a bag of tricks. It is a fixed order.
Structure first, types second, labels third, duplicates last, and all of it on a copy while the raw sheet sits frozen. Each station exists because the one after it assumes it is already done. That single idea is most of the skill.
The one order that makes cleaning honest
I think of a dirty sheet as a patient on the table and the cleaning as four stations, because surgery is the right mood for this. You do not improvise, and you do not operate on the original. The stations are structure, types, labels, and duplicates, in that order, and the order is the whole method.
The order cannot flip, and the reason is mechanical. Duplicate detection compares values, so it can only be trusted after the labels are consistent, because a row saying Lahore and a row saying LHR are the same site wearing two coats, and any dedupe that runs first will swear they are different. Label mapping needs types settled first, because a date stored as text and the same date stored as a real date never match in a lookup. And type conversion needs structure, because filters and pivots all assume one header row on one clean rectangle, and a merged cell in the middle shifts everything half a step sideways. Each station makes the next one honest. Run them backward and each one makes the next one lie.
Four words on a sticky note, structure, types, labels, duplicates, will save you more hours than any formula, because the note removes the deciding. You always know what to do next, and most of the panic in a messy file is really just not knowing where to start.
A two click test that exposes fake dates
Station two is where most files are secretly broken, and it has a free diagnostic you can run today. The usual curse looks like this. A CSV arrives from a system that writes the day first, Excel expects the month first, so 03/04/2024 gets read with the wrong calendar while 25/04/2024 cannot be read at all, because there is no month twenty five, and it quietly stays as text. Half the column is now real dates and half is text wearing a date costume. The sheet sorts half right, a pivot splits one month into two rows, and every chart on top is wrong without a warning.
Open the filter arrow on the column and look at how the values group. Real dates fold into a tidy tree of years and months. Text dates cannot fold, so they sit at the bottom of the list, flat and scattered, confessing that they are not really dates. There is a second tell before you even filter, because Excel leans real dates and numbers to the right and leans text to the left, so a column with ragged alignment already has a type problem hiding in it.
Fixing it is its own small craft. It is an explicit conversion that tells Excel the true source order rather than retyping cells, plus a short formula for the numbers that refuse to sum because an invisible space sits inside them. Those exact recipes depend on how your system exports, which is the part I keep tuned per file rather than hand out as a one size answer.
Freeze the original before you touch anything
One rule sits under all four stations, and it comes straight from audit work. Never destroy the original. Before your first fix, duplicate the tab, name one RAW, protect it, and clean only the copy. Values flow one way, from RAW to the copy, and nothing ever flows back. When a number looks strange three hours in, you compare against RAW and know in seconds whether the strangeness arrived with the file or was manufactured by your own cleaning.
I learned to treat the frozen original as a witness. When I audited a bought list of 1,031 supposedly verified emails, only 386 passed my technical checks, 620 needed a closer look, and 25 were provably dead, with one big courier company's whole address sitting on a domain that publicly refuses all mail. The only reason those numbers were defensible to anyone is that every verdict sat in new columns beside an untouched source. Keep a witness. Your future self is the auditor you are protecting.
Where the harder stations live, and when to hand it off
Stations three and four are more work than they look. Labels means giving every stray spelling of a category one clean value, so Lahore, LHR, and lahore stop counting as three cities. Duplicates means the not quite duplicates, where Acme Logistics Ltd and ACME LOGISTICS LIMITED with a trailing space are the same company to your eye and two different things to Excel, and no built in button catches that on its own. Both have clean, repeatable recipes, a reusable mapping and a normalized key. I keep those as templates rather than paste them here, because the fields that define identity change with every file.
There is also a moment to stop cleaning by hand. The first time a shaped file arrives, clean it and write down every move in plain words. The second time, follow your own notes. The third time is the signal, because by then you are a human macro, and the honest move is to encode the rules once, in Power Query inside Excel or a small Python script across many files. A script has one virtue that tired humans never will. When a column vanishes from next month's export, it stops and names the missing column instead of filling the gap with a guess at six in the evening. If Python feels far away, I wrote about learning enough of it fast with AI beside you.
Sometimes the right answer is to hand the whole thing over. If you do, three things make it fast and the quote fair. A real sample file, raw and untouched, that honestly shows the mess. The target shape, even a ten row mock of the clean table. And the rules in plain words, what a blank means, which spelling wins, what counts as a duplicate in your world. With those three, I can start the same day. If you want my eyes on your actual file, start a project and I will tell you honestly whether it is an hour of hand work or a script worth building. I reply fast, and small changes stay free for a month after I deliver.
The whole method fits in one breath. Freeze the raw tab, then work the four stations in order, structure until the sheet is a rectangle, types until the filter tree is tidy, labels until every category has one spelling, and duplicates last, only through a key your own eyes have reviewed. The order is the method, because every station assumes the one before it is done, and the frozen original is the witness that keeps your numbers defensible.
Start with the worst file on your desk this week. Duplicate the tab, name one RAW, and run just station one, and you will feel the difference immediately, because a rectangle with one header row behaves like a different animal. The filter test then shows how much type work remains, and the rest follows in order.
When the same mess lands every month, the done for you version is the honest upgrade, the mapping templates, the conversion recipes, and a Power Query or Python build tuned to your real files so cleaning stops eating your evenings. That is the work I take on. And if you also use the tools I build, the membership on this site includes every product I ship, free for members. Either way, make tea first. Cleaning goes better with tea.