Using Excel Layouts to Run Reports in Dynamics 365 Business Central

Excel Layouts in Dynamics 365 Business Central are a powerful tool for financial professionals, accounts payable (AP) and accounts receivable (AR) clerks, and anyone working with Business Central. These layouts allow users to extract data and customize reports directly in Excel.

In this article, we’ll explore Excel Layouts use cases, their benefits and limitations, and how to create a custom report layout step by step.

*Note – Excel Layouts are not designed for printed reports.

Use Cases

Excel Layouts enable users to pull data from any Business Central report that you’re running and export it to Excel, making it more configurable and consumable.

This is particularly useful for users creating reports on the Balance Sheet, multiple versions of the Trial Balance (TB by Period, Budget, and Previous Year), Aged Account Receivables/Payables, Income Statement, and more.

How to Create a Custom Report Using Excel Layouts

Step 1

Use the Searching feature in Business Central to find the required report. In this example, I am going to export the Customer Sales List.

You can see the available out-of-the-box reports within Business Central here.

Screenshot in Business Central showing the search results for "Customer sale" highlighting the report "Customer - Sales List."

Step 2

Adjust any of the options and filters on the report page and select ‘Send to’ once your report is set to your requirements. You can find further assistance regarding the different options and filters here. Next, choose the Microsoft Excel Document (data only) file type and click ‘OK.’

Screenshot in the Customer -Sales List under Choose file type highlighting "Microsoft Excel Document (data only)."

This format extracts the raw data from the system and presents it in a table format, as seen in the screen clip below:

Screenshot of raw data in Excel.

Until now, this raw data format has not been practical for users to extract and analyze their data. Any changes made to the file would not be saved, requiring the user to duplicate the analytics every time the report was run. With Excel Layout capability, users can now save their file and store it as the default report layout within Business Central.

Step 3

In the generated Excel template, the user can create customized tables to represent the data in a visual format. Get creative in designing reports by adding custom tables, pivot tables, slicers, graphs, etc. Users can define as many worksheets within the report layout as they desire. Excel offers many powerful data analysis tools that can be used to help you create that first report layout.

*Note – do not use any password protection or document sensitivity as Business Central will not be able to validate the file when you upload.

In a simple example, we can create a customer mailing list by formatting the raw data from the Customer Sales report into a table with very specific fields:

Screenshot in Excel of the data formatted.

In a more detailed example, using the same report from Business Central, I have created multiple worksheets analyzing the same data within the report:

Screenshot in Excel of worksheets analyzing the data.

Here is an example showing an inserted pivot table to analyze the total sales of each customer, an added bar graph for visual effect, and inserted slicers so users can filter the data:

Screenshot of more detailed ways to analyze the data including pivot charts and slicers.

Step 4

Once the report has been defined, the next step is to save the file in a location that you prefer and then upload it into Business Central. Use the Searching feature to find “Report Layouts.”

Screenshot in Business Central with the seach "Report Layout."

Search for the report you would like to override with your Custom Excel Layout. Click on the report line and select ‘New’ from the ribbon.

Screenshot in the Report Layouts window highlighting "New" and the report line "Customer - Sales List."

Enter the new Layout Name. You can add a description of the custom report, and choose the Excel format option. Click ‘OK’ and select your custom Excel file that you just saved.

Screenshot in the "Addd New Layout for a Report" window.

Step 5

To ensure this custom layout will be the format presented when Business Central runs the requested report, you need to define your Excel file as the default report layout by clicking on the line of the custom report and using the function found in the ribbon called “Set Default.”

Screenshot in the Report Layouts window highlightng the line "Customer - Sales List" with the layout name "VCustom Customer Sales List" and "Set Default" in the top ribbon.

Now, when you choose to run the report in Business Central, you want to select the download option, which will download the Excel file with all the custom analytics you created. When opening the Excel report, if the protected view is turned on, you must click ‘Enable Editing’ for the data to update accordingly.

Screenshot in the Customer - Sales List window highlighting "Downlaoad."

Tip: You could also click ‘Send to’ and select the Schedule option. After changing the Report Output Type to Excel and setting up a frequency you would like this report to run, it will be viewable on your Role Center (homepage of Business Central) under the Insights section for you to review without having to search the report every time.

Screenshot of the Role Center in Business Central highlighting the Report Inbox section.

You can also collaborate with team members within the file using comments and notes. You can share the document and protect the workbook from user edits with password and sensitivity functions.

Screenshot of the Share option in Excel. Screenshot of the Send Link option in Outlook.

Excel Layouts empower you to transform raw data into valuable reports for your organization, allowing for creative and customized reporting.

Benefits and Limitations

Benefits

You can easily extract data from Business Central to configure data and build reports directly in Excel.

This enables you to create multiple custom reports all in one Excel spreadsheet. These reports can include pivot tables and charts and use formulas to present your data in a meaningful way, allowing you to analyze the data differently from how Business Central presents it.

Once an Excel Layout has been created, Business Central will automatically keep your credentials whenever you generate the report again.

Limitations

Using Excel Layouts for reporting does have some pitfalls. It is best to use this reporting method when you want to quickly summarize data in a way that is easier to consume than Business Central presents.

These are static worksheets; you cannot push the data you customize back into Business Central. The report will present a snapshot of the instance within the system from which you exported the data.

To learn more about reporting in Dynamics 365 Business Central, visit our reporting tools article.

Please connect with us to learn more about Business Central and using Excel Layouts.

Excel Layouts for Reporting in Dynamics 365 Business Central

In this webinar, learn how to create, manage multiple layouts, and use custom Excel layouts for reporting in Dynamics 365 Business Central.

October 8
9:00 am – 9:30 am PST

Register here

Webinar - Excel Layouts for Reporting

Oct. 8
9:00 am – 9:30 am PST

Register