Does the Trial Balance use the same As-of-date convention as the Balance Sheet?

The task pane Trial Balance is an As-of Date report: pick a single period-end and the full TB lands with debit, credit, YTD movement, and closing-balance columns. The cell-level XO.BALANCE pattern needs two date cells, not one — XO.BALANCE on a Balance Sheet account ignores the start date and returns the as-of balance at the end date, but on a P&L account (Revenue, Expense) it returns activity between the two dates per FUNCTIONS.md. Set start = fiscal-year start and end = as-of date so P&L rows show YTD movement and BS rows show the as-of balance — the same shape Xero produces.

Can I build a multi-period Trial Balance (current month vs prior month vs year-end) in one workbook?

Yes. Insert the TB via the task pane for one period-end, then add side-by-side workbook columns referencing a different period per column. For Balance Sheet accounts a single as-of date per column is enough. For P&L accounts each column needs its own period-start AND period-end pair: fiscal-year start through current month-end, fiscal-year start through prior month-end, and so on — XO.BALANCE on a P&L account returns activity between the two dates, so the start argument is what makes each column a YTD figure. All columns reference the same chart of accounts spilled from XO.COA in column A and refresh independently when Refresh is clicked.

Can I filter the Trial Balance by branch, department, or other tracking categories?

Not via the task pane Trial Balance wizard — TABLES.md is explicit that the standard TB report does NOT support tracking filters or Compare periods (use the Profit & Loss or Balance Sheet reports for tracking-filtered analysis). For a tracking-filtered TB in cell-level form, however, XO.BALANCE does accept up to two tracking-category arguments — for example =XO.BALANCE($A$2, "4000", $D$1, $E$1, "Region", "North") returns the North-region activity for account 4000 (a Revenue account) between $D$1 (fiscal-year start) and $E$1 (as-of date). Combining XO.COA (account list) with category-filtered XO.BALANCE columns gives you a per-branch TB built in Excel from the live ledger, even though the wizard itself does not expose the filter.

How does the Trial Balance handle multi-currency organizations?

The Trial Balance is generated in the organization’s base currency — Xero handles the underlying currency conversion of foreign-currency transactions when balances are computed. Account-level currency breakdowns are visible in the inserted task pane report. For cell-level workbooks, spill the chart of accounts with =XO.COA($A$2) — column 12 (CurrencyCode) shows each account’s native currency, so you can filter the spilled table to flag non-base-currency accounts.

How does the Trial Balance tie to the Profit & Loss and Balance Sheet?

The TB is the source — every line on the P&L (revenue + expense accounts) and every line on the Balance Sheet (asset + liability + equity accounts) appears on the TB at the same as-of date. Revenue minus expense rows on the TB sum to the Net Income line on the P&L. Asset rows minus liability and equity rows balance to zero on the TB. The XO.BALANCE formula and the task pane reports all read the same underlying ledger, so the three reports always reconcile.

Xero Trial Balance in Excel — Live Workpapers

What it is

The Trial Balance is the accountant's workhorse — a single-page listing of every General Ledger account with its debit, credit, and period-end balance. It is the first report opened at month-end close, the first attachment in an audit pack, and the anchor against which every other report must reconcile. Profit & Loss and Balance Sheet both derive from it.

In Xero the Trial Balance is generated as of a chosen date, with YTD movements alongside the closing balance for each account. Bookkeepers and external auditors live in this report during close week — they reconcile sub-ledgers against it, mark off accounts as reviewed, and attach commentary at the account level. The challenge is that Xero produces one TB per as-of date, while the actual workpaper template usually needs current month next to prior month next to year-end.

Why analysts want it in Excel

Excel is where the Trial Balance becomes a real workpaper. The standard close template lays out two or three consecutive period-ends side by side with variance columns, highlights material movements, and ties each section to a sub-ledger reconciliation tab. Most firms keep commentary cells, reviewer-sign-off columns, and adjusting-journal worksheets alongside the TB so the entire close binder lives in one workbook.

Once the TB data layer is live, those workpapers stop being one-off artefacts. The template gets reused every period — the same review columns, the same sub-ledger ties, the same materiality thresholds — with the actual balances refreshed in seconds rather than rebuilt by hand. Audit firms in particular gain enormous leverage: one TB workpaper template applied to every client engagement, refreshed on demand without breaking the layout.

Why the manual method breaks

Generate the TB in Xero for each as-of date, export to CSV, paste into Excel, line up account codes — repeat for every period and every entity in the close cycle.
Account codes silently mis-align if Xero adds a new account mid-period or you forget to copy the new row into the workpaper — the totals still balance, but a balance is missing from review.
Adjusting journals posted after the export are invisible until the next refresh; sign-off columns can be ticked against stale figures.
Multi-period comparison requires re-exporting prior periods every time a late journal lands in a closed month — most firms simply accept the drift.
Tracking-category drill-down (branch TB, departmental TB) requires a completely separate export per filter combination.
Audit pack assembly multiplies the effort by the number of clients: ten engagements means ten manual TB rebuilds every month, every quarter, every year-end — most of which never get reused because the next period has its own export to chase down.

The live add-in method

XO Report covers the Trial Balance two ways. Pick the surface that matches your workpaper layout.

Primary: insert the full Trial Balance via the task pane. Open Tables and Reports, choose Trial Balance (it is an As of Date report — pick a single period-end), then click Insert. The full TB lands on a new sheet with debit, credit, and YTD movement columns alongside the period-end balance — exactly the layout Xero presents in its viewer.

Complementary: compose a custom workpaper with XO.COA + XO.BALANCE. When your firm's workpaper template differs from the standard TB layout — extra reviewer columns, custom groupings, sub-ledger ties — spill the chart of accounts in one column with XO.COA and pull each balance with XO.BALANCE. The workpaper structure stays under your control; only the live balances come from Xero.

# Step 1: set up date cells (XO.BALANCE needs both arguments)
#   $D$1 = period start (fiscal-year start for a YTD TB, or month
#          start for a single-period TB). XO.BALANCE IGNORES this
#          for Balance Sheet accounts and USES it as the from-date
#          for P&L accounts.
#   $E$1 = period end / as-of date.

# Step 2: spill the full chart of accounts in cell A2
=XO.COA($A$2)
# Returns 12 columns: Org ID, Org Name, Code, Name, Type, Class,
# Status, Description, TaxType, ReportingCode, ReportingCodeName,
# CurrencyCode. Account Code lives in column C, Class in column F.

# Step 3: in column N (next to the spilled COA), pull each balance.
# Drag the formula down to match the spilled COA height.
=XO.BALANCE($A$2, C2, $D$1, $E$1)

# How the same formula handles both account classes (per FUNCTIONS.md):
#   - Balance Sheet accounts (ASSET, LIABILITY, EQUITY): start
#     date is IGNORED — returns the as-of balance at $E$1.
#   - P&L accounts (REVENUE, EXPENSE): returns ACTIVITY between
#     $D$1 and $E$1 — set $D$1 to your fiscal-year start to get
#     the YTD movement that ties to the task pane TB's YTD column.
#
# Add subtotal rows by account Class (ASSET, LIABILITY, EQUITY,
# REVENUE, EXPENSE) using SUMIFS against the spilled COA's column F.

XO.COA spills the entire chart of accounts as a 12-column table. Column C is the GL account code — the value XO.BALANCE needs as its second argument. Lock $A$2 (org reference), $D$1 (period start), and $E$1 (period end) with absolute references so the formula can be dragged down without rewriting.

The two date cells are how a single dragged formula covers every account class correctly. For Balance Sheet accounts XO.BALANCE ignores the start argument and returns the as-of balance at $E$1. For P&L accounts (Revenue, Expense) it returns activity from $D$1 through $E$1 — set $D$1to the fiscal-year start and the P&L rows show the YTD movement that ties to the task pane TB's YTD column. This matches how the wizard-inserted TB renders both balance and YTD columns side by side.

The pattern is dynamic: when Xero adds a new account, the next refresh of XO.COA grows the spilled range, and the adjacent XO.BALANCEcolumn should be extended once to cover the new rows. Excel's structured references or a helper table make this self-extending.

For the standard TB layout (debit/credit/movement columns) use the task pane wizard above — it returns the exact format Xero generates. The composed-formula pattern is for custom workpapers where your template differs from the default. Both approaches read the same underlying balances and reconcile to the same totals.

Related docs

Frequently asked questions

Does the Trial Balance use the same As-of-date convention as the Balance Sheet?: The task pane Trial Balance is an As-of Date report: pick a single period-end and the full TB lands with debit, credit, YTD movement, and closing-balance columns. The cell-level XO.BALANCE pattern needs two date cells, not one — XO.BALANCE on a Balance Sheet account ignores the start date and returns the as-of balance at the end date, but on a P&L account (Revenue, Expense) it returns activity between the two dates per FUNCTIONS.md. Set start = fiscal-year start and end = as-of date so P&L rows show YTD movement and BS rows show the as-of balance — the same shape Xero produces.
Can I build a multi-period Trial Balance (current month vs prior month vs year-end) in one workbook?: Yes. Insert the TB via the task pane for one period-end, then add side-by-side workbook columns referencing a different period per column. For Balance Sheet accounts a single as-of date per column is enough. For P&L accounts each column needs its own period-start AND period-end pair: fiscal-year start through current month-end, fiscal-year start through prior month-end, and so on — XO.BALANCE on a P&L account returns activity between the two dates, so the start argument is what makes each column a YTD figure. All columns reference the same chart of accounts spilled from XO.COA in column A and refresh independently when Refresh is clicked.
Can I filter the Trial Balance by branch, department, or other tracking categories?: Not via the task pane Trial Balance wizard — TABLES.md is explicit that the standard TB report does NOT support tracking filters or Compare periods (use the Profit & Loss or Balance Sheet reports for tracking-filtered analysis). For a tracking-filtered TB in cell-level form, however, XO.BALANCE does accept up to two tracking-category arguments — for example =XO.BALANCE($A$2, "4000", $D$1, $E$1, "Region", "North") returns the North-region activity for account 4000 (a Revenue account) between $D$1 (fiscal-year start) and $E$1 (as-of date). Combining XO.COA (account list) with category-filtered XO.BALANCE columns gives you a per-branch TB built in Excel from the live ledger, even though the wizard itself does not expose the filter.
How does the Trial Balance handle multi-currency organizations?: The Trial Balance is generated in the organization’s base currency — Xero handles the underlying currency conversion of foreign-currency transactions when balances are computed. Account-level currency breakdowns are visible in the inserted task pane report. For cell-level workbooks, spill the chart of accounts with =XO.COA($A$2) — column 12 (CurrencyCode) shows each account’s native currency, so you can filter the spilled table to flag non-base-currency accounts.
How does the Trial Balance tie to the Profit & Loss and Balance Sheet?: The TB is the source — every line on the P&L (revenue + expense accounts) and every line on the Balance Sheet (asset + liability + equity accounts) appears on the TB at the same as-of date. Revenue minus expense rows on the TB sum to the Net Income line on the P&L. Asset rows minus liability and equity rows balance to zero on the TB. The XO.BALANCE formula and the task pane reports all read the same underlying ledger, so the three reports always reconcile.

Xero Trial Balance in Excel