6.1 Automating Excel: choosing your route

What you'll be able to do

Pick the right route for automating a task, for a stated reason, before writing a line of anything. The three routes are Power Query, Office Scripts and VBA. Choosing wrong costs weeks, and this fifteen-minute map prevents it.

Before you start

Bring one genuinely repetitive Excel task from your own month. You will route it, on paper, by the end.

The work

1. Same either way: ask the first question. Is the repetitive part about shaping data: importing, cleaning, combining, reshaping? Then the answer is Power Query, which you have already learned, and there is no code at all. The recorded recipe plus a refresh is automation. Reaching for a script to do what a query does is the classic wrong turn, and most "I need a macro" requests end right here.
2. Same either way: some tasks are actions rather than shaping. Format this, insert that, produce the monthly file. Then the choice is between the two script routes, and they differ in where they run. Office Scripts are written in TypeScript, stored in the cloud, and run in Excel on the web and in current desktop Microsoft 365. They can be scheduled through Power Automate. They are the modern default, and one of the five paid topics from Getting Your Footing. VBA is the older route. It runs on a desktop only, works offline, and is still required for inherited .xlsm files, for user forms, and for driving other desktop apps.
3. Same either way: route by constraint, not by preference. If it must run on the web, or on a schedule with nobody's laptop switched on, use Office Scripts. If it must run offline, or maintain the macros a predecessor left, or drive Word from Excel, use VBA. If neither constraint applies, use Office Scripts, because it is the better-supported future.
4. Same either way: check what the licence allows before promising anything. Office Scripts needs a work or school Microsoft 365 plan, and personal plans and one-time purchases do not include it. VBA comes with desktop Excel, but many organisations block macro files completely, and mail filters remove .xlsm attachments. The environment can rule out either route, so ask before you build.
5. Same either way: route your own task now, out loud. "The repetitive part is shaping, so a query." Or "it is actions, on the web, on a schedule, so a script." Or "it is an inherited .xlsm, so VBA." One sentence with a reason is what this topic promised. The next three lessons teach the craft of each route.

Check yourself