Published 24 August 2026

Consolidating Multiple Xero Organisations in One Excel Workbook

Xero is built around one organisation at a time. Reports run against the organisation you are currently in, and you switch between them from the organisation menu. That is fine until you are responsible for a group — three trading companies and a holdco, or a client with a property entity sitting alongside the main trade — and someone wants one set of numbers covering all of them.

The usual answer is a workbook with a tab per entity, a "Consol" tab full of =Trading!C14+Property!C14, and a monthly ritual of pasting fresh exports into each tab and praying the row order held. This guide replaces that with a Power Query pattern that survives new accounts, new entities and back-dated journals — and is honest about the point where the file-based version stops being worth maintaining (which is where a synced database, the approach Flow takes, starts to earn its keep).

Decide what kind of consolidation you're doing

Worth separating two jobs that get the same name, because they need different amounts of rigour.

Management consolidation is a combined view for decision-making: group revenue, group overheads, an entity-by-entity P&L with a total column. Intercompany recharges are usually netted off, and nobody signs anything. This is what most practices need monthly, and it is what this guide builds.

Statutory consolidation brings in acquisition accounting, goodwill, non-controlling interests, FX translation reserves and consolidation journals that live outside any entity's ledger. Power Query can assemble the inputs, but the consolidation adjustments belong in a proper schedule with an audit trail — don't bury them in an M step.

Build the management version first. It is the same data spine either way.

Get every entity into one long table

The structural mistake in the tab-per-entity workbook is that entity identity is encoded in the sheet name rather than in the data. Fix that and everything downstream gets easier.

Target shape — one long, tidy table, one row per account per month per organisation:

Start by cleaning each organisation's trial balance export into that shape individually — the step-by-step for a single TB is in Xero trial balance in Excel with Power Query. Then stamp the organisation name on before you combine:

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

Do that once per entity — set each query to Connection Only so nothing lands on a sheet — then append them:

let
    Combined = Table.Combine({Trading, Property, Holdco}),
    Ordered  = Table.SelectColumns(Combined, {"Organisation", "AccountCode", "AccountName", "Period", "Net"})
in
    Ordered

Table.Combine matches on column names, not position, so a stray extra column in one export won't shift anyone's figures — it appears as a column of nulls, which is loud and easy to spot. Adding a fourth entity later means one more query and one more name in that list.

If your entities share an identical export layout, the folder pattern is fewer moving parts: put every export in one folder named TB-Trading-2026-06.xlsx, TB-Property-2026-06.xlsx, use Data → Get Data → From File → From Folder → Combine & Transform, and parse both entity and period out of the Source.Name column with Text.BetweenDelimiters. One query instead of four, at the cost of a filename convention nobody is allowed to break.

Make the accounts actually line up

Appending gets the rows into one table. It does not make them comparable. Separate Xero organisations have separate charts of accounts, and even when they were created from the same template they drift — one entity adds 4001 Consultancy income, another calls the same thing 4000 Sales.

Do not solve this by editing charts of accounts to match. Solve it with a mapping table that lives in the workbook:

Load it as its own query and merge it onto the combined table on Organisation + AccountCode — a two-column merge, because code 200 legitimately means different things in different entities. Then group to report line:

let
    Source   = Combined,
    Mapped   = Table.NestedJoin(Source, {"Organisation", "AccountCode"}, Mapping, {"Organisation", "AccountCode"}, "map", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Mapped, "map", {"ReportLine", "Sort"}),
    Grouped  = Table.Group(Expanded, {"ReportLine", "Sort", "Organisation", "Period"}, {{"Amount", each List.Sum([Net]), type number}})
in
    Grouped

The important habit: use a left outer join and then filter for ReportLine = null in a separate exceptions query loaded to its own sheet. That query is your control. When someone adds an account in Xero mid-month, an unmapped row appears there rather than silently dropping out of the consolidation — which is exactly what an inner join would do, and it would still foot. Map by code, never by name; names get edited in Xero and a mapping keyed on them breaks without telling you.

Intercompany: net it off deliberately

If entities recharge each other, the combined total double-counts. The clean approach is to keep intercompany in dedicated account codes in every organisation — a management recharge income code and its matching expense code — map those codes to a ReportLine of Intercompany, and exclude that line from the group total while still showing it per entity.

Two things to check every month before anyone circulates the pack:

• The intercompany lines net to zero across the group. If they don't, someone booked one side and not the other, or booked it to the wrong code. Add it as a check cell in the workbook, not a thing you remember to eyeball.
• Intercompany balance sheet accounts agree. Company A's receivable from B should equal B's payable to A. Timing differences around the month-end are the usual culprit when they don't.

Neither check needs to be clever. A SUMIFS returning something other than zero, formatted red, is enough — the point is that it runs on every refresh rather than depending on someone's diligence at 6pm on the third working day.

The output layer

With a long table loaded, an entity-by-entity P&L is a pivot table: ReportLine down the side, Organisation across the top, Amount in values, Period as a slicer. New entity, new column. New account, new row. Nothing to relink.

Where you need a fixed layout — a board pack format that must not reflow — drive it with dynamic arrays off the same table rather than cell references into other tabs:

=LET(
    lines,   SORT(UNIQUE(FILTER(consol[ReportLine], consol[Period] = $B$2))),
    trading, SUMIFS(consol[Amount], consol[ReportLine], lines, consol[Organisation], "Trading Ltd", consol[Period], $B$2),
    property,SUMIFS(consol[Amount], consol[ReportLine], lines, consol[Organisation], "Property Ltd", consol[Period], $B$2),
    HSTACK(lines, trading, property, trading + property)
)

SUMIFS accepts the spilled lines array as its criteria and returns one result per line, so the block grows and shrinks with the data instead of needing rows inserted.

Where the file-based version stops scaling

Be clear-eyed about this, because the pattern above is genuinely good and the failure mode is gradual rather than dramatic.

• Exports multiply by entity, not by report. Four entities and twelve months is 48 exports a year for the TB alone, each one a manual step that a person has to remember.
• History is frozen in files. A back-dated journal into March reaches your March figures only if someone re-exports March. Across a group this is the most common source of comparatives that quietly stop agreeing to last month's pack.
• Entity onboarding costs a query. Every new organisation means a new query, a new set of mapping rows and a re-test.
• Refresh gets slow. Combining dozens of workbooks means opening dozens of files on every refresh, with no query folding to push the work elsewhere.

The alternative is to stop treating each organisation as a separate source. If a service syncs every connected organisation into one SQL database, the organisation is a column in a single table, and the entire append layer above disappears:

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

The mapping table and the output layer stay exactly as they are — that work is not wasted. What goes away is the exporting, the appending, and the per-entity maintenance. The wider trade-offs between exports, calling the Xero API directly and querying a synced database are set out in how to connect Excel to Xero with Power Query.

Pitfalls worth designing around

• Different year-ends. If entities have different accounting reference dates, a "year to date" column means different things per entity. Consolidate on calendar months and derive the group year separately.
• Different base currencies. Appending figures in different currencies produces a total that means nothing. Translate before you combine, at a rate held in a table with the period against it — never a hard-coded number in an M step.
• The current month is provisional. It changes daily in every entity, and entities close at different speeds. Label it as provisional in the pack.
• Sign conventions must match across entities. Decide once — debits positive, credits negative — and enforce it in each entity's query. One entity with the convention flipped produces a consolidation that still foots and is still wrong.

Where Flow fits

Flow syncs all your connected Xero organisations into one Azure SQL database — monthly trial balance snapshots, invoices, credit notes and the chart of accounts — so consolidation becomes a group-by on one table rather than an append across files, and back-dated journals land in the right period because historic months are re-verified rather than written once.

Plans start at £20/month for up to 5 organisations with 2 years of trial balance history; Plus (£40/month) covers 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.