Every Monday morning for about eight months, I sat down with a coffee that went cold before I finished it — because I was buried in Excel files. Client performance reports, campaign summaries, budget trackers — all living in slightly different formats, all needing to be converted, cleaned, and restructured before anyone else on the team could actually use them. I estimated I was losing somewhere between four and six hours a week just to this one task. That's a full workday every two weeks, gone.
Then I finally did something about it. Here's exactly what I did, what tools I used, and what I'd do differently if I were starting from scratch today.
Why Excel Report Conversion Was Eating My Time
The problem wasn't Excel itself — it's a perfectly capable tool. The problem was the inconsistency between sources. Data was coming in from multiple platforms: Google Ads exports, Meta Ads Manager downloads, CRM outputs, and manually maintained budget sheets. Each one had its own column structure, date formatting, and naming conventions.
Before any analysis could happen, I had to:
- Rename columns to match our internal schema
- Strip out merged cells and decorative headers
- Reformat dates from MM/DD/YYYY to ISO 8601
- Convert currency strings like "$1,204.50" into plain numerics
- Combine data from multiple tabs into a single flat table
Do that once? Fine. Do it 12 times a week across different clients? That's where the hours disappeared.
The First Step: Mapping the Repetition
Before I touched a single tool, I spent one full week just documenting the transformations I was doing manually. I opened a blank spreadsheet and logged every action — every column rename, every formula I was copying, every manual find-and-replace. This gave me a transformation map: a source-to-destination dictionary for each report type.
This step is easy to skip and almost always worth doing. You can't automate what you haven't fully described. Once I had that map, the actual build took far less time than I expected.
The Tools I Used (And Why)
Python with pandas and openpyxl
For the heavy lifting, I used Python. If you're not a developer, don't let that put you off — the scripts I wrote were relatively straightforward, and the logic mirrors what you'd do manually. The pandas library handles tabular data beautifully, and openpyxl lets you read and write .xlsx files directly.
My core script did the following in sequence:
- Read the incoming Excel file and detect which report type it was based on column signatures
- Apply the relevant transformation map (rename columns, drop unused ones, reformat values)
- Output a clean, standardised CSV or Excel file into a shared folder
The detection logic was simple: if column A was "Campaign Name" and column D was "Impr.", it was a Google Ads export. If it had "Ad Set Name" in column B, it was Meta. I maintained a small config file — just a JSON dictionary — that matched those signatures to transformation rules. Adding a new report type meant updating the config, not rewriting the script.
Power Automate (for the non-technical team members)
Not everyone on the team was going to run Python scripts from a terminal, nor should they have to. For the parts of the workflow that others touched, I used Microsoft Power Automate to watch a shared OneDrive folder. When a new file landed there, it triggered the Python script via an HTTP request to a simple local Flask endpoint (later moved to an Azure Function so it could run without my machine being on).
The result: someone drops a raw export into a folder, and a cleaned file appears in the output folder within about 30 seconds. No one has to think about it.
A Lightweight Validation Layer
One thing I added after the first month was a validation step. The script now checks the output against a set of rules before saving it — things like: are there any null values in required columns? Does the date range make sense? Are there suspiciously large or small numbers that might indicate a formatting error?
If something fails validation, the file goes into an "exceptions" folder with a log file explaining what went wrong. This caught several issues early — including one where a client's export had a hidden row that was throwing off all the column index mappings.
What the Time Savings Actually Looked Like
In the first full month after the automation was running, I tracked my time carefully. The four-to-six hours per week dropped to roughly 20-30 minutes — mostly reviewing exception files and occasionally updating the config when a platform changed its export format (which happens more than you'd like).
Across a year, that's somewhere between 150 and 200 hours reclaimed. At an agency billing rate, that's significant. More importantly, that time went back into actual strategy work — the kind of thinking clients are paying for, not the kind of reformatting that a script can do faster and more accurately than I can at 8am on a Monday.
What I'd Do Differently
If I were building this from scratch today, I'd do two things earlier:
- Set up the Azure Function from day one instead of running the script locally first. The local setup created a false dependency on my machine that caused problems when I was travelling.
- Involve the team in the transformation map phase. I built the initial mappings based on my own understanding of the reports, and I got a few things wrong that my colleagues spotted immediately when they started using the outputs. Their domain knowledge would have saved me a revision cycle.
This Isn't Just an Excel Problem
The underlying principle here applies to any repetitive data-handling task: document the repetition before you automate it, build the simplest thing that works, then harden it. I've since applied the same approach to PDF invoice parsing, social analytics exports, and onboarding data ingestion.
Automation doesn't have to mean a massive platform investment or a dedicated developer. It can start with one script, one folder, and a clear description of what you're doing by hand right now.
If you're spending meaningful hours on a task that follows a consistent pattern, that's a signal — not that you need to work faster, but that the task probably shouldn't require a human at all.