Funcognito — Explore and learn anything, you got this!
core Estimated learning time: 3 h

6.1 Automating Excel: choosing your route

You pick Office Scripts, VBA or Power Query for a real reason, not by habit.

Before:04. Excel — Cleaning & Power Query

Excel has three automation routes — Power Query for data shaping, Office Scripts for cloud-friendly tasks, VBA for the desktop and legacy files — and this topic is about choosing between them for a reason. It opens the automation module because the wrong route costs weeks. The rule of thumb taught here: if the task is shaping data, Power Query wins before any code is written. The common confusion is assuming automation means macros; recording a macro to do what a refreshable query does natively is the habit this topic exists to break.

Work through these

  • Power Query for data shaping — usually the right answer first

    For anything that is reshaping or cleaning data, the query tool is almost always the right first answer and needs no code. Reaching for a script before trying it is the common mistake.

  • Office Scripts: cloud, cross-platform, works in Excel on the web

    The cloud scripting option runs in the browser version and on any platform, which makes it the portable choice. Its capabilities are narrower and its reach is wider.

    Office Scripts documentation · Docs

  • VBA: desktop only, but still required for some interop and legacy files

    The older macro language runs only on the desktop and is still required for certain older files and integrations. It is not obsolete, but it is no longer the default.

    Differences between Office Scripts and VBA macros · Docs

  • Microsoft's own guidance on the difference

    Microsoft publishes its own comparison of these routes, and reading it is faster than arguing about them. The right choice depends on where the work has to run.

    Office Scripts documentation · Docs

Practice file

Opens in Excel. Every name, number and address in these files is made up. No account needed to download.

Sign in to keep your progress.

Free resources

We haven't checked most of these for screen reader use yet.

Links last checked 29 Aug 2026.

Stuck here?

Ask a mentor. A real person answers, and they can see exactly which topic you're on. Usually within a couple of working days.

Checking your session…

Topics shown in module order.