Xero Consolidated P&L Pivot Table: Why You Can't Pivot a Xero Report

You have the group P&L. It took most of a morning: export each entity, paste into the master workbook, run the eliminations tab, check it casts. It's right, and you send it.

What you don't have is a pivot table.

Then the first question arrives. "Which entity drove the jump in staff costs?" You can answer that — it's in the workbook. Then: "Was that one month or has it been building since June?" And: "Is it the same story if you strip out the intercompany recharges?"

Each of those is a different cut of the same numbers. And each one means going back to the workbook, reshaping it, and sending a new version. By Thursday there are four files called some variant of Group P&L FINAL, and nobody is sure which one the board saw.

The report answered one question. A pivot table would have answered all of them.

Where native Xero stops

There's no group export in the first place. Xero reports one organisation at a time. There is no cross-organisation consolidation, and this is not an oversight anyone has missed — the feature request was filed in October 2012, holds 277 votes and a status of Accepted, and in July 2025 Xero's community manager said plainly that work on consolidated reporting "is not currently planned", pointing people to App Store partners instead. Thirteen years is a clear answer. Build around it.

A Xero report export is a document, not a table. This is the part that catches people. You export the P&L expecting data, and what lands is a formatted report: merged heading cells, section bands, indentation that carries the hierarchy, and Xero's own subtotal and total rows sitting in the same column as the account lines. A pivot table needs the opposite — one row per fact, every dimension in its own column, no totals inside the data. So before you can pivot anything you have to unpick the presentation, and reshaping a formatted report into a clean table is usually more work than starting from raw transactions.

One filter at a time. Xero's reporting lets you filter by a tracking category, but not cross two of them into a matrix. You cannot put department down the side and project across the top — which is exactly the shape a pivot exists to produce. We wrote about the underlying ceiling in the two-limit tracking category problem; the practical effect here is that even within one entity, Xero answers one question per run.

Custom layouts don't survive contact with anything else. Spend an afternoon building a P&L layout with your groupings and subtotals and you get a report that looks right — and a budget comparison that ignores the structure entirely. The layout is a presentation setting, not a data model, so nothing downstream inherits it.

Every answer is a re-run. None of the above would matter much if re-running were free. It isn't: it's a switch of organisation, a re-export, a re-paste, and a re-check that the thing still casts.

What hitting the wall looks like

The group controller on results day. The pack went out at 9am. By noon there are five follow-up questions, each needing a different slice, each taking twenty minutes. The numbers never change. Only the arrangement does.

The finance manager who inherited the workbook. Twelve sheets, one per entity, a thirteenth that sums them, and an eliminations tab nobody has fully traced since the person who built it left. It produces one view. Asking it for a different one means editing formulas, which means risking the view that already works. See also: what proper monthly management accounts should look like.

The practice partner reviewing a client group. She wants to see the same account across four entities, side by side, for six months. There is no report that does this. There is an export per entity per month — twenty-four exports — and an afternoon.

Anyone asked "why". A consolidated P&L tells you the number. It cannot tell you what the number is made of, because by the time it's a report, the detail has been summed away.

The thing to pivot is a ledger, not a report

The fix isn't a better export. It's pivoting something else entirely.

Underneath every P&L line is a set of posting lines — individual transactions coded to an account. If you assemble those into one flat table across every entity, with each attribute in its own column:

• Entity — the organisation the line was posted in
• Month — the posting month, stored as text so it groups rather than scattering into dates
• Account — name and code
• Section — Revenue, Cost of sales, Operating expenses, and so on
• Type — invoice, credit note, bank transaction, journal
• Contact — who it was with
• Amount — one signed column

…then a pivot over that table is the group P&L. Section and account down the side, entity across the top, amount in the middle. Want it by month instead? Drag month into the columns. Want to exclude intercompany? Filter the column. Want to know what makes up a figure? Double-click it.

No re-run. The report stops being a document and becomes a view.

One detail that pays for itself: store the amount credit-positive, so revenue is positive and expenses are negative. Do that and the pivot's grand total is net profit, automatically, in every cut. Store it the other way and every subtotal needs a sign convention explained in a footnote.

Where the difficulty actually lives

Assembling that ledger is the real work, and it's worth knowing what you're taking on before you start.

The ledger Xero gives you is built to be read, not pivoted. Xero does have a General Ledger Detail report, and it is genuinely one row per posting line: date, source, description, reference, debit, credit, running balance, tax rate. As a document to read down, it's fine. As a table to pivot, four things are in the way.

• Debit and credit are separate columns. They have to be, or the running balance can't work. But a pivot needs one signed column — sum "Debit" and you get gross movement, which answers nothing. Collapse the pair into a single amount before anything else.
• There's a running balance in the data. It's cumulative, so it's meaningless in a pivot and actively wrong if anything sums it. Drop the column entirely.
• The account is a heading, not a value. The report groups by account, so the account name sits in a band above its rows rather than on each one, with a subtotal closing the group. A pivot can't group by something that isn't in the row. The band has to be pushed down into every line it covers, and the subtotals deleted.
• The export has a title block on top. Rows of report name, entity and date range sit above the headers, so the column names aren't in row 1 when the file opens.

None of that is hard. It's just that it's the same half-hour of reshaping every period, per entity — and there's no entity column either, because the report only ever covers the organisation you ran it in. Stacking six entities means adding that column yourself, six times, and getting it right every time.

If you assemble the ledger from the API instead, some postings aren't documents. Xero's automatic FX entries — realised and unrealised currency gains, bank revaluations — exist only as system journals. No document carries them, so a ledger built up from invoices, bills and bank transactions simply won't see them, and your pivot will quietly under-report. They have to be lifted separately from the P&L report, one line per month per account.

GL lines don't know which section they're in. A posting line carries an account, not a statement section. The section has to be derived — from the account's type, or from a mapping you maintain — or the pivot has no meaningful row hierarchy.

Pivots sort alphabetically, and statements don't. Cost of sales belongs above operating expenses, not after it. Most pivot engines give no custom row order, so the usual fix is to prefix the section label with its position — 1 Revenue, 2 Cost of sales — and let alphabetical sorting reproduce the statement.

Prove it ties. A ledger you assembled yourself is a claim until it's checked. Put a tab in the workbook that compares the ledger's P&L net, per entity per month, against Xero's own P&L report for the same period. Sub-dollar differences are rounding across thousands of lines. Anything larger is a missing posting, and you want to find it before the board does.

What a good group P&L pivot actually looks like

1. One row per posting line, every dimension a column. No merged cells, no subtotals in the data, no blank spacer rows.
2. Entity as a column, not a tab. The moment entity is a dimension rather than a file, cross-entity questions stop being a project.
3. Credit-positive amounts, so the grand total is net profit in every cut.
4. Section ordered like a statement, not like a dictionary.
5. Intercompany flagged, not deleted. A flag column lets the reader include or exclude group-internal revenue on demand. Deleting the lines makes that decision permanently, for everyone. Full intercompany elimination is a separate exercise and belongs on the consolidated statement, not in the ledger.
6. A tie-out tab reconciling the ledger back to Xero's own reports, per entity, per month.
7. Drill-through that reaches the document. From a cell, to its lines, to the invoice number you can search in Xero.
8. Regenerated, not maintained. Next month it rebuilds from source. A pivot fed by a table someone updates by hand is just a workbook with extra steps.

The workarounds, ranked

1. Export per entity, stack in Excel, pivot that. The honest starting point, and fine for two entities and a quiet month. You'll spend the time on stripping out subtotal rows and normalising account names, and you'll spend it again next month.
2. A consolidation add-on. Joiin, Fathom and the rest will give you a group P&L, and some give you a degree of slicing. Worth comparing against what you actually get asked for — we did that in Joiin vs Fathom vs Spotlight. The common ceiling is that you get their cuts, not arbitrary ones.
3. Assemble the ledger off the Xero API and pivot it. Most work up front, no re-templating afterwards, and the only route that ends with arbitrary slicing. Building Custom Reports with the Xero API covers the gotchas honestly before you commit.

Where Cheetah fits

A fair number of our consolidation engagements start with someone sharing a group P&L workbook and then, almost apologetically, a second file — the one they rebuild whenever anyone asks a follow-up question.

We tend to build both: the consolidated statement with its eliminations, and beside it a posting-line ledger with a live pivot over it, so the statement answers the standing question and the pivot answers everything after. It regenerates each period from Xero, carries a tie-out tab so the ledger can be trusted against Xero's own reports, and exports to Excel with the pivot intact for whoever wants to take it apart themselves.

If your group P&L is accurate and still can't answer a follow-up without a re-run, Cheetah is probably worth twenty minutes.

The short version

Xero has no group P&L, and the request for one has been open since 2012 with a clear "not planned" attached. But the deeper problem is that even a single-entity Xero report can't be pivoted, because it's a formatted document — bands, indentation and subtotals — rather than a table.

Pivot the ledger instead. One row per posting line, entity and month as columns, amounts credit-positive, sections ordered like a statement, and a tab that proves it ties back to Xero. Then the group P&L stops being a file you reissue and becomes a view you re-cut.

If you're weighing up how to get there, the routes are laid out in Xero Custom Reports: 4 Ways to Build Them in 2026 — and the statement-shaped half of the problem is in how to consolidate multiple Xero entities.