Ah, the report. Whatever that is in YOUR organization, we all have one. We gather data, put into our systems, then we download it.
Excel magically opens it (I KNOW, it's still a thrill, isn't it??).
You start looking it over and.... your eyes well up with tears. Your face flushes, your head feels the pressure. Because you can see that before you can actually do anything with the information...
You have to fix it.
Names are in the wrong format. Two pieces of information are crammed into one column. Numbers are stored as text. Dates aren't 'dating'. Random spaces, welcome aboard!
And somehow you're expected to turn all of that into a crispy, beautiful report by 2:00.
Sound familiar?
Start with easy wins
I know, that last section was bringing the DRAMA, right? Did you feel the data mess? In your bones?
I do have good news, friends. Not every data cleanup problem requires an advanced solution.
Sometimes Excel already has exactly what you need.
That's right. Things like:
Text to Columns can split information that's crammed together.
Flash Fill can recognize patterns and help reformat text incredibly quickly.
Find & Replace can fix repetitive inconsistencies.
Even your basic sorting and filtering action can help you identify random outliers hiding in a large dataset.
Before jumping to automation, learn the simple tools.
They're surprisingly powerful. And as stated, surprisingly simple.
Now ask yourself another question
Ok, back to the drama.
This is where we go down that rabbit hole. There's nothing stopping us from throwing hours – HOURS – at this spreadsheet. We just know we can clean it up, so we roll up the old sleeves and dig in. We decide what that clean up process should look like and we deliver.
Over. And over. And over.
Week after week, cleaning up this SAME report (cue the tears springing to your eyes again).
You need a new question. Instead of "How can I clean this faster?", try asking yourself "Why am I still manually cleaning this?"
That's where tools like Power Query become really interesting.
Power Query to the rescue
Ok, not to go back to the drama yet again, but maybe just one minute more. Come on now, imagine receiving the same messy export every Friday.
The columns need rearranging. Three fields need cleaning. One column needs splitting. Blank rows need removing.
Instead of repeating those steps manually, Power Query can record the transformation process and apply it again when new data arrives.
I'm sorry, what's that now? Power Query can swoop in and SAVE me time??
So next Friday's report won't take hours of my clean up time?
Let Power Query replace or update the source data. Refresh. And let Excel repeat the cleanup.
Welcome back to your day. No tears involved.
Macros 2026 and beyond
You might be thinking macros too, so let's just have a quick chat on these.
Macros are a solution that many people are still using because they have been embedded in their worksheets forever. The problem is that when a macro breaks, there's often no one there to fix it.
Enter Copilot. It's entirely possible that Copilot could help us fix it. (But put a pin in that for now).
Ok, sounds good so far....
However, the biggest challenge with macros right now is that they don't work in the web version or the mobile version of Excel. Ooof.
Well, there's an alternative. This alternative, or updated, version of a macro is called a script. And if you go into Excel and you click on Automate, that's what you'll see.
So really, if you are looking to build anything new for data cleanup, a script would be preferred over a macro.
The important skill is simply knowing that macros, script or Power Query exist. It's knowing when you've crossed the line where automation makes more sense than another manual workaround.
(Side note: As you can see, with Macros 2026, things are changing YET again, so how will YOU stay current?)
What's the goal here?
You might think the goal is cleaner data. It's not.
It's about giving people time back.
Here's a little quick math: If an employee spends 45 minutes cleaning the same report every week, that's roughly 39 hours a year.
That's almost an entire workweek spent preparing data instead of using it. That's just one report. And if we're being honest, you've done WAY more than that, right?
That's why I want to emphasize how the first step is to start with the easiest tool that solves the problem.
And pay attention. Because when you catch yourself doing the same thing over and over, stop. Stop trying to get faster, better, make more time for that.
Instead, start figuring out how to make Excel do it for you.

