Integrate financial reports with Excel

The Financial Reporting feature offers multiple ways to view the reports in Excel. This integration makes it easier to generate your monthly and yearly financial statements in a format that works for you. You can:

  • Download the report to Excel and use it as the starting point for a formatted template.
  • Upload and store Excel templates as Excel layouts in Business Central.
  • Define a default Excel layout for the report.

This article describes each approach.

Set up Excel integration for a financial report (create an Excel template)

To set up Excel integration for a financial report, follow these steps to create an Excel template.

  1. Choose the [Lightbulb that opens the Tell Me feature 4.] icon, enter Financial Reports, and then choose the related link.
  2. On the Financial Reports page, select the financial report to enable with Excel, and then choose the View Financial Report action.
  3. On the Financial Report page, choose the Open in Excel (using layout) action to download a template Excel workbook with a worksheet named after the report's row definition. Business Central only creates a new workbook if you don't specify a default Excel layout for the report. Learn more in Working with Microsoft Excel layouts.
  4. Copy the worksheet for the formatted report to a new worksheet.
  5. In the original report worksheet, mark all cells that show data from the financial report, including column and row headers. On the Home ribbon, find the Number menu and choose General as the category.
  6. Choose the leftmost cell of the area with data from the financial report, and set a reference to the equivalent cell in the copied worksheet. Drag the formula to extend it to all cells in the first row, and then drag the row down to cover all rows in the financial report.
  7. Hide the original worksheet.
  8. Format the copied worksheet to suit your needs.
  9. On the Financial Report page, choose the Excel Layouts action, choose Import, and then choose the workbook.
  10. Close the workbook.

Tip

The Row No. field shows on the data worksheet, but you might not want to include it on your worksheet.

Note

If you make changes to the row or column definitions of the report you created a template for, you must update the template too. Each row and column in the Excel template must be in the same position as the generated report.

Format your report worksheet with Copilot in Excel

When you export data from a financial report to Excel by using the Open in Excel (using layout) action, you can use Copilot in Excel to help prepare and format the workbook. Describe the changes you want in natural language. Copilot can edit the workbook directly in edit mode or propose a plan first in plan mode. Review and verify its changes before you use the report.

An example prompt could be (adjust to your style):

I need to build a balance sheet report based on the data in the BS worksheet.
The data was generated from the Financial Reporting feature in Business Central.

Keep the BS worksheet as is, but hide it.
My report should show in additional worksheets.
It's OK to add multiple worksheets for different types of analysis.

I want the report be formatted professionally with headers in bold, and amounts formatted as currency (rounded to nearest integer).
Totals should have a line above them.

Learn more in Use Copilot in Excel to enhance data tasks.

Example: Use and update an Excel template to create a monthly report

You can create an Excel template that contains the reports that you include in your reporting package. Each report in the workbook must have a data tab and a tab that has the formatted report. When you update your monthly package, use the Update Copy of Existing Document for each report. Be sure to save after each update.

To produce your monthly financial report, follow these steps:

  1. Choose the [Lightbulb that opens the Tell Me feature 4.] icon, enter Financial Reports, and then choose the related link.
  2. Choose the financial report you used to create the Excel template, and then choose the View Financial Report action.
  3. Set the date in the Date Filter field to the next month.
  4. Choose the Open in Excel (using layout) action.
  5. The Excel workbook is updated with data and downloaded for you. Be sure to enable editing to show the changes when you open the workbook.
  6. Save the workbook with a different name than the original template.

Save Excel templates as Excel layouts

INTRODUCED IN: Business Central 2025 release wave 1.

Instead of saving your Excel templates locally, you can import them into Business Central with the Excel layouts for Financial Reporting feature.

Manage Excel layouts for a financial report

Use the Financial Report Excel Layouts page to create, import, export, run, and delete layouts. To manage Excel layouts for a financial report, follow these steps:

  1. Choose the [Lightbulb that opens the Tell Me feature 4.] icon, enter Financial Reports, and then choose the related link.
  2. On the Financial Reports page, select the financial report to enable with Excel layouts, and then choose View Financial Report.
  3. On the Financial Report page, choose Definitions, and then Excel Layouts.

Create a new Excel layout for a financial report

To create a new Excel layout for a financial report, follow these steps:

  1. Open the Financial Report Excel Layouts page for the report.
  2. To create a new layout, fill in the Code, Description, and File name fields. Business Central prepares a new workbook for you in the background.
  3. To download the Excel template, choose the Export/Run action.
  4. Create your layout using the steps described in Set up Excel integration for a financial report (create an Excel template).
  5. When your layout is ready for testing, close the Excel workbook and choose the Import action. Upload your Excel workbook.

Run a financial report with an Excel layout

To run a financial report with an Excel layout, follow these steps:

  1. Open the Financial Report Excel Layouts page for the report.
  2. Select the row with the layout, and then choose the Export/Run action.

Replace an Excel layout for a financial report

To replace a financial report with a new Excel layout, follow these steps:

  1. Open the Financial Report Excel Layouts page for the report.
  2. Select the row with the layout, and then choose the Import action. Upload the new Excel workbook.

Define a default Excel layout for a financial report

To define a default Excel layout for a financial report, follow these steps:

  1. Choose the [Lightbulb that opens the Tell Me feature 4.] icon, enter Financial Reports, and then choose the related link.
  2. On the Financial Reports page, choose the financial report, and then choose the View Financial Report action.
  3. On the Options FastTab, in the Default Excel Layout field, choose the default Excel layout for the report. By default, the field is hidden, so you might need to choose Show more on the FastTab.

Run a financial report with a default Excel layout

To run a financial report with a default Excel layout, follow these steps:

  1. Choose the [Lightbulb that opens the Tell Me feature 4.] icon, enter Financial Reports, and then choose the related link.
  2. On the Financial Reports page, open the financial report.
  3. Choose the Export to Excel/Print action, and then choose the Open in Excel (using layout) action.

Contributors

Microsoft maintains this article. The following contributors provided parts of the examples.

Use Copilot in Excel to enhance data tasks
View a financial report
Prepare financial reporting
Financial analytics overview
Finance
Setting Up Finance
Work with Business Central

Find free e-learning modules for Business Central here