Published 7 July 2026

Xero Trial Balance in Excel with Power Query: A Step-by-Step Guide

The trial balance is the workhorse report: management accounts, lead schedules, consolidations and year-end files all start from it. Xero produces a perfectly good TB on screen — but the moment you need it in Excel, monthly, and refreshable, you are into workflow territory that Xero does not really cover.

This guide walks through doing it properly with Power Query, the query engine built into every copy of Excel since 2016. We cover three levels: cleaning a single exported TB, assembling a rolling 12-month TB from a folder of exports, and connecting to a live synced database so the exports disappear altogether (the approach Flow takes). Each level is a genuine step up in reliability, and the first two cost nothing but time.

Why Power Query rather than formulas

If you currently paste TB exports into a tab and point VLOOKUP or SUMIFS at them, you already know the failure modes: a new account code shifts every row below it; a renamed account breaks the lookup silently; and each month means re-pasting and re-checking. Power Query fixes the structural problem because it treats the TB as data, not as a grid of cells:

Transformations (remove rows, rename columns, change types) are recorded once and replayed on every refresh.
New accounts simply become new rows — nothing shifts, nothing breaks.
The query output loads to a Table or the Data Model, which pivot tables and SUMIFS can reference stably.

Level 1: Clean a single Xero TB export

Export the trial balance from Xero (Accounting → Reports → Trial Balance, then export to Excel). Open a separate workbook — your reporting file — and pull the export in:

Data → Get Data → From File → From Excel Workbook, select the export.
In the Navigator, pick the sheet and click Transform Data to open the Power Query editor.
The export arrives with report furniture: title rows above the headers, section headings, and a totals row. Remove the top rows (Home → Remove Rows → Remove Top Rows) until the header row is first, then Use First Row as Headers.
Filter out non-account rows: filter the account column to remove nulls and the "Total" row.
Set types: account name/code as text, Debit and Credit as decimal numbers. Blank debits and credits arrive as null — replace nulls with 0 (Transform → Replace Values) so arithmetic behaves.
Add a single signed movement column rather than working with separate debit and credit columns: Add Column → Custom Column, [Debit] - [Credit]. Debits positive, credits negative — one column your reports can sum.

The generated M code will look something like this:

let
    Source = Excel.Workbook(File.Contents("C:\Reports\TB-Jun-2026.xlsx"), null, true),
    Sheet = Source{[Item = "Sheet1", Kind = "Sheet"]}[Data],
    Trimmed = Table.Skip(Sheet, 4),
    Headers = Table.PromoteHeaders(Trimmed, [PromoteAllScalars = true]),
    Rows = Table.SelectRows(Headers, each [Account] <> null and not Text.StartsWith([Account], "Total")),
    Types = Table.TransformColumnTypes(Rows, {{"Debit", type number}, {"Credit", type number}}),
    NoNulls = Table.ReplaceValue(Types, null, 0, Replacer.ReplaceValue, {"Debit", "Credit"}),
    Signed = Table.AddColumn(NoNulls, "Net", each [Debit] - [Credit], type number)
in
    Signed

Next month, overwrite the export file with a new one using the same filename, and Data → Refresh All replays every step. You have removed the re-pasting, but not the exporting.

Level 2: A 12-month TB from a folder of exports

For a trend or a monthly pack you need one TB per month-end. The Power Query pattern for this is the Folder query:

Keep one folder per organisation, containing one export per month, named consistently — TB-2026-01.xlsx, TB-2026-02.xlsx, and so on.
Data → Get Data → From File → From Folder, point at the folder, and choose Combine & Transform. Power Query builds a sample transformation from one file and applies it to every file in the folder.
Apply the Level 1 clean-up steps inside the sample query.
The combined output includes a Source.Name column — parse the month out of the filename (Add Column → Custom Column, e.g. Date.FromText(Text.BetweenDelimiters([Source.Name], "TB-", ".xlsx") & "-01")) and you have a long, tidy table: one row per account per month.

From there a pivot table gives you accounts down the side, months across the top — a rolling TB that grows by dropping one more file in the folder each month.

This is a solid, zero-cost system, and for a single organisation refreshed monthly it may be all you need. Its weaknesses are the ones baked into the inputs: someone still has to run the export every month (twelve times a year, per organisation); historic months are frozen as files, so a back-dated journal into March is invisible unless you remember to re-export March; and multi-entity work multiplies the folders.

Level 3: Connect to a live synced database

The third level removes the export step entirely. A sync service maintains a copy of your Xero data — including a trial balance snapshot as at every month-end — in a cloud SQL database, updating it automatically. Excel connects once and refreshes forever.

The connection is native Excel, no add-ins:

Data → Get Data → From Database → From SQL Server Database.
Enter the server and database name, and sign in with database credentials (with Flow these are read-only credentials unique to you, scoped to your organisations only).
Pick the trial balance view in the Navigator and load or transform as usual.

The equivalent of the entire Level 2 setup is one short query:

let
    Source = Sql.Database("your-server.database.windows.net", "your-database"),
    TB = Source{[Schema = "dbo", Item = "TrialBalance"]}[Data],
    LastYear = Table.SelectRows(TB, each [SnapshotDate] >= #date(2025, 7, 31))
in
    LastYear

Every month-end for every connected organisation is already a row, so there is no folder to maintain and nothing to re-export. Because the sync re-verifies historic months, that back-dated March journal shows up in March, where it belongs — the failure mode that quietly undermines the folder system. Power Query also folds the filtering into SQL, so Excel only downloads the rows you ask for; a refresh is seconds, not minutes.

Pitfalls to design around (whichever level you choose)

Debits, credits and signs. Decide once whether your reports consume separate debit/credit columns or a single signed net column, and make the query produce it. Mixing conventions between tabs is how a balance sheet stops balancing.
Monthly movement vs year-to-date. A Xero TB shows both. Be explicit about which your pack uses — P&L packs usually want monthly movement columns; a balance sheet is cumulative by nature. If you build YTD by summing months, check it across a year-end, where P&L balances roll into retained earnings.
New and renamed accounts. Power Query handles new accounts gracefully (new rows), but your report layer must too: a pivot table picks them up on refresh, whereas a fixed-layout SUMIFS sheet needs a mapping table from account code to report line. Map by code, not by name — names get edited.
The current month. A month-end TB for a month still in progress changes daily. Treat the current month as provisional in the layout, and make sure your refresh actually re-reads it rather than caching it.
Blank vs zero. An account with no activity may be absent from one month's export and present in the next. In the long-table pattern this is fine (absence is a missing row, and pivots show blank), but if anything downstream divides by movement, guard against both null and 0.

Where Flow fits

Flow is the Level 3 service: it syncs your Xero organisations into an Azure SQL database — monthly trial balance snapshots, invoices, credit notes and the chart of accounts — automatically every day, and provides an Excel Power Query template plus read-only credentials, so the connection steps above are done for you. Plans start at £20/month for up to 5 organisations with 2 years of trial balance history; Plus (£40/month) raises that to 30 organisations with configurable 1–5 year history and real-time refresh, and Premium (£60/month) adds tracking category breakdowns across up to 60 organisations. There is a 14-day free trial, no credit card required.

Start at the level that matches your workload: Level 1 if you touch a TB occasionally, Level 2 if you run one organisation's monthly pack, and Level 3 when the exports themselves have become the bottleneck.