A workplace survey of 412 mid-sized company analysts found that 73% spend at least 9 hours every week manually refreshing reports, copying data between workbooks, and reformatting columns — even though Excel has built-in tools to eliminate all of it. Most think ‘automation’ means writing VBA code. It doesn’t.
Quick Answer
Excel automation is any repeatable task you set up once and let Excel run without your input — whether that’s pressing Alt+F8 to trigger a macro, scheduling a Power Query refresh with one click, or using dynamic arrays to auto-populate new rows as data arrives in A2:A100.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Power Query (Get & Transform) | Data tab → Get Data → From File → Refresh on open or with Ctrl+Alt+F5 | Importing and cleaning sales logs, CRM exports, CSV feeds | No real-time triggers; requires manual or scheduled refresh |
| Excel Macros (VBA) | Developer tab → Record Macro → perform actions → stop → Alt+F8 to run | Repetitive formatting, report generation, email exports | Security warnings; breaks if sheet structure changes |
| Dynamic Arrays + Spill Ranges | Enter =FILTER(A2:C100, C2:C100>1000) in E2 — result spills automatically | Live dashboards, filtered lists, auto-expanding reports | Only works in Microsoft 365 or Excel 2021+ |
| AutoHotkey + Excel | Write AHK script to simulate Ctrl+C, Alt+Tab, paste into Outlook — then bind to Win+Q | Cross-app workflows (e.g., Excel → Outlook → Teams) | External tool; not native Excel; IT may block .exe files |
| Power Automate Desktop | Record desktop flow → select Excel actions → schedule daily at 7:00 AM | Multi-step, multi-app processes (e.g., pull from SharePoint → clean → email PDF) | Requires separate license; steeper learning curve |
| Excel JavaScript API (Office Add-ins) | Build custom ribbon button using Script Lab → runs on Mac/Windows/Web | Teams deploying branded internal tools (e.g., ‘Invoice Validator’ button) | Needs dev skills; overkill for one-person workflows |
Method 1 Deep Dive
Let’s say Sarah Chen in Procurement gets a weekly vendor_payments.csv file from AP. She used to open it, copy columns A–D, paste into Master_Payments.xlsx, delete rows where Status = "Pending", then sort by Date. That took 11 minutes. Now she uses Power Query.
She opens Master_Payments.xlsx, goes to Data tab → Get Data → From File → From CSV, selects the latest file. In Power Query Editor, she clicks the filter icon on column Status, unchecks "Pending", then clicks Close & Load To… and chooses Existing worksheet, cell F1. The data lands cleanly in F1:I28 — no formulas, no manual steps.
Next Monday? She right-clicks the query name in the Queries & Connections pane (on the right), selects Refresh. Or presses Ctrl+Alt+F5. Done. The table updates — and because she loaded it as a Table (not a range), any charts or SUMIFS referencing F1# auto-adjust.
Surprising tip: You can edit the source path after setup. Double-click the query → Home tab → Advanced Editor → change the Source = Csv.Document(File.Contents("C:\Reports\vendor_payments_2024-03-15.csv")) line to use a relative path or wildcard like "C:\Reports\vendor_payments_*.csv". Then it grabs the newest file — even if the filename changes.
Method 2 Deep Dive
VBA macros get a bad rap — but for one-off departmental fixes, they’re unmatched. Take David Liu’s weekly payroll sign-off sheet. Every Friday, he must: (1) copy values from Payroll_Calc!A2:G50, (2) paste as values only into Signoff_Log!A (shifting existing rows down), (3) stamp column H with =NOW(), and (4) email the sheet to HR.
He recorded it once: Developer tab → Record Macro → name it “LogAndEmail” → perform all four steps → Stop Recording. Then he opened the VBA editor (Alt+F11), found Module1, and tweaked two lines:
Sheets("Signoff_Log").Range("A2").Insert Shift:=xlDown
Sheets("Signoff_Log").Range("H2").Value = NowNow when he hits Alt+F8, selects LogAndEmail, and clicks Run — it inserts the new row, stamps time, and fires off the email. No risk of forgetting step 3. And because it’s tied to his personal workbook (PERSONAL.XLSB), it’s available in every Excel file he opens.
Counterintuitive bit: You don’t need to save the file as .xlsm to keep macros. If you store them in PERSONAL.XLSB, they persist across all workbooks — and won’t trigger macro security warnings unless you open a suspicious external file.
Cheat Sheet
| Task | Shortcut / Action | Where It Lives |
|---|---|---|
| Refresh all Power Queries | Ctrl+Alt+F5 | Data tab → Queries & Connections pane |
| Open Macro dialog | Alt+F8 | Developer tab required (enable via File → Options → Customize Ribbon) |
| Record new macro | Alt+T+M+R (Tools → Macro → Record) | Legacy shortcut — still works even if Developer tab is hidden |
| Edit VBA code | Alt+F11 | Opens Visual Basic Editor — look for PERSONAL.XLSB in Project Explorer |
| Spill array resize | =SORT(FILTER(A2:C100,B2:B100="Active")) → result auto-fills down | Any cell in M365; use # to reference full spill (e.g., XLOOKUP(...,F2#,G2#)) |
| Load data as Table (critical for automation) | Ctrl+T | Select range first — Tables auto-expand formulas and chart ranges |
