How to work with Excel in Dynamics 365 Business Central

How to work with Excel in Dynamics 365 Business Central


Business Central makes analyzing and working with your financial data simple with integration to Microsoft Excel. This video provides an overview of how you can export your financial data to Excel as well as modify your data in Excel and publish the changes back to your database and Business Central. Export data to Excel. Business Central allows you to easily export data from a list page to Excel, enabling quick analysis of your data using the extensive data analysis tools Excel provides. For example let’s say you want to create a report from your customer ledger entries that shows quarterly sales by customer. Start by searching for and opening your Customer Ledger Entries page. On each supported list page you will see an Edit in Excel button on the Home tab of the ribbon. Click this button to open the data in Excel. When the workbook opens the Microsoft Dynamics Office Add-in opens, creating a connection to the data and providing controls for working with the data connection including the ability to refresh the data. Once the data is open in Excel we can then use the application’s powerful reporting and charting functionality to work with the data. We can view the quarterly sales by customer we wanted to see by creating a simple PivotTable. Choose to insert a PivotTable from the Insert tab on the ribbon. We then have a simple drag and drop experience for creating the charts and graphs we need. From the available fields we’ll drag them to the appropriate sections of the report. The Customer Ledger Entries page contains all document types for customers. To view sales we can create a filter for the table to view just the posted invoices this week. We can then do some quick data formatting and make any other visual changes we want, and we’re done. This gives us a table with the information we wanted to see. We can also easily add a chart to visualize this information. In a few clicks we can add a PivotChart to our report. Getting the data to Excel enables powerful reporting abilities, all of which can be refreshed through the data connection to ensure that the reports and charts you create are always up to date. Edit data in Excel. Business Central also enables you to easily update records. Let’s start on the Customers list page. In this example we’ll assume we want to update the document sending profile for all our customers. This could take a while if you had to update the field one by one on each customer card. We can speed this up by making the updates to the list of data in Excel. We can open the data in Excel by clicking the Edit in Excel button on the ribbon. Once the data is refreshed in Excel we can make the changes we need. For the Coho winery let’s change the document sending profile value to Email and publish the change. Note that the process to publish the data also includes validation of the data. If the data we had entered and tried to publish is invalid, the row with invalid data is highlighted and a message provides information on the changes made. In this case Email isn’t a profile that has been set up in the system so we need to enter a different profile. We’ll change the document sending profile to DirectFile and quickly fill down the changes to all customers. We can then publish the changes again now that a valid profile has been entered for the lines. Once the changes have been published successfully I can then go back to the customer card and see that the field is been updated correctly for my customers. Conclusion, integration with Microsoft Excel provides quick and powerful data analytics as well as simple options for updating your data. Enjoy working with Microsoft Dynamics 365 Business Central. Business solutions reinvented.

Leave a Reply

Your email address will not be published. Required fields are marked *