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, how to build your first of each, 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: 15 Formulas You Need.

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 and phones: fine for viewing and some refreshing, but not for building folder queries or running VBA macros.

Power Query for accountants: your first query in 10 steps

Take the office bank statement download, the one that arrives each month with bank logos in the first rows, dates as text and blank lines in between. Use a copy, or practice data, the first time.

1. Make one folder for the source file, for example Office Data\Bank, and name one owner for it.
2. In a new workbook: Data, Get Data, From File, then From Text/CSV or From Workbook, and pick the file.
3. Click Transform Data (not Load). The Power Query Editor opens.
4. Home, Remove Rows, Remove Top Rows to drop the bank's header lines.
5. Use First Row as Headers.
6. Remove Rows, Remove Blank Rows.
7. For the date column: right-click, Change Type, Using Locale, Date, English (India). This reads 05-04-2026 as 5 April, not 4 May.
8. Set the amount columns to Decimal Number. Remove any column you do not need.
9. Home, Close & Load. A clean table appears in a new sheet.
10. Next month, save the new download with the same name in the same folder and click Data, Refresh All. Every step runs again.

Each step you click is recorded in the Applied Steps list on the right. You can click any step to see the data at that point, or delete a step you got wrong. The original file is never changed.

Three more queries every office uses

Once the first query works, the same editor handles most other monthly jobs. Power Query for accountants becomes really useful when these three are added:

• Combine a folder of files: Data, Get Data, From File, From Folder, then Combine & Transform. Eight timesheets become one table; next month, drop in the new files and Refresh.
• Merge two lists: Home, Merge Queries. A Left Outer join adds client name and partner to each invoice. A Left Anti join, with the client master first, lists clients with no invoice.
• Unpivot: select the fixed columns (Client, Partner), then Transform, Unpivot Other Columns. A report with April to March across the top becomes a proper list you can pivot and filter.

A fourth, Group By (Home, Group By), turns the cleaned fee register into a summary: total billed, collected and outstanding per partner, or by age bucket. Load it to a sheet, add a chart, and the monthly partner report refreshes with the data. The same approach feeds a client MIS, described in MIS Report Format in Excel for Clients (Free Template).

Your first macro: record it, then make it safe

A macro is a recorded set of actions. Use one when Excel has to do something to a workbook: format a report, split it into files, save sheets as PDF, protect all sheets.

1. Show the Developer tab: File, Options, Customize Ribbon, tick Developer.
2. Save the workbook as Excel Macro-Enabled Workbook (.xlsm). A normal .xlsx file cannot keep macros.
3. Developer, Record Macro. Give it a clear name like FormatBillingReport, store it in This Workbook.
4. Do the steps once: bold the header, set column widths, apply number format, freeze the top row.
5. Developer, Stop Recording.
6. Test it on a copy of next month's report.

Recorded macros often hard-code the exact rows you used, so they break when next month has more rows. Learning to read and make two or three small edits in the VBA editor fixes that. The Excel Automation course covers this edit step and then builds three office macros: one file per client code, save chosen sheets as PDF, and protect or unprotect all sheets.

Macro safety rules for the office

Macros can run any code on your computer. That is why Microsoft now 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: 10 Jobs to Automate Now 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.

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.

The Excel Automation for Tax & Accounts Offices course gives you practice files for every lesson, numbered clicks with "what you should see", ready code to paste if the clicks go wrong, and exact check numbers. 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)