Your First Xero Report in Excel

Picture the finished workbook before we start. It has two tabs:

• Setup — your connected Xero organisations spilled from =XO.ORG(), plus two date cells (Period Start, Period End).
• P&L — five rows, one per account. Columns: account code (you type), Actual (=XO.BALANCE), Budget (=XO.BUDGET), Variance (=Actual-Budget).

Changing the two date cells on the Setup tab refreshes every row of the P&L tab. Hand the file to next month's analyst, they change the dates, the model is current. That's the entire goal.

From Excel, open the Insert tab, click Get Add-ins, search for XO Report, click Add. Excel installs the add-in into your Office 365 account — it follows you across Excel on Windows, Mac, and the browser.

The trial is 14 days and covers up to 10 organisations — no credit card. If you need more orgs, the Solo plan starts at $9.90/month and the Pro plan at $29.90 covers up to 10. The trial is intentionally generous so you can build the workbook you actually need without hitting limits.

Open the task pane: Home tab → XO Report button. The pane appears on the right. Click Connect to Xero — a popup opens for OAuth consent. Log into Xero if needed, approve the scopes (XO Report asks for read-only access; no write permissions).

Once connected, you can authorise as many organisations as the trial / your plan allows. The task pane lists them. Disconnecting an organisation later is one click in the pane settings; XO Report stops fetching from that org and existing formulas return an error until you reconnect or repoint them.

Create a new worksheet, name it Setup. In cell A1, type =XO.ORG() and press Enter. A two-column table spills starting at A1: Org ID in column A, Org Name in column B, one row per connected organisation.

Pick the organisation you want to report on — say row 2 (A2 contains the Org ID like !abc123). Every formula in the workbook will reference Setup!$A$2 (the $ signs lock the row absolute so copy-fill behaves).

Below the org list, add the date inputs. Suggested layout: D1= label "Period Start", D2 = the date (e.g., 1 March 2026); E1 = "Period End", E2 = the date (e.g., 31 March 2026). Format the cells as dates so Excel does not treat them as text.

Create a second worksheet, name it P&L. In column A list five account codes that exist in your Xero chart of accounts — for example:

A1: Account
A2: 4000
A3: 4100
A4: 5000
A5: 6200
A6: 7000

In B1 type a header like "Actual". In B2:

=XO.BALANCE(Setup!$A$2, A2, Setup!$D$2, Setup!$E$2)

Press Enter. The first time it runs, give it a moment — XO Report fetches from the Xero API. The cell shows the activity on account 4000 between your Setup dates. Now fill down: select B2, drag the fill handle to B6. Every row pulls its own account. Total time: about thirty seconds.

For variance you also need the budget side. In C1type the header "Budget". In C2:

=XO.BUDGET(Setup!$A$2, "Overall Budget", A2, Setup!$D$2, Setup!$E$2)

The second argument is the budget name as defined in Xero. "Overall Budget" is the default name for a single-budget org; if your budget is named differently (e.g., "FY2026 Plan"), use that string. Match it exactly — the lookup is case-insensitive but otherwise literal.

Fill down to C6. Xero budgets only apply to P&L accounts (Revenue and Expense); Balance Sheet accounts will return 0 because they cannot have a budget. If you see an error, it usually means the budget name does not match — the error message lists the available budget names in your org.

Variance is plain Excel — no XO formula. In D1type "Variance". In D2:

=B2-C2

Fill down. Now format the table: select B2:D6, set the number format to your local currency. Highlight column D and apply Conditional Formatting → Highlight Cells Rules → Greater than with value 0 and format green; repeat with Less than 0formatted red. That's the standard variance colour code.

Test the workbook: change Setup!E2from 31 March to 30 April. Every B, C, and D cell updates — actuals, budget, variance — for the new period. Hand the file to next month's analyst with one instruction: "change the dates on Setup, the rest follows."

Two small polish moves worth doing now. First, freeze the header row on the P&L tab so the column labels stay visible as the table grows (View → Freeze Panes → Freeze Top Row). Second, lock the formula columns to prevent accidental overwrites: select B2:D6, set Format Cells → Protection → Locked, then enable Review → Protect Sheetwhile leaving the input cells unlocked. The next analyst can edit dates on the Setup tab but cannot break the formulas on the P&L tab.

One more sanity check before declaring done. Compare a known cell (say B2for the first revenue account) against the same line in the Xero P&L report screen for the matching date range. The numbers should match exactly. If they do not, the most common cause is the wrong account code in column A — Xero account codes are case-sensitive strings, not numbers, so "4000" matches but 4000 as a number may not.