Getting Xero data into Excel is a problem with five common solutions, only one of which keeps the workbook in sync with Xero after you close the file. This guide walks each method honestly — CSV export, clipboard copy-paste, Power Query, third-party data connectors, and live formulas via the XO Report add-in — then walks through the live-formula method end-to-end with realistic examples.
The short version: if your workbook is a one-off, manual CSV export is fine. If you will open it again next month, live formulas save the most time. Every other method sits somewhere in between, with trade-offs we lay out below.
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 Insert tab, click Get 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 in one click
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 organisations, contacts, accounts, tax rates, and reports. No write access.
You can connect every Xero organisation you have access to in a single session. The free trial covers up to 10 organisations; paid plans run from Solo (1 organisation) through Max (50). Multi-entity groups typically need the Lite or Pro tier.
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:D1— 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. Signs are auto-adjusted so 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 dependent
XO.BALANCEformulas refresh. For an explicit re-pull from Xero, click the task-pane Refresh button or use F9 in Excel desktop.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 — open the task pane and click Refresh when you want fresh data from Xero.
None of these surfaces refresh on a schedule by themselves — XO Report uses a pull model, not push. For the basic post-install workflow, see the quick-start guide. Scheduled and triggered refresh patterns (Windows Task Scheduler, Office Scripts in Excel for Web, workflow tools that open the file) are covered in a separate scheduled refresh tutorial in this cluster.
The four methods we did NOT walk through:
- Manual CSV export from Xero. Works for a one-off — Xero Exportin Reports → P&L drops a CSV. Re-opening the workbook means re-exporting. 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 but 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 CSV from Xero?
- CSV export 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.
- 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 — XO.BALANCE and XO.PROFIT formulas pointing at the changed date will re-pull from Xero in the next recalculation cycle. 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.
- Can I schedule the workbook to refresh automatically?
- XO Report itself uses a pull model — it does not push data on a schedule. To automate refresh, the standard patterns are Office Scripts in Excel for Web, Windows Task Scheduler opening the file daily, or a workflow tool (Make / Power Automate) that opens the workbook to trigger recalculation. The XO add-in does not have a built-in scheduler.