Live Xero data in Excel is not just a matter of avoiding the export-paste cycle — it is an architectural choice. Done well, the workbook has a single setup tab with all inputs (organisation IDs, period dates, account codes, tracking dimensions) and every formula on every other tab points back to those control cells. Change one input, the whole model rebuilds.
This tutorial walks the pattern end-to-end with realistic XO Report formulas. The same pattern works for a monthly board pack, a budget-versus-actual variance table, or a rolling 12-month KPI dashboard. By the end you will have a workbook you can hand to next month's analyst and they will only need to change the dates.
Prerequisite: XO Report installed and connected to at least one Xero organisation. If you have not done that yet, read the how to export Xero to Excel tutorial first — it covers install and first-formula end-to-end.
Build a Setup tab with control cells
Insert a new worksheet and name it Setup. In cell
A1type=XO.ORG()and press Enter — your connected organisations spill into a two-column table starting at A1. Rows are: header (A1:B1) then one row per organisation. Lock these positions: every subsequent formula will reference$A$2(or whichever row matches the target entity).Below the org list, build a small inputs block. Suggested layout:
D1= label "Period Start",D2= the date itself;E1= "Period End",E2= the date. Repeat for tracking dimensions if you use them:F1= "Region category",F2= "Region",G2= "North". The shape is up to you — what matters is that every input lives in one tab and gets referenced absolutely ($D$2) from elsewhere.Write your first XO.BALANCE referencing control cells
Add a new tab named P&L. In column A list your account codes — say
4000,4100,5000,6200,7000. In B1 put a header like "Period balance". In B2 write:=XO.BALANCE(Setup!$A$2, A2, Setup!$D$2, Setup!$E$2)Fill down. Every row now pulls its account from column A and its dates from the Setup tab. Change
Setup!E2from 2026-03-31 to 2026-04-30 — every balance refreshes to the new period end. For Balance Sheet accounts XO Report uses the end date as "as of" and ignores the start date, so the same formula shape covers both P&L and Balance Sheet rows without branching.Extend the model with XO.PROFIT and XO.BUDGET
Two formulas extend the model without leaving the control-cell pattern.
=XO.PROFIT(Setup!$A$2, Setup!$D$2, Setup!$E$2)returns the net income for the selected period in a single cell — perfect for a header strip or a dashboard KPI. It also accepts optional tracking-category / option pairs so a single profit number can be sliced by region or department.For variance analysis, pair
XO.BALANCEwithXO.BUDGET:A: Account | B: Actual | C: Budget | D: Variance 4000 | =XO.BALANCE(Setup!$A$2, A2, Setup!$D$2, Setup!$E$2) | =XO.BUDGET(Setup!$A$2, "Overall Budget", A2, Setup!$D$2, Setup!$E$2) | =B2-C2XO.BUDGETreturns the budget amount only — the Variance column is a standard Excel subtraction. Note that Xero budgets only apply to P&L accounts; Balance Sheet accounts do not support budgets. The budget name (here "Overall Budget") must match the budget name as defined in Xero exactly.Understand the refresh model — formulas vs tables vs reports
Three XO Report surfaces have three refresh behaviours. Formulas recalculate whenever Excel decides their inputs changed — change a date cell, the dependent
XO.BALANCEformulas refresh. This is the entire point of the control-cell pattern. F9 (or Shift+F9 for the active sheet) forces a manual recalculation if Excel is in manual-calc mode.Tables (a full
=XO.COA(Setup!$A$2)spill, for example) and Reports(the full P&L or Balance Sheet inserted via the Tables and Reports task-pane mode) cache more aggressively than formulas. They have a per-surface Refresh control on the task pane next to each inserted block. Use this when the underlying Xero data may have changed but the input cells did not — e.g., a new month-end close was posted in Xero overnight.None of these refresh on a schedule by themselves. XO Report uses a pull model (formula recalculation or explicit Refresh click) rather than a push model (continuous polling, websocket subscription). For schedule-driven refresh, the patterns are external (Task Scheduler, Office Scripts, workflow tools).
Multi-org: extend the pattern across entities
For multi-entity groups, the control-cell pattern extends naturally. Two common shapes:
Side-by-side per entity: in a comparison sheet, columns B/C/D each reference a different row of the spilled
XO.ORG()table —=XO.BALANCE(Setup!$A$2, …),=XO.BALANCE(Setup!$A$3, …),=XO.BALANCE(Setup!$A$4, …). Five entities means five columns. Changing the period in Setup refreshes every column at once.Stacked / consolidated:XO Report's wizard generates VSTACK formulas for Tables (e.g.
=VSTACK(XO.COA(Setup!$A$2), DROP(XO.COA(Setup!$A$3),1), DROP(XO.COA(Setup!$A$4),1))) to combine multiple organisations into one table without duplicate header rows. The multi-entity consolidation spoke covers this pattern in depth.
The control-cell pattern works because XO Report is read-only and idempotent. Every formula is a pure function of its inputs and the current Xero data; nothing in the workbook drifts. Hand the file to next month's analyst, change two date cells, and the model is current.
Compared to data-connector pipelines that sync Xero into a database overnight, the live-formula approach trades scheduled refresh for in-the-moment freshness. The model is current at the instant you opened the workbook (or last clicked Refresh) rather than at last night's sync. For finance teams that care about the most-recent Xero state when they open the file, this is the right trade-off.
Frequently asked questions
- How is this different from a static Xero CSV export?
- A CSV is a snapshot taken at one moment. The control-cell pattern is a workbook that re-derives every number from live Xero data on demand. Re-opening the workbook next month, changing two date cells, and pressing F9 gives you next month's numbers without re-exporting anything.
- What happens if someone renames an account in Xero?
- XO.BALANCE references the account by code (e.g., "4000"), not by name, so a rename does not break the formula. If the account is archived or deleted in Xero, the formula returns an error and you update the workbook to use the replacement code.
- Can the workbook refresh while no one has it open?
- No — XO Report runs as an Office add-in inside Excel, so refresh requires the workbook to be open. Patterns to work around this: open the file daily on a schedule (Task Scheduler, Office Scripts in Excel for Web) or use a workflow tool to open and save the file. The add-in itself is pull-only.
- How fresh is the data when I press F9?
- XO Report fetches from the Xero API directly when a formula recalculates (subject to a short cache to avoid hammering Xero on every keystroke). For most reports this is within seconds of the latest posted transaction in Xero. Reports that aggregate large date ranges may take a few seconds to return on the first call.
- Does the control-cell pattern work for the Balance Sheet?
- Yes. XO.BALANCE auto-detects the account class — for Balance Sheet accounts (Asset, Liability, Equity) it returns the balance as of the end date and ignores the start date. So a Setup tab with one period-end date is enough for a Balance Sheet model; you only need start dates when you also have P&L rows.
- Can I lock the workbook so users can only change input cells?
- Yes — use Excel's standard Protect Sheet feature on each Report tab while leaving the Setup tab unlocked (or only the input cells of Setup unlocked). XO Report formulas behave like any other Excel formula for the purposes of cell protection.