PracticeGuru Learnings · Course
Excel Automation for CA Offices: Power Query & Macros
Bank statements, timesheets, fee registers and software exports: set them up once in Power Query, then just Refresh. Record and safely use macros for the jobs you repeat every month.
New courseFirst lesson freeCertificate
- 13 short lessons12 to 15 minutes each
- About 3 hoursin all, at your own pace
- Bonus filesStart and Finished practice files for every lesson, plus all code
- Who it is forfor senior staff, accountants and partners
What you need before you start
Device: A Windows laptop or PC
- Windows onlyExcel desktop on Windows (Microsoft 365 or 2021)Power Query and macros work best on Windows; Mac Excel is limited.Microsoft Excel
What you will do
Each lesson ends with a "Do this today" box and a number to check your answer against. By the last lesson your office has refreshable queries for its monthly data, three safe macros and a handover sheet with an owner for each.
- Free What Power Query and macros do for a CA officeThe monthly-repeat test, which tool fits each job, and which Excel you need. · 12 min
- 01 Power Query basics: clean a bank statement onceSource-folder habits, the editor, Applied Steps and Refresh. · 15 min 🔒 Paid
- 02 Combine sheets and files the smart wayOne sheet per branch, staff timesheets, sub-folders, columns in a different order. · 15 min 🔒 Paid
- 03 Merge queries: lookups without formulasJoin two lists, and find what is missing with anti-joins. · 15 min 🔒 Paid
- 04 Unpivot: fix reports with months across the topTurn a wide report into a list a pivot table can use. · 12 min 🔒 Paid
- 05 Group By: summaries that refreshTotals by partner, job and age bucket that update themselves. · 15 min 🔒 Paid
- 06 Software exports, Indian dates and numbersClean ledger exports with dd-mm-yyyy dates and lakh-style numbers, and when not to trust a PDF. · 15 min 🔒 Paid
- 07 Make queries last: errors, folder paths and handoverChanged headers, moved folders, type errors and notes for the next person. · 12 min 🔒 Paid
- 08 Macro safety firstBlocked macros, one restricted macro folder, no Undo, test on a copy. · 12 min 🔒 Paid
- 09 Your first recorded macroRecord once, replay every month; .xlsm and the Personal Macro Workbook. · 15 min 🔒 Paid
- 10 Reading and editing a recorded macroThe VBA editor without programming: change a range, find the last row, add notes. · 15 min 🔒 Paid
- 11 Three office macros, built safelySplit a list by client code, save sheets as PDF, protect sheets against accidental changes. · 15 min 🔒 Paid
- 12 Your Excel automation handover sheetFormula, Power Query or macro? An owner, notes and a check for every one. · 12 min 🔒 Paid
Who made this
The PracticeGuru team, who run OneClick Branding for thousands of tax professionals. Steps are written for Windows Excel (Microsoft 365) and tested on it before release; practice files use made-up data only.