Xero report export (Excel, P&L / Balance Sheet)
A Xero financial report saved to Excel, which people paste in and parse as a grid, when its merged header cells, region-driven date and number formats, and silent basis/tracking scoping make a naive column read unreliable.
The shape, how the file is really laid out
The sheet opens with the organisation name, the report name, and the period as
stacked title rows, commonly in merged cells that span the data columns, and
closes with a footer such as "Printed <timestamp>". A P&L may carry several
period columns plus a Total column; sections resolve to derived Gross Profit
and Net Profit lines. Dates and numbers follow the organisation's region:
31/03/2025 (day-first) is normal, and some locales render thousands and
decimals as 1.234,56 rather than 1,234.56. Negatives appear in parentheses or
with a leading minus depending on region.
The traps
Merged title cells break a header-row=1 parse and can shift every column by one when un-merged.
- Every trap in this export, worked through
- The reconciliation anchor, the total the file asserts about itself
- The refusal cases, when to stop and ask instead of guess
Reference: Xero Central, "Export a report" and report layout/basis (cash vs accrual) documentation · Xero Central, tracking categories and multi-currency reporting (reference only)