The formatting in Account Schedules is fairly basic in Microsoft Dynamics 365 Business Central. However, you can take it to the next level by exporting the output into excel and building a template that references the output and then formats it into something nicer.
For an in-depth demo, view the video or read the summarized transcript below:
The first step is to create a template. Run the report. Go to Actions > Excel > Export to excel > Create a New Document. Save the document as Income Statement Template.
The important thing is to not make any changes to this sheet (M-Income) as you will be referencing the cell data as it is for every row and column. I prefer to save it as a Template and then make a copy of it called ‘Output’. We will print this sheet for our reports.
On the Output page, you make the references to the report. You can add you company name or logo, add a Title, etc. You can get fancy here and format it as needed – use actual formulas, do conditional formatting, shading different areas with colors, links to other data files, add percentages, set print areas, etc. You can insert as many rows or columns even in between on the ‘Output’ page because once you link the cell and copy it over, it always references the right point.
Once you have setup the template, run the report again. This will update the data in the template.
Go to Actions > Excel > Export to excel > Update Copy of Existing Document. Select the original unformatted – M-Income – sheet. You’ll notice the updated values on the Output Page. The formatting is already set, so you don’t need to change anything other than maybe the start dates (some sort of dynamic inputs) and then update the report.
You could add multiple sheets to one workbook as long as you know which ones are the updated sheets. If you want, you can put all of your reports into one workbook so that you can print them all at once and update them with the same dates.
With this method you can be as creative as you want with the Excel formatting and get a visually enhanced version of what you can get out of Business Central. If you would like to learn more about Dynamics 365 Business Central, contact us.
– Jason has over 20 years of financial leadership experience in high growth technology companies. He is a Microsoft Certified Dynamics 365 Business Central Functional Consultant Associate. See Credential.
