Excel automation:
what it can do, and what it cannot.
Most Excel automation advice is written by people selling automation. This separates the work that genuinely disappears from the work that only looks like it will.
The four things that really do go away
Collecting. Copying figures out of twelve other files into one. This is the single most common job and it is nearly always automatable.
Reshaping. Turning the export nobody can read into the layout the report needs. Same input shape every time, different data.
Repeating. The same calculation across forty sheets where the only thing that changes is a row number.
Checking. Confirming a workbook still follows its own rules: totals add, nothing is hard-coded, no line is double-counted. This one is underrated, because it catches the errors the other three speed up.
The two that do not
Judgement. Deciding what the number should be. A model or a macro can carry a figure across, but it cannot know that a supplier is charging the wrong rate unless somebody told it what the right rate is.
Definition. No automation fixes two systems disagreeing about what "revenue" means. If the definition is unsettled, automating it makes the disagreement faster and harder to see.
That second one is worth sitting with. A large share of the work this practice does is not automation at all. It is finding out that two departments meant different things, and then agreeing it once.
Fix the definition, then automate it.
Automating a process nobody has agreed on produces the wrong answer faster and with more confidence. The unglamorous step is worth more than the clever one, and it is the step that gets skipped when somebody buys a tool first.
Power Query is enough more often than you would think
Power Query is built into Excel and it is the most underused thing in it. It records the steps you take to clean and combine data, then replays them on refresh. If the job is the same reshaping every month with different files, it is the whole answer.
It is also inspectable. The steps are listed, in order, in a panel. Somebody else can read them. That alone makes it better than a macro, which hides the same logic in code nobody can see.
We have told more than one client that they did not need us for this, and shown them the button. It is a shorter conversation than selling them a project.
- When it needs to decide something, rather than transform it.
- When several systems have to talk to each other and stay in step.
- When it has to run unattended, on a schedule, without anyone opening a file.
- When a rule has to be enforced, not just repeated. At that point the spreadsheet is being asked to be an application, and it will eventually say no.
- 01Reshaping data the same cleaning steps, every month, on a new file
- 02Combining files appending twelve exports into one table
- 03Deciding something where the logic needs a judgement, not a transformation
- 04Enforcing a rule where a limit has to be held, not merely repeated
Past the second mark the spreadsheet is being asked to be an application, and it will eventually say no. That is the point to stop adding to it.
Before you automate anything
Three questions, in this order. If the answers are not numbers, the project is a guess.
- How often does it happen? A monthly job that takes an hour is not worth a project.
- How long does it take, and who does it? Multiply the two. That is the annual cost of the way it works now.
- What does a mistake cost? This is the one people leave out. A process that is slow but checked is often cheaper than a fast one nobody checks.
And the worksheet underneath it
Before a workbook is automated it is worth knowing what is in it. Most inherited workbooks contain a wrong total, a hard-coded override, or two figures that stopped agreeing some time ago and nobody noticed because the summary is on a different tab.
Automating that preserves the error and makes it harder to find. A same-day audit first is the cheaper order, which is why the two services sit next to each other.
Questions people actually ask
Collecting and reshaping data from other files, repeating a calculation across many sheets, assembling a report that always has the same shape, and checking that a workbook still follows its own rules. The things that cannot be automated are the judgement calls: what the number should be, and whether the number is right.
It is a large part of it, and it is where most businesses should stop. Power Query records the steps you take to clean and combine data and replays them on refresh. If the job is 'every month, same reshaping, different files', Power Query is the answer and you do not need Python or a developer.
When the logic needs a decision rather than a transformation, when the work spans several systems that need to talk to each other, when it has to run unattended on a schedule, or when a rule has to be enforced rather than repeated. At that point the spreadsheet is being asked to be an application.
They work until they do not. A macro is invisible code with no record of why it exists, it breaks silently when a column moves, and only one person usually understands it. We have replaced more than one and inherited fewer good ones. If a macro is genuinely the right answer, it should be written down and versioned like anything else.
For most businesses, yes. Excel is a good tool and the problem is almost never the tool. The problem is a workbook that accumulated three people's assumptions over four years. Fixing that is cheaper than replacing it and usually comes first.
Measure it before you build anything. How often does it happen, how long does it take, and what does a mistake cost. A monthly job that takes an hour is not worth a project. A weekly job that takes a day and gets checked twice is.
If it is more than a workbook
When the work spans several systems, or has to run without anybody opening a file, it has stopped being an Excel problem. That is the point to look at the process rather than the spreadsheet.
- Spreadsheet audit, which finds what is wrong before anything is automated.
- Reporting and reconciliation, for when two systems disagree.
- Business process automation, the next article, for work that is larger than one file.
Tell us what is not working.
Describe the problem in a few lines. You get a plain answer about whether it is worth fixing and what it would take, before any money changes hands.