01Decide what the report must answerName the question, companies, period, columns and Xero totals you need.
02Keep the right details linkedMake sure every row still shows which company, customer, invoice and account it belongs to.
03Save the set-upKeep the companies, columns, filters and sort order ready for the next run.
04Keep a record of each runNote when it ran, what it covered and any figures changed by hand.
1. Decide what the report must answer
Many businesses follow the same routine every month: export reports from Xero, paste them into Excel and fix the same layout problems again. A reusable report starts differently. Before you export anything, write down the question you need the report to answer.
For example: Which customers owe us more than $5,000, and how long has each amount been overdue? Then list the companies, months and columns you need. Exporting everything just in case creates more information to clean and more room for mistakes.
Also write down which total must match Xero. If you are preparing an overdue-customer report, the total owed in Excel should match the same date and company in Xero's Aged Receivables report. This check is called reconciliation: confirming that two sets of numbers agree.
2. Keep the right details linked
In Xero, one invoice links to a customer, its line items, the accounts used and any payments. Separate exports can lose those links. You may then spend time using VLOOKUP or XLOOKUP formulas to join the pieces again.
A dependable Excel report keeps each row labelled with its company, customer, invoice and account. This matters when two companies use the same invoice number or spell a customer name differently. Stable IDs are safer than matching on names alone.
If you combine several Xero organisations, include the company name on every row. That one column prevents a great deal of confusion when someone filters, sorts or copies part of the report later.
3. Save the set-up
A normal export is a snapshot. Next month you start again. A reusable set-up remembers which companies, columns, filters and sort order you chose, along with any account or customer matching rules.
Check the first result carefully before you automate it. Confirm the date range, company list, column meanings and Xero totals. Once the set-up is correct, save it as the approved starting point for the next run.
If the report will run on a schedule, choose a sensible time and an owner who will review the result. Automation saves effort, but it does not remove the need to notice an unexpected balance or a failed refresh.
4. Keep a record of each run
If someone asks where a number came from, you should be able to answer. Keep the run date, companies, reporting period and filters with the result. Note any figures changed by hand and explain why they changed.
Do not overwrite the original figures. Add adjustments beside them or keep a separate adjustment sheet. This leaves a clear path from the Excel result back to Xero and makes the next review much easier.
A new staff member should be able to open the file and understand how it was prepared without relying on the person who built it. That is the practical test of whether the report is truly reusable.
WORKED EXAMPLE
Worked example: Sam's monthly overdue-customer report
Sam looks after three Xero companies. Every month, Sam exports three Aged Receivables reports and combines them in Excel.
Part of the job
Rebuilt every month
Reusable set-up
Choose companies and dates
Selected again in each Xero file
Saved once and checked before each run
Combine customer information
Copied and matched with formulas
Company and customer details stay linked
Check the total
Manual comparison after every copy-and-paste
A named Xero total is part of the review checklist
Typical preparation time
About two hours
A short refresh and review
Main risk
Old formulas, missed rows or mixed company data
Changes remain visible and the same checks are repeated
PUT IT INTO PRACTICE
Checklist for a report you can reuse
The question the report must answer is written down.
The companies, period and required columns are listed.
The totals that must match Xero are named.
Every row keeps its company and source-record details.
The set-up is saved only after the first result is checked.
Manual changes are noted without overwriting the original figures.
COMMON QUESTIONS
Questions people ask about this work
Can't I just use Xero's own Excel export?
Yes, for a one-off report. The limitation appears when you repeat the same work, combine companies or need information from related records. You must then preserve the set-up, links and checks yourself.
What if a customer has a different name in two companies?
Do not rely on the visible name alone. Keep the Xero contact ID and the source company, then record the rule used to treat the two contacts as the same group customer.
How do I know my Excel totals match Xero?
Choose a control total before you begin, using the same company, date and status in both places. Compare that total after every run and investigate any difference before using the report.
Should every report be automated?
No. Automate only after the first result has been checked and the business question is stable. Reports involving judgement may still need a person to review each run.
Written and reviewed by the KalkuleX product team.
