View and edit Business Central data with integration to Microsoft Excel

A major benefit of implementing Microsoft business applications like Dynamics 365 and Microsoft 365 is the interconnectivity between applications and data. Microsoft Excel is currently one of the most popular software applications available for viewing and manipulating data.

For users of Dynamics 365 Business Central, the integration between Business Central and Microsoft Excel provides the ability to view and edit financial data from Business Central directly in Excel and import it back into Business Central. In this blog we’ll review how this works and showcase an example of how to do it.

Opening Business Central data in Excel

There are times when ERP users simply want a better way to review data like customers, invoices, or purchase orders. With the click of a button, Business Central users can export record lists into an Excel spreadsheet. Whatever filters and columns are applied to the data list in Business Central will also be applied to the spreadsheet in Excel. Once the data is in Excel it can be manipulated and changed, but cannot be published back into Business Central. The spreadsheet can be saved as a separate Excel file on a computer or cloud drive. The open in Excel feature is available on both Windows and mac OS devices so all users can take advantage of viewing data in Excel.

In the example below, we have opened a list of recent sales invoices in Business Central. We can view this data in Excel by locating the Page tab and clicking the Open in Excel option. The spreadsheet will immediately begin downloading locally onto the device. It’s important to note that download time can depend on both the speed of your internet and amount of data in the list.

Editing Business Central data in Excel

Editing Business Central data in Excel follows the same process as viewing data in Excel except that changes made in the spreadsheet can be uploaded back into Business Central. This is a major advantage for users who are used to manipulating data in Excel versus a web based ERP application. When launched, the Dynamics Office Add-in for Excel will pop-up in the spreadsheet.

The user will need to sign into the add-on with their credentials before accessing the Business Central data in the spreadsheet.

From there, the user can make changes to the data within the spreadsheet. When the data is ready to be brought back into Business Central, the user simply clicks the publish button in the Add-in window on the spreadsheet.

To see the data changes reflected in Business Central, the user just needs to refresh the browser window. It should be noted that editing Business Central data in Excel is currently only available on the Windows operation system.

To see both viewing Business Central data in Excel and the editing process, check out Microsoft’s video that walks through both processes.