Excel Automation: Every Approach Compared, and Why the Logic Is the Hard Part

What Excel automation actually covers

Three different jobs get filed under one term, and confusing them is the first mistake.

Inside one workbook. Recalculating, applying rules to rows, building a summary tab, formatting an output sheet. Everything the automation needs is already in the file.

Across several files. Five regional workbooks into one consolidation, or a statement against a ledger export. The automation has to align them and decide what agreement means.

Between Excel and everything else. Downloading a report, waiting for an attachment, uploading the result back into a portal. Excel is one station on the line, not the line.

Most published advice covers only the first job. Most real back office work is the second and third.

---

Why Excel automation projects stall

The reason a spreadsheet takes six years to automate is not that VBA is hard. It is that the specification does not exist.

Open any workbook that has run a real process for a few years:

• A nested IF deciding whether a difference is a match or an exception. Nobody remembers who chose the threshold.
• A VLOOKUP into a mapping tab that somebody maintains by hand, sometimes.
• A conditional format where red means "checked and genuinely wrong" and amber means "not checked yet", a workflow state living in a cell colour.
• A hardcoded exclusion, usually an account code or a set of items, sitting inside a SUMIFS with no comment explaining it.
• A column overridden manually about once a quarter, for a reason the person doing it can explain in ten seconds and never wrote down.

None of that is documentation. All of it is the specification, written in a language never meant to be read as one.

So the first real task is not writing code. It is getting a person to state rules they have applied by reflex for years. That conversation is slow, it is boring, and it kills more projects than the code ever does.

The second reason compounds the first: the specification keeps moving. A director asks a new question in March, a column goes in, the totals row shifts down. The source system changes its export and the paste lands two columns to the right.

The automation now produces a wrong number silently, and silently is the dangerous word. Manual work has a person looking at it who notices when a total looks odd. Automation removes the person and, unless somebody designed for it, removes the noticing.

Judge every approach below on those two facts: the unstated rules, and the moving template.

---

The five ways people automate Excel

Macros and VBA

VBA has shipped inside Excel since the mid nineties. No licence, nothing to install, and on a locked down corporate machine it is often the only programmable thing a business user can reach.

Genuinely good at: work inside one workbook, on a template that does not move, maintained by someone who sits near the process. A monthly formatting routine. A button that rebuilds a summary tab. If that is your job, VBA is the correct answer and reaching for Python is self harm. It also travels with the file, which nothing else here does.

The ceiling, in the order people hit it. Recorded macros encode positions, not meaning: the recorder writes down that you selected D14, not that you selected the total, so an inserted column leaves it doing the wrong thing confidently. Then maintenance, because a three hundred line module with no tests, written by someone who has left, is hard to change safely. Then reach: Microsoft blocks macros arriving from the internet by default, and VBA does not run in Excel for the web.

Best fit: one workbook, stable template, a maintainer inside the team, desktop Excel.

Power Query and Office Scripts

Power Query is the most underused tool in this article. Built into Excel for Windows, no code, and it records a transformation rather than a sequence of clicks: import, promote the headers, drop these columns, unpivot that block, merge, load. Next month you press refresh.

What matters is what that does about the template problem. Power Query steps refer to columns by name, so new rows are free and a column arriving in a different position is free. The step says "the column called Invoice Date", not "column F". That removes the most common cause of Excel automation breakage, with no code involved.

Where it stops. Power Query shapes data, it does not make decisions. The exception that needs a human look, the override that happens once a quarter, the two rows matching on amount but not on reference: push those in and you get conditional columns nested six deep, the same unreadable specification moved somewhere new. It also does not fetch the file, so if the report must be downloaded first, Power Query starts late.

Office Scripts is the browser era version: TypeScript, runs in Excel for the web, plugs into Power Automate. It needs a Microsoft 365 business or enterprise licence, and it is a non answer if your process touches systems that are not Microsoft's.

Best fit: recurring import, clean, reshape, load, done by the person who owns the report.

Python: pandas, openpyxl, xlwings

Python is the most capable option here and it is not close. pandas does in eight lines what takes a day of formulas. openpyxl reads and writes files directly, so it runs on a server with no Excel installed. xlwings drives a real Excel instance, which is what you want when formulas or add-ins have to actually calculate.

Three traps worth knowing before you commit:

• openpyxl does not calculate formulas. You get the formula string, or the value Excel cached the last time the file was open. A file generated by a script and never opened in Excel has an empty cache, so the read returns nothing.
• pandas discards everything that is not data: formatting, conditional formats, comments, the formulas themselves. You get the numbers and none of the presentation.
• Excel and pandas disagree on rounding, dates and blanks, so a reconciliation rebuilt in pandas can produce a different answer from the workbook it replaced.

The real ceiling is not technical. Python needs a developer: to write it, host it, schedule it, and be reachable when it fails at 6am on the last working day of the month. Handing an analyst a script and a requirements.txt transfers none of that. The most capable approach here needs the resource the small workbook was never going to get.

Best fit: volume, complex joins, testing, a developer who owns it afterwards.

RPA

RPA drives applications the way a person does, which makes it right for the parts either side of Excel. Log into the system, run the report, download it, paste into the workbook, save, upload, email. Enterprise RPA runs that chain with scheduling, credential handling, audit logs and an exception queue somebody is paid to watch, and modern tools talk to Excel through proper interfaces rather than only clicking the UI. Dismissing it is a tell that the writer never ran one in production.

The ceiling is economics, not capability. A developer trusted with a production process costs real money, and a Center of Excellence has a queue prioritised, correctly, by volume and criticality. A workbook one person uses for two hours a week does not clear that bar and never will. The CoE is not wrong to reject it. That is the last mile, a structural outcome rather than anyone's failure of judgment.

Second constraint: RPA drives the workbook without understanding it, so the specification problem does not go away. It moves into a business analyst's document and goes stale.

Best fit: high volume, cross system, funded, owned by a CoE afterwards.

GPA: read the workbook, extract the logic already in it

Guerrilla Process Automation starts from a different premise: the workbook is the specification, so read it.

Debrief is the part of the Guerrilla Bots suite built for that. It opens an Excel file and works out what process it represents from the structure: sheets, headers, formulas, lookups, named ranges, conditional formats, and the mapping tab three sheets over that everything quietly depends on. Then it asks about what it cannot infer, while you work. You run the month end the way you always do and narrate it in plain language. The tool plays back what it thinks each rule is, asks a clarifying question when it is unsure, and lets you answer by pointing at the cell. A red fill on a reconciliation sheet gets read as a flag, because that is what red means there.

What comes out is a stated, reviewable description of the rules the workbook enforces, worth having even if you never automate anything.

Three honest notes.

Debrief is not available yet. I² Recorder ships first and Debrief follows. This describes what is being built, not something you can download today.

It does not remove the need for a person to confirm the rules. It changes who confirms them: the person who runs the process answers directly, instead of sitting through workshops that produce a document nobody updates.

The privacy line is worth interrogating in any tool, including this one. A workbook's logic is in its structure, the sensitive part is its values, and those are separable. Ask which one leaves the machine.

Best fit: recurring work, real logic in the workbook, no developer available, too small to ever get funded.

---

How the approaches compare

One row decides more outcomes than the rest: where the logic lives afterwards. Every approach here produces a working automation on day one. The difference shows up in month fourteen, when the builder has changed teams and the template has moved twice.

---

What this looks like in real work

Several workbooks that have to agree

A system export, a hand maintained tracker, last month's closing file. The job is not merging them, it is deciding what counts as agreement: exact match on reference, or amount within tolerance, or amount and date but not reference because that system truncates it. The tolerance is the business rule and it is almost never written down. See bank reconciliation automation.

The template that shifts when someone asks a new question

Management wants a regional split, so a column goes in. Every hardcoded reference downstream is off by one and every automation written against positions is producing confident nonsense. The defence is a choice made on day one: refer to data by name, never by address.

Month end consolidation

Five files that should be identical and are not. One region renamed a sheet, one added a row above the header, one is still on the old template. The automation has to be tolerant of harmless variation and loud about the rest, and telling those apart is judgment, not parsing.

Data pasted in at a different position every time

Someone copies a block out of a system into a staging tab, and where it lands depends on how much they selected that day. Anything reading a fixed range is already broken. The automation has to find the header row and match columns by name.

One output, three audiences

The same numbers go to the finance director as a summary, to operations with full detail and exceptions flagged, and into a group template nobody may change. The calculation happens once, the formatting three times, and formatting is what pandas throws away. Which is why MIS report automation is harder than it looks.

---

How to choose an approach

Six questions, in order. The first you answer honestly usually settles it.

1. Can you state the rules right now, out loud, in under ten minutes? If yes, any of these will work and you should pick on cost and maintenance. If no, stop. Every hour spent choosing a tool before the rules are stated is an hour you will spend again.

2. Does the job stay inside Excel, or start somewhere else? If the file has to be downloaded from a system, or the output uploaded back into one, VBA and Power Query solve the middle of a problem whose ends are still manual. The wider version is in back office automation.

3. Is the shape of the incoming data stable? Not the values, the shape. If columns get inserted, or a person pastes the data in by hand, anything referring to cell addresses is a liability.

4. What proportion of runs hits something needing a human decision? Under five percent, straight through automation is realistic. Above twenty, you need a design where the automation stops and asks, from the start. Deputy exists for that shape of problem, and any tool you evaluate should have an answer for it.

5. Who maintains this in eighteen months? Say the name out loud. If the honest answer is "the person who built it, who is on a two year contract", pick the approach that keeps the logic readable by somebody else.

6. Would this survive a business case? If it clears the bar, take it to the CoE and get it built properly, with support and an audit trail. If you already know it will not, and most of these workbooks do not, stop resubmitting and pick from the approaches that need no funding.

---