The Consolidated Trial Balance Excel report shows the net change or balances of general ledger accounts for a selected period, aggregated for each business unit.
Run it in the consolidation company after consolidating the entries you want to review. Enter an Ending Date and, optionally, a Starting Date. At least one business unit must exist. The report reads the current company's G/L entries and groups amounts by business unit and the two global dimensions. It doesn't perform consolidation or show separate debit and credit columns.
The Excel workbook contains worksheets that you can use to analyze your trial balance:
- Consolidated Trial Bal. (Print)
- Trial Balance (LCY)
- By Business Unit (LCY)
- Trial Balance (ACY)
- By Business Unit (ACY)
- TrialBalanceData, hidden by default
The Consolidated Trial Bal. (Print) worksheet provides a printable view.
Use the worksheets to analyze data in different ways.
Tip
If you configure OneDrive for system features, the Excel workbook opens in your browser in Excel for the web. Otherwise, the workbook is downloaded to your device.
Note
This report does the calculations when you view it in Excel for the web, or when you download and open it. If a banner displays text about external data connections, you might need to select the Enable content button to load data. Enable content only if you trust the file. The report doesn't connect to any external data sources. Some layouts use Power Query to prepare the analysis worksheets. Depending on your organization's security settings, you might also need to right-click a PivotTable cell in Excel for Windows and select Refresh to update data in the report.
Filters and slicers let you zoom in on the balance as seen from business units, global dimensions, or by account categories and subcategories.
Trial balance (LCY) worksheet
This worksheet shows the trial balance for the consolidation company with amounts in local currency (LCY).
[Screenshot of the Trial Balance (LCY) worksheet]
Tip
The data is shown in an Excel PivotTable. Select any cell in the PivotTable to show the Field List, where you can arrange fields. You can also group or ungroup values and filter data in the PivotTable. Learn more in the following articles:
By business unit (LCY) worksheet
This worksheet shows the trial balance for the consolidation company with amounts in LCY for your business units.
[Screenshot of the By Business Unit (LCY) worksheet]
Tip
The data is shown in an Excel PivotTable. Select any cell in the PivotTable to show the Field List, where you can arrange fields. You can also group or ungroup values and filter data in the PivotTable. Learn more in the following articles:
Trial balance (ACY) worksheet
This worksheet shows the trial balance for the consolidation company with amounts in additional reporting currency (ACY). The report requires that you use ACY. If you don't use ACY, the report doesn't show data.
[Screenshot of the Trial Balance (ACY) worksheet]
Tip
The data is shown in an Excel PivotTable. Select any cell in the PivotTable to show the Field List, where you can arrange fields. You can also group or ungroup values and filter data in the PivotTable. Learn more in the following articles:
By business unit (ACY) worksheet
This worksheet shows the trial balance for the consolidation company with amounts in ACY for your business units.
[Screenshot of the By Business Unit (ACY) worksheet]
Tip
The data is shown in an Excel PivotTable. Select any cell in the PivotTable to show the Field List, where you can arrange fields. You can also group or ungroup values and filter data in the PivotTable. Learn more in the following articles:
TrialBalanceData data worksheet
This worksheet shows the raw data used in the report. Depending on the report, the worksheet shows individual entries or aggregated amounts.
Use this worksheet for data analysis assisted by built-in tools in Excel, such as Excel Copilot when it's available to you, or the what-if analysis tools. You can use Forecast Sheet in Excel for Windows if the data includes a date or time series with consistent intervals and corresponding numeric values.
[Screenshot of the TrialBalanceData worksheet]
Copilot availability depends on your license and your organization's settings. Learn more in Get started with Copilot in Excel.
Other worksheets in the workbook
The Business Central platform always adds three system worksheets to Excel reports:
- TranslationData, with text needed for multilingual reports.
- CaptionData, with text from Business Central fields needed for multilingual reports.
- Aggregated Metadata, with data about the report, and when and how it was run.
All three worksheets are hidden by default. Learn more in System Excel sheets.
Use cases
Use Excel to view a snapshot of your consolidated chart of accounts at a given point to check the net change and closing balance.
Try the report
Try the report here: Consolidated Trial Balance Excel
Tip
If you hold down the Ctrl key while you select the report link, the report opens on a new browser tab. The new tab lets you stay on the current page while you explore the report on the other browser tab.
Make the report your own
You can create your own report layouts with Excel, either from scratch or based on an existing layout. To modify the layout for the report, export the layout (not the workbook that resulted from running the report) from Business Central, make your changes, and then import the layout into Business Central.
Learn more in Task 1: Create the Excel layout file.
Alternative reports
There are several other ways to analyze your balance. Learn more in the following articles:
Contributors
Microsoft maintains this article. The following contributors provided some or all of its contents.
- Kim Dallefeld | Microsoft MVP
- Steve Chinsky | Microsoft MVP
- Rob Delprado | Microsoft MVP
Related information
Set Up Company Consolidation
Balance Sheet (Power BI)
Key finance report overview
Ad hoc analysis on finance data
Power BI finance app
Financial Reporting Financial analytics overview