How to Use Excel Reports in Dynamics 365 Business Central

By Marcus Haug | February 21, 2024

Using Excel reports in Dynamics 365 Business Central allows you to export data from standard reports into Excel files, where you can customize and manipulate it easier.

You can use Excel’s built-in tools such as pivot tables, charts, and formulas to analyze, visualize, and present your data in a customized way. Despite its innovation, this function is something we find our clients underutilize. In this blog, we will highlight the benefits of Excel reports and show you how to set up and run them in Dynamics 365 Business Central.

How to Run Excel Reports in Dynamics 365 Business Central

To show you how to run this process, we will use an aged receivables report as an example. This report shows the outstanding balances of your customers by aging buckets, and it is suitable for creating a chart for demonstration purposes.

To run the report, go to Reports > Finance Reports and select the “Aged Accounts Receivables” report.

Excel Reports Dynamics 365 Business Central reports

You can specify the “Age as of Date” as you wish. Then, click Preview to see the report in the standard RDL format. This is the familiar layout that shows the detailed information of each customer and their balances.

Excel Reports Dynamics 365 Business Central report preview

However, you may notice a new feature at the top of the report preview. This is the “Report Layout” option, which allows you to choose a different format for the report. In the past, you had to go to the “Report Layouts” page to create or modify the report layouts, but now you can access them directly from the report preview.

Excel Reports Dynamics 365 Business Central report layout

  • Notice the “Report Layout” option at the top of the report preview by clicking on the ellipses, which allows you to choose a different format.
  • Select the “Excel Spreadsheet” format and click Preview again.

Excel Reports Dynamics 365 Business Central excel

  • You will see a message that says the report is being generated. When the report is ready, you can download it by clicking the download button at the bottom of the message.

Excel Reports Dynamics 365 Business Central excel report layout

  • The downloaded Excel file contains several tabs.
    • The first tab is the “Data” tab, which shows the raw data of the report in a table format.

Excel Reports Dynamics 365 Business Central excel download

    • The second tab is the "Report Metadata" tab, which shows the parameters that you used to run the report.

Excel Reports Dynamics 365 Business Central metadata tab

    • The other tabs are optional and depend on the report layout that you created or modified. For example, you may have a tab that shows a pivot table or a chart based on the report data. We will explain how to create these tabs in the next section.

Excel Reports Dynamics 365 Business Central chart

How to Create Excel Reports in Dynamics 365 Business Central

There are two easy, convenient, and straightforward options to create a new Excel spreadsheet report in BC.

OPTION 1: To create a new Excel spreadsheet within the report, you follow these steps:

  • Run your report in BC.

Excel Reports Dynamics 365 Business Central run report

  • Send to “Microsoft Excel Document (data only)” and it will give you the “Data” and “Report Metadata” tabs. You can then create a new sheet and pull data from either table to build pivot tables, etc.

Excel Reports Dynamics 365 Business Central microsoft excel document

OPTION 2: You can also create a new Excel spreadsheet using the “Report Layouts” page. This page allows you to manage the different formats of the reports, such as RDL, Word, and Excel.

  • Run your report in BC.

Excel Reports Dynamics 365 Business Central run report

  • Select the “Report Layout” option by clicking on the ellipses.

Excel Reports Dynamics 365 Business Central report layout

  • Go to the “Report Layouts” page and click “New Layout”.
  • Enter a name for your new report layout and select the Excel spreadsheet option.
  • Ensure to include the report ID number. You can search for the report name in BC. For example, this aged receivables report has the report ID 10040.
  • Click OK to create the report layout.

Excel Reports Dynamics 365 Business Central new layout

  • Drag and Drop your Excel layout file

Excel Reports Dynamics 365 Business Central choose file

  • Run your report, check the parameters to ensure accuracy, and download.

Excel Reports Dynamics 365 Business Central download report

Want to Learn More About Dynamics 365 Business Central Processes?

We hope you found this helpful and encourage you to spend some time running Excel reports in Dynamics 365 Business Central and discover the benefits it may bring to your organization. For more information on features like this, reach out to us at Stoneridge Software.


Under the terms of this license, you are authorized to share and redistribute the content across various mediums, subject to adherence to the specified conditions: you must provide proper attribution to Stoneridge as the original creator in a manner that does not imply their endorsement of your use, the material is to be utilized solely for non-commercial purposes, and alterations, modifications, or derivative works based on the original material are strictly prohibited.

Responsibility rests with the licensee to ensure that their use of the material does not violate any other rights.

Start the Conversation

It’s our mission to help clients win. We’d love to talk to you about the right business solutions to help you achieve your goals.

Subscribe To Our Blog

Sign up to get periodic updates on the latest posts.

Thank you for subscribing!