How to consolidate tracking category budgets in Xero
If your Xero organisation uses tracking categories — say, one budget per Department, Region, or Project — you've probably hit the same wall: Xero lets you build a budget against each tracking option, but it does not roll those tracking budgets up into a consolidated total.
You can run a Profit & Loss report broken out by tracking category, and Xero will gladly compare actuals against each individual tracking budget. But the moment you want a single "Overall" budget that equals the sum of every department, you're on your own.
This post walks through how to consolidate tracking category budgets in Xero the manual way, and where the manual process breaks down.
Why consolidation matters
A few common reasons teams want a consolidated budget:
- Board reporting — leadership wants one company-wide budget number to compare against actuals, not nine departmental ones.
- Cash-flow planning — the finance team needs total monthly outflows across all cost centres, not silo by silo.
- Performance reviews — comparing rolled-up budget vs. actual at the GL level (without the tracking dimension) makes variance discussions cleaner.
If your departments are budgeted independently in Xero, none of these require new data — they require summed data.
The Xero limitation
Inside Reports → Budget Manager, you can create:
- Overall Budget — one per organisation, no tracking dimension.
- Tracking Budgets — one per tracking option, e.g. one for "Sales", one for "Marketing", and so on.
There is no built-in way to say "set my Overall Budget equal to the sum of all my tracking budgets." You have to do the maths yourself, then import the result.
The manual consolidation workflow
The pattern is always the same:
- Export each tracking budget
- Combine them in Excel
- Import the consolidated total back into Xero as the Overall Budget
1. Export each tracking budget
In Xero, go to Reports → Budget Manager. At the top of the page you'll see four selectors:
- Select Budget — pick the tracking budget you want to export (e.g., a region like "South").
- Start — the start date of the budget (e.g., "Jan 2026").
- Actuals — how many months of actuals to bring in alongside the budget. Defaults to 3 months; options are None, 3, 6, or 12.
- Period — the length of the budget measured in months from the start date. Options are 3, 6, 12, or 24 months.
Set those to match the budget you're consolidating (use the same Start, Actuals, and Period for every export so the columns align cleanly when you stack them in Excel), click Export, and choose CSV — not XLS. Repeat for every tracking budget you want to consolidate.
Why CSV when you're consolidating: the XLS export is formatted as a P&L with section headers ("Revenue", "Operating Expenses"), subtotal rows, and blank spacer rows. Xero's importer is happy to take that shape back in, but you won't be — the moment you start summing across files, those subtotal rows could double-count (summing detail rows and the subtotal that already includes them if row names are the same) and one stale formula reference silently misses a whole section. The CSV export is a flat row-per-account table that SUMIFS can operate on cleanly without the arithmetic gotchas.
You'll end up with one CSV per tracking option — budget-sales.csv,
budget-marketing.csv, etc.
2. Combine them in Excel
Open each export in Excel. Each row is an account (formatted as
Account Name (Code) — e.g., Cost of Goods Sold (500)); each column
after the account is a month (e.g., Jul-2025, Aug-2025, ...).
The most reliable approach is to put each tracking budget on its own sheet in a single workbook, build a "Consolidated" sheet that lists every account once, and use SUMIFS to add the same account across all the source sheets:
=SUMIFS(SalesBudget!B:B, SalesBudget!$A:$A, $A2)
+ SUMIFS(MarketingBudget!B:B, MarketingBudget!$A:$A, $A2)
+ SUMIFS(OpsBudget!B:B, OpsBudget!$A:$A, $A2)
Drag that formula across the month columns and down the account rows
on the Consolidated sheet. SUMIFS keys off the account string, so the
order of rows on each source sheet doesn't have to match — but the
Account Name (Code) value has to be identical across the sheets and
in your Consolidated sheet, character for character. Don't rename or
re-format the account column.
3. Import the totals back into Xero
In Budget Manager, open the Overall Budget and click Import.
Xero expects the same CSV layout it gave you on export — leave the
account column values untouched and keep the MMM-YYYY month columns.
If the import fails, the most common culprits are:
- Accounts that don't match your chart of accounts — usually
because an account was renamed or archived in Xero after the export,
the
Account Name (Code)text in the spreadsheet was edited, or a manual row was added that doesn't correspond to a real account. - Sign conventions — revenue and expenses both go in as positive numbers; Xero handles the sign internally based on the account type.
- Hidden rows in Excel that didn't make it into the CSV save.
Where the manual process breaks down
The manual workflow is fine once. It gets painful when:
- You have 5+ tracking options to combine.
- You re-forecast monthly and need to re-consolidate every time.
- A department adds an account mid-year and your spreadsheet's hard-coded row references quietly stop summing it.
- Your fiscal year doesn't align cleanly with the export's calendar months.
Each of those is a place where a transposed cell or a stale row reference silently understates the consolidated budget — and the error doesn't show up until variance reports come back wrong.
A faster path
We built Budget Consolidator precisely because this loop ate too many hours every month. It connects to your Xero org via OAuth, reads every tracking budget through Xero's API (so account codes and periods align by construction), sums them, and produces a Xero-ready import file for the Overall Budget — no Excel required.
If you do this consolidation once a year, the manual process is fine. If you do it monthly, it's worth automating.