Concept
1. Start with the simplest tool that works
Every step up the ladder adds power, but also setup, maintenance and the risk that nobody else in the office can fix it. Climb only as far as you need.
| Level | Tool | Best at | Runs where |
|---|---|---|---|
| 1 | Formulas | calculations that update live | Excel, Sheets — everywhere |
| 2 | Power Query | importing and cleaning data the same way every time (folders of files, exports, databases) | Excel desktop (Windows; Mac partially) |
| 3 | Office Scripts | automating Excel on the web, connecting to Power Automate | Excel for the web (Microsoft 365 business/education plans) |
| 4 | VBA | automating the Excel desktop app: buttons, file loops, PDFs, Outlook emails | Excel desktop (best on Windows) |
| 5 | Apps Script | automating Google Sheets, Gmail, Drive; scheduled cloud jobs | Google's cloud |
| 6 | Python | big data, many files, APIs, complex logic, jobs that run without Excel open | any computer or server |
2. The decision questions
Ask these in order. Stop at the first "yes".
- Is it a calculation that should update when data changes? → Formulas.
- Is it "import this file/folder and clean it the same way every month"? → Power Query (covered in the Analysis track). No code, and one click on Refresh repeats it.
- Is the file in Google Sheets? → Apps Script.
- Does it need to run in Excel on the web, or be triggered by Teams/Outlook/SharePoint events? → Office Scripts + Power Automate.
- Does it need the Excel desktop app — buttons, printing, PDFs, Outlook, working with the open workbook? → VBA.
- Is it large data (hundreds of thousands of rows), many files, an API, or must it run on a schedule without anyone opening Excel? → Python.
3. Real examples
| Task | Right tool | Why |
|---|---|---|
| GST and totals on an invoice | Formulas | live calculation |
| Combine 30 branch CSVs every month | Power Query | repeatable import, no code |
| Format the daily stock sheet and email it from Excel | VBA | desktop + Outlook |
| Email an alert when stock in a Google Sheet goes below 5 | Apps Script | Sheets + Gmail + trigger |
| Pull gold rates from an API every morning into a sheet | Apps Script (Sheets) or Python | API + schedule |
| Merge 2 years of sales (8 lakh rows) and build a report | Python | data size |
| Shared OneDrive workbook updated by a Power Automate flow | Office Scripts | web + flow |
4. Other factors
- Who will maintain it? If your team knows only Excel, a Power Query or a short VBA macro is easier to hand over than a Python script.
- Mac users? Some VBA (Outlook, Dictionary object) is Windows-only. Office Scripts, Apps Script and Python avoid this.
- Company IT rules? Many offices block macros from email/internet files. Check before you build.
- Combining is normal. Power Query cleans, formulas calculate, a small VBA macro or script handles export and email.
Common mistakes
Writing VBA for something a formula or Power Query does better. Choosing Python because it's popular when the user only has Excel. Forgetting that someone has to maintain the automation after you.