Trial Balance Excel Reports for Xero

Home / Report Templates / Trial Balance Excel Reports for Xero

The Trial Balance Excel Reports for Xero is a simple dashboard that provides a series of reports to display the net debits and credits values for your chart of accounts in Excel. For a Power BI version of this report, see the Trial Balance Power BI Reports for Xero.

The dashboard includes:

• Trial Balance
• Trial Balance by Month
• Trial Balance YTD by Month
• Trial Balance by Company

Not only this, but it can also consolidate data from multiple Xero Organisations (data files) when using the Consolidation OData Feed.

While this is a fully working and standalone dashboard, it can also be used as a starting point that can be further customised or modified as required to fit whatever business rules you need. It is not locked in any way. Because of this, it can also be used to learn how to create power queries, how to create DAX formulas, etc.

Reports Included

Trial Balance

The Trial Balance report is a simple report that displays Accounts down the page and Debit, Credit, Debit YTD and Credit YTD across the page.

You can filter this report by company, year and month.

Trial Balance by Month

The Trial Balance by Month report is a simple report that displays Accounts down the page and months across the page. The values are the net movement (debit less credit) for the month.

You can filter this report by company, year and month.

Trial Balance YTD by Month

The Trial Balance YTD by Month report is a simple report that displays Accounts down the page and months across the page. The values displayed depend on the type of account:

• Profit and loss accounts show the year to date net movement (debit less credit) for the month.
• Balance sheet accounts show the closing balance for the month.

You can filter this report by company, year and month.

Trial Balance by Company

The Trial Balance by Company report is a simple report that displays Accounts down the page and companies across the page. For each company, 4 values are displayed:

• Debit
• Credit
• Debit YTD
• Credit YTD

You can filter this report by company, year and month.

Requirements

To use the report, you will need to:

1. Have an OdataLink Subscription or Trial Account.
2. Connect OdataLink to your Xero Organisation.
3. Configure a Model that includes the following endpoints:

• Accounts
• AccountsClass
• AccountsTotals
• AccountsTypes
• DataFiles
• DatesByMonth
• TrialBalanceMultiPeriodTable

Download and Setup

You can download the Power BI templates from the following links:

• Trial Balance Reports for Xero

There is also a Company Financial Reports template that combines Profit and Loss, Balance Sheet and Cash Flow all in one.

• Company Financial Reports for Xero

Once downloaded, you can connect it to your own data using this article.

How to Refresh the reports

To refresh the reports, follow these steps.

1. Go to the Inputs sheet to enter the Parameters driving this report.
2. Enter the Date Start and Date End for the months you wish to report on.
   This can be any date range but the larger it is, the longer the refresh will take to run.
3. Go to the Data tab in the ribbon, click the Refresh All button and wait for the data to download.
4. Select a Worksheet and use the Filters to display the data you want.

Find an issue with the template?

Let us know of any issues you find with the template. We built it to use the least amount of data possible but this might not suit everyone.