Xero Consolidation in Excel: Multi-Entity Reporting

Group consolidation is the process of combining financial statements across multiple legal entities into a single set of statements that represents the group as one economic unit. For a CFO running a multi-country holding company, it is the most-consulted internal report, the basis for board reporting, investor updates, treasury decisions, and compliance filings.

Xero runs each organization as a separate set of books, in its own base currency, with its own chart of accounts, and its standard reports cover one organization at a time. So in most Xero-driven groups, consolidation is an Excel discipline: every period, finance teams pull each entity's P&L and Balance Sheet into a master workbook, apply FX rates, post intercompany eliminations, and roll up.

Excel remains the most common surface for group consolidation. The master workbook needs entity columns, FX-rate cells, elimination adjustments, opening-equity tie-outs, board-reporting templates, and commentary panels, all in a layout the finance team controls. Even firms running dedicated consolidation platforms typically export back into Excel for the final pack.

Live multi-entity data in Excel transforms this monthly slog into an on-demand refresh. The structure of the consolidation workbook (entity columns, GL account rows, FX cells, elimination columns) is set once and reused every period. Only the underlying balances refresh from Xero. CFOs gain a current group view that no longer waits for the close calendar to align across entities.

For accounting firms serving multi-entity clients, the same template scales: one consolidation model per client structure, refreshed against each client's connected Xero organizations at the click of a Refresh button.

• Log into each Xero organization, export the P&L and Balance Sheet, paste into the master workbook entity column, then repeat for every entity, every period, every reporting cycle.
• Entity-by-entity exports lose the live link to the source ledger the moment they hit Excel; late journals in any entity invalidate the entire group pack.
• FX-rate adjustments are typed in by hand against export totals; any FX-rate change requires re-keying every entity's translated balance.
• Intercompany account reconciliation is manual: match entity A's intercompany receivable against entity B's intercompany payable, line by line, with no automated tie-out.
• Version control across five-plus entity exports becomes a nightmare; the wrong export attached to the wrong column quietly breaks the group balance sheet.
• Audit-trail reconstruction requires keeping every export file forever. Most firms simply accept that prior-period reconstruction is impractical.

Honest positioning: XO Report is not a consolidation platform. There is no XO function called XO.CONSOLIDATE, no dedicated consolidation report, and no elimination or FX-translation engine. But the standard reports can be inserted for several organizations at once, and formulas can pull any entity into any cell. That is the multi-organization data layer a real consolidation workbook needs. Your consolidation logic stays under your control.

Method 1: one report for every entity

1. In the XO Report task pane, open Tables and Reports and choose Profit & Loss, Balance Sheet or Trial Balance.
2. In the Organizations step, tick every entity in the group, then set the dates.
3. In Options, tick Hide Totals. It is off by default, and Xero's subtotal and total rows would otherwise be counted twice when you add the table up.
4. Click Insert. You get one Excel table holding every entity's rows, each tagged with OrgID and OrgName. Profit & Loss adds Section, AccountCode, Account and Amount; the Balance Sheet shows Balance in place of Amount; the Trial Balance gives debit and credit columns.
5. Insert a PivotTable from the Profit & Loss or Balance Sheet table: Account in Rows, OrgName in Columns, and Sum of Amount (or Balance) in Values. Each row total is that account across the group, before FX translation and eliminations.

Good to know:

• Amounts stay in each organization's base currency. If the entities use different currencies, translate them in your workbook before adding them up.
• Pivot on the account name, not the section: custom report layouts in Xero can give each organization different sections, and computed rows such as Current Year Earnings have no account code. If entities name accounts differently, add a mapping column that assigns each account to a group line, and pivot on that.
• Tracking-category filters are unavailable when more than one organization is selected.
• Bank Summary and Budget Summary can combine organizations the same way. The Aged Receivables and Aged Payables reports run on one organization at a time.
• One Refresh re-pulls every selected organization; then refresh the PivotTable to update the group view.

Method 2: fixed layout with formulas

The pattern. Each XO.PROFIT or XO.BALANCE formula reads one organization, so each entity gets its own column. Use =XO.ORG() to spill the list of every connected organization with their Org IDs. Pull entity-level Net Income with XO.PROFIT and account-level balances with XO.BALANCE, then combine across columns with Excel SUM for the group total. Apply FX rates and intercompany eliminations in dedicated columns you control.

Method 1 is the quickest way to see every account for every entity; Method 2 suits a board pack whose layout stays fixed, and both can live in the same workbook. Either way, XO Report is the data layer and your workbook is the consolidation engine. It is NOT a replacement for a dedicated consolidation platform if your needs include automated equity-pickup, automated FX retranslation against historical rates, or audit-grade consolidation reporting.