There are five common ways to get Xero data into Excel: Xero's own export, copy-paste from Xero's screens, Power Query, a third-party data connector, and live formulas from an add-in such as XO Report. They differ in what happens after you close the file. An export or a copy-paste is a snapshot that goes stale as soon as anything changes in Xero. Power Query and data connectors can refresh, but need extra setup: an API connection or third-party driver for Power Query, or a separate sync service for a connector. Live formulas refresh inside Excel itself.
The short version: for a one-off, Xero's own export is fine, and the steps are right below. If you will open the same workbook again next month, live formulas save the most time; the numbered walkthrough further down sets them up end-to-end.
Export directly from Xero
Xero can export most of its data without any add-in:
- Reports (Profit and Loss, Balance Sheet, Trial Balance, aged reports and more): in the Reporting menu, select All reports, open the report, click Export and choose PDF, Microsoft Excel or Google Sheets. If some amounts show as 0.00 in the Excel file, click Enable Editing in Excel and they update (Xero: export and print a report).
- Invoices and bills: in the Sales menu, select Invoices (or Purchases → Bills), pick a tab, optionally search by date, then export (for bills, Export sits in the menu next to New Bill). Xero downloads a CSV file with one row per line item, up to 500 transactions per export (Xero: export invoices and bills).
- Bank and other account transactions: open the Account Transactions report under Reporting → All reports, select the bank or other accounts and a date range, click Update, then export it like any other report (Xero: Account Transactions report).
- Contacts and chart of accounts: in the Contacts menu, choose a group, click the menu icon and select Export; for accounts, go to Accounting → Chart of accounts and click Export. Both download as CSV.
Menu names can differ slightly by region and Xero version, so the linked Xero Central articles have the current steps. Xero's overview of exporting data lists everything else it can export.
What XO Report keeps live, and what it doesn't
Every export above is a snapshot. If you repeat one every month, XO Report can pull the same kind of data live instead: the 10 standard Xero reports, the chart of accounts, contacts, items, tracking categories, tax rates and currencies, and invoices, bills, credit notes and payments as tables. A Bank Transactions table (Beta) lists money in and out of your bank accounts, checked against Bank Summary (classic expense-claim payments are not included). It has no table or report for payroll or employee records, manual journals or the transaction-level general ledger (Account Transactions). For those, keep using Xero's own export.
The walkthrough assumes Excel 365 on Windows or Mac, or Excel for Web. XO Report runs in all three. No accounting expertise required, only basic Excel familiarity (writing a formula, locking a cell reference with $).
Install XO Report from AppSource or the Xero App Store
XO Report is distributed as a Microsoft 365 Office add-in. From Excel, open the Home → Add-ins, search for XO Report, and click Add. Excel for Web installs it directly; Excel desktop will install the add-in for your Office 365 user account. The same add-in works on Windows, Mac, and the browser.
If you would rather start from the Xero side, the same add-in is listed in the Xero App Store. Install from there and Xero connects you to AppSource to complete the Office side of the install. Either entry point lands you in the same place.
Connect to Xero
The first time you open the task pane (Home tab → XO Report), you will see a Connect to Xero button. Click it. Xero opens an OAuth consent screen in a popup. Log in if needed and approve the requested scopes. XO Report asks for read-only access to your organisation details, chart of accounts, contacts, invoices and bills, payments and credit notes, bank transactions, manual journals, budgets, tracking categories, tax rates and the financial reports. No write access. If you are starting a trial, click Start Free Trial in the task pane before pulling data.
You can connect every Xero organisation you have access to in a single session. The free trial covers up to 10 organisations; paid plans scale from Solo (1 organisation) to Max (50), with Business covering 25 organisations. Multi-entity groups typically need the Pro or Business tier. For what the connection can access and how to add or remove organisations later, see how to connect Xero to Excel.
List your organisations with =XO.ORG()
The first formula is the org list. In an empty cell (say
A1on a tab named Setup), type=XO.ORG()and press Enter. The function spills a table with two columns: Org ID (a short identifier like!abc123) and Org Name. Every other XO formula references an Org ID, so this single tab becomes your control sheet.For workbooks that should always bind to the same organisation, lock the reference:
=XO.BALANCE($A$2, "4000", C1, D1)reads the Org ID from a fixed cell. Copy-and-fill safely. Multi-entity workbooks usually have one Setup tab with the org list spilled intoA1:B11and every formula on every other tab pointing at one of those rows.Pull your first report with =XO.PROFIT() or =XO.BALANCE()
Two formulas cover most reporting starting points.
=XO.PROFIT(A2, C1, D1)returns the bottom line from the Xero P&L for the date range inC1:D1as a single number, perfect for a dashboard KPI. Positive is profit, negative is loss. Date range cannot exceed 365 days (a Xero API limit on the P&L report).=XO.BALANCE(A2, "4000", C1, D1)returns the activity on account 4000 between the two dates. For Balance Sheet accounts (Asset, Liability, Equity) it returns the balance as of the end date. The start date is ignored. XO Report figures out which behaviour applies from the account class, so you write the same formula either way. P&L signs match Xero; Balance Sheet normal balances are positive. Both functions also accept optional tracking-category / option pairs as the final arguments. Pass them to slice the result by region or department.From there, the rest of the formulas (
XO.BUDGET,XO.COA,XO.CONTACTS, etc.) follow the same shape. The per-function reference under /docs/functions lists every parameter and a copy-paste example for each of the 11 XO formulas.Refresh, and understand which surfaces refresh how
Three surfaces in XO Report have three different refresh patterns. Formulas recalculate whenever Excel decides their inputs changed: change a date cell and the dependent
XO.BALANCEformulas recalculate for the new date, pulling that period from Xero if it is not already loaded. Recalculating without changing inputs (for example pressing F9) may reuse the figures XO Report already has. Either way, to pull the very latest straight from Xero, click the task-pane Refresh button.Tables (XO.COA-style spilled tables) and Reports(full P&L, Balance Sheet, etc. inserted from the Tables and Reports mode) have a per-surface refresh control next to each inserted block. They cache more aggressively than formulas, so open the task pane and click Refresh when you want fresh data from Xero.
For the basic post-install workflow, see the quick-start guide.
How the other four methods compare:
- Xero's own export. Works for a one-off (the steps are at the top of this page). Re-opening the workbook next month means exporting again. See manual export vs XO Report for the maintenance math.
- Copy-paste from Xero report screens. Same problem as CSV plus formatting issues: negative numbers in parentheses, totals that lose their formulas, date columns parsed as text.
- Power Query against Xero API. Powerful, and it can refresh once set up, but it needs an OAuth token (Xero does not officially support custom Power Query connectors), credential rotation, and per-org query templates. Reasonable for one-off engineering projects but not for a finance team that needs to add an org without writing M code.
- Third-party data connectors. Sync Xero into a database (or a worksheet) on a schedule, then point Excel at the database. Strong for analytics stacks where the data also feeds Looker / Power BI; overkill if Excel is the only destination. See XO Report vs data connectors for cost comparisons.
Live formulas via XO Report sit in the gap between "copy-paste each month" and "build a data warehouse": refreshable but bound to the spreadsheet, with no extra infrastructure. If the workbook lives and dies inside Excel, this is usually the right tool.
Frequently asked questions
- How is this different from exporting from Xero?
- An export from Xero (Excel, CSV, PDF or Google Sheets) is a one-time snapshot: close the workbook, re-open it next month, and the numbers are stale. XO Report formulas refresh on demand against the live Xero data, so the workbook stays current without a re-export step.
- Can XO Report export payroll or employee details from Xero?
- No. XO Report reads Xero accounting data (reports, accounts, contacts, items, invoices, bills, credit notes, payments and more) and does not read payroll or employee records. For employee details, use the payroll reports and exports in Xero or in your payroll provider.
- Does XO Report support multiple Xero organisations in one workbook?
- Yes. =XO.ORG() spills every connected organisation; reference different Org IDs in different formulas to pull from different entities. Multi-entity consolidation workbooks typically have one Setup tab with the org list and every formula bound to a row from that table.
- What happens to my workbook if I change a date cell?
- Excel recalculates any formula whose inputs depend on that cell, so XO.BALANCE and XO.PROFIT formulas pointing at the changed date recalculate for the new period, pulling from Xero if that period is not already loaded. Tables and Reports inserted from the task pane have their own Refresh control because they cache more aggressively than formulas.
- Is XO Report read-only against Xero?
- Yes. Every formula and every table is a read operation. XO Report never writes back to Xero. The OAuth scopes you approve on install do not include any write permissions on Xero.
- Does this work on Mac and Excel for Web?
- Yes. The same Office add-in runs on Excel desktop (Windows + Mac) and Excel for Web. Formulas behave identically. The task pane UI is the same across surfaces.