Power Query and Macros for Accountants: Automate Excel

Power Query for accountants solves one specific problem: the Excel job you did last month, and will do again next month, step for step. Cleaning the same bank statement download, copying eight staff timesheets into one sheet, stacking four branch fee registers. This guide shows which jobs suit Power Query, which suit macros, what a monthly job looks like before and after, and the safety rules that keep macros from becoming a risk in your office.

Both tools are 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.

Which jobs to automate: the monthly-repeat test

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

1. Does it come back every month or every 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, for example by asking for the same export format every time.

Formula, Power Query or macro?

Think of the three tools as three different staff members.

• A formula does one calculation next to the data, such as days since an invoice or a client name looked up from a code. It recalculates by itself, but it cannot clean or reshape a messy file.
• Power Query is the person who tidies the data before anyone uses it: removes junk rows, fixes dates, joins two lists, stacks twelve files. It writes down every step and repeats them on demand. It never touches the original file, so a mistake costs you nothing.
• A macro is the person who does things to the workbook itself: formats, splits, saves, protects. It is powerful, which is also why it needs rules. A macro can delete or overwrite data, and there is no undo.

A good rule of thumb: if the job is about getting clean data, use Power Query. If it is about doing something with the finished workbook, use a macro.

Seven of these ten are Power Query jobs. That is typical, which is why most offices should learn Power Query before macros. For the formula side, see Excel for Chartered Accountants: The Formulas You Need.

One warning before you start. Power Query does not fix weak Excel basics. If staff still struggle with lookups, SUMIFS or a pivot table, an automated query only produces wrong answers faster.

Which Excel you need

• Windows desktop Excel, Microsoft 365: the full feature set. Build here.
• Excel 2016, 2019 and 2021 for Windows: Power Query is on the Data tab, in the Get & Transform group. Most features are there; some screens may differ.
• Mac: only Excel for Microsoft 365 has Power Query, with fewer data sources. The macros most offices use are written for Windows.
• Excel for the web: can import, edit and refresh Power Query queries, but cannot create, run or edit VBA macros. Phones: fine for viewing. For the full workflow in this guide, including macros, use Windows desktop Excel.

Power Query for accountants: before and after

The easiest way to see what Power Query does is to look at one monthly job, before and after. Take the office bank statement download: the file that arrives each month with bank logos in the first rows, dates stored as text and blank lines in between.

The same story repeats across the office. Eight staff timesheet files become one table with a single Refresh. Four branch fee registers stack into one list. A fee report with April to March across the top turns into a proper list that a pivot table can use. A partner-wise summary of billed, collected and outstanding amounts refreshes with the data. The same approach feeds a client MIS, described in MIS Report Format in Excel for Clients (Free Template).

How does it work? Power Query writes down every step you take on the data, in order, and repeats them on demand. You can look at any step to see the data at that point, and remove a step you got wrong. That written record is also what makes the work checkable by someone else.

The catch is the first build. Power Query has its own editor and its own words: applied steps, merge, join kind, unpivot, group by. Most people give up when they try it on a live office file with no guide, a date reads wrong, and nobody knows which step caused it. Two weeks of practice on safe data is what made the difference for Swati.

One export many offices repeat every year is the client trial balance for finalisation. If that is your biggest repeat job, our own SignReady imports the trial balance from Tally or Excel and runs the standard checks, so you may not need to build that query yourself.

Where macros fit, and what can go wrong

A macro is a recorded set of actions. Use one when Excel has to do something to a workbook: format the monthly billing report, split a work list into one file per client code, save chosen sheets as PDF, protect all sheets against accidental changes. You do the steps once while Excel watches, and the macro repeats them with one button.

That sounds simple, and recording is simple. The problems come later:

• Hard-coded rows. A recorded macro often remembers the exact rows you used. Next month the report has more rows, and the totals land in the wrong place.
• No undo. A macro can delete or overwrite data, and Ctrl+Z will not bring it back.
• One person knows it. The macro works while its author is in the office. When that person leaves, nobody knows what it does or how to fix it.
• Code from outside. Code copied from the internet or written by AI can do more than you think, on real client files.

Every one of these can be managed. A macro that is tested on a copy, edited so it finds the last row by itself, and written down with an owner and a rollback note is a safe office tool. A macro without those is a risk sitting in a shared folder.

Macro safety rules for the office

Macros can run code within the permissions of the user who runs them. That is why Microsoft Office on Windows blocks macros in files from the internet, such as email attachments, by default, and shows a security risk banner instead. Treat that banner as a feature, not a nuisance.

An AI tool can write or explain macro code well if you describe the workbook layout. Never paste real client data into the chat. Our AI Automation for CA Firms: Office Jobs to Automate First guide covers the wider office automation picture, beyond Excel.

Make it last: queries that survive next month

A query built in one afternoon can break in month three. The usual causes are simple: a column header changed in the source file, text appeared in a number column, or someone moved the folder. Three habits prevent most breaks.

• Write down a check number when you build the query: the row count and the total of the amount column. After every Refresh, compare.
• Do not hide errors with Remove Errors. Find out which rows failed and why.
• Keep a handover sheet in each workbook: what each query or macro does, the source folder, the owner, and the monthly check.

The handover sheet matters most in a team. In many offices one keen person builds the queries, then moves on, and the rest of the office goes back to doing the job by hand because nobody else understands it.

If a job is a one-off or changes every time, it is not worth automating. Keep using formulas, taught in the Excel for Tax Practice course. If it comes back every month in the same shape, build it once.

What the course gives you

This article stops at the "what" and the "why". The Excel Automation for Tax & Accounts Offices course covers the "how": practice files for every lesson in start and finished versions, numbered clicks with "what you should see", the Indian date and number fixes for software exports, folder paths that survive a move, and a handover sheet for every query and macro. Start with the free first lesson on what Power Query and macros do, and list your own monthly jobs as you read.

Questions people ask

Sources

• Microsoft Support, About Power Query in Excel
• Microsoft Support, Quick start: Create a macro
• Microsoft Learn, Macros from the internet are blocked by default in Office (updated July 2026)