When you clean messy Excel data, always work on a copy and fix things in a fixed order. Fix the structure first, then the data types, then the labels, and finally the duplicates. Start by unmerging cells to keep one header row, then convert any dates and numbers stored as text. Make sure every spelling of the same item is identical, and only then remove duplicates, because a duplicate check on inconsistent labels judges the rows wrong.
Last updated 21 September 2026 · by Inam Ul Haq, data analyst and automation engineer · about the author
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, and that same lean is the only free warning you get when REGEXEXTRACT hands numbers back as text.
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, which is before you get to the separate question of which duplicate row the button keeps once it does find a pair. 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. Which of the two goes first is decided by the shape of the job, who refreshes it and where the output has to land. 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. And if you are wondering which AI tools are worth a slot in this work at all, the copilots are weakest on exactly the sheets described above, which is one reason my working analyst stack is four slots rather than ten products.
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.
Which Excel functions fix text that looks clean but is not?
I mentioned earlier that some numbers refuse to sum because an invisible space is sitting inside them. The standard advice is to run your column through the TRIM and CLEAN functions in Excel. But both of these functions actually do less than their names suggest. Microsoft's own help pages explain exactly what each one leaves behind, so that is the part you really need to know before you trust a cleaned column in your report.
| Function | What it removes | What it leaves behind |
|---|---|---|
| TRIM | The ordinary space (character 32) at both ends, and extra spaces between words | The non-breaking space (character 160), common in text copied from web pages |
| CLEAN | The first 32 non-printing characters, codes 0 to 31 | The non-printing codes 127, 129, 141, 143, 144 and 157 |
| SUBSTITUTE | Any one character you name, such as CHAR(160) | Every character you did not name |
| REGEXEXTRACT | Pulls out whatever matches the pattern you write | Matches are case sensitive unless the last argument is 1, and the result is always text. Microsoft 365 only |
The TRIM and CLEAN functions only work from a fixed list of characters, and anything outside that list will pass straight through while the cell still looks clean. So when a value still refuses to match or sum after you use both functions, you have to name the leftover character yourself using the SUBSTITUTE function. This is exactly what Microsoft suggests for the higher characters on their Top ten ways to clean your data page. The newer regex functions have a completely different catch, and I wrote a separate piece on why the numbers REGEXEXTRACT pulls out can add up to zero.
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.