What Power Query and macros do for a CA office

Every office has jobs like these: the data changes, the steps do not. This course teaches two tools already inside your Excel. Power Query cleans and joins data, then repeats the cleaning with one Refresh. Macros repeat actions on a workbook, such as formatting, splitting and saving, with one button.

If you did it last month and you will do it next month, write it down once and let Excel repeat it.

The monthly-repeat test

Before you automate anything, ask four questions about the job:

1. Does it come back every month or week?
2. Does the data arrive in the same layout each time?
3. Does it take more than 15 minutes by hand?
4. Are the steps fixed rules, with no judgement?

Four yeses: automate it. A "no" to question 4 means a person must still decide, so automate only the boring part around the decision. A "no" to question 2 means fix the source first.

Which tool fits which job

• Formula: one calculation next to the data, such as days since an invoice (see Excel for Tax Practice).
• Power Query: the data needs cleaning, combining or reshaping before you can use it. It never changes the original file.
• Macro: Excel must do something to a workbook: format, split, save as PDF.

Ten monthly jobs from a typical office:

Seven of the ten are Power Query, so the course starts there. Macros come after the lesson Macro safety first.

Which Excel you need

• Windows desktop Excel, Microsoft 365: the supported path. It has every Power Query feature, and all steps are written for it.
• Excel 2016, 2019 and 2021 for Windows: Power Query is there, on the Data tab in the Get & Transform group. Microsoft says these versions have a large subset of the features, not all. If your screen differs from a step, check your Excel version.
• Excel for the web: can view and refresh queries, but cannot import from a folder on your computer, and cannot create, run or edit VBA macros. Use desktop Excel.
• Excel on phones and tablets: Power Query is not supported.

Quick check: on the Data tab, look for Get Data in the Get & Transform group.

How each lesson works

There are no videos. Every hands-on lesson gives you five layers instead:

1. Numbered clicks for Windows Excel, Microsoft 365, each followed by "What you should see", so you know you are on the right screen.
2. Practice files with made-up data: a Start file, and a Finished file with the correct result as values (no live queries, as it was built without Excel).
3. Ready code to paste if the clicks go wrong, with the folder path in one cell you edit.
4. Check your answer: exact counts and totals, plus the common wrong number and what it means.
5. Do this today: one real office job to try.

Our promise on the steps

The steps are written for and tested on Windows Excel, Microsoft 365. Before the course opens, a staff member works every lesson on a clean Windows computer: each click checked against the screen, each query and macro pasted and run, each check number matched. Menus do move over time; if a button is not where we say, look nearby and tell us.

What you will have at the end

A bank statement that cleans itself with Refresh, combined timesheets and branch registers, a billing list with missing-client checks, a pivot-ready fee report, summaries by partner and age, three safe office macros, and a handover sheet naming the owner of each. All built on practice data first, and on a copy of real data only after that.