There is a joke I’ve heard that goes something like this. What is the most widely used ERP (Enterprise Resource Planning) System? The answer: Microsoft Excel. By and large, the many forms in Microsoft Dynamics 365 for Finance and Operations allow the system to work with data in many ways. However, there are some scenarios, where it can still be helpful to handle the data in Excel. In this article learn how to export data to excel in D365.
Other Ways To Export
In other articles, I have covered several other ways data can be exported from D365. You can use the data management framework, data entities, Odata, Excel, or X++.
However, the ability to export data to Excel in D365 is by far the quickest and easiest way. Many of you may already be familiar with this process. Nonetheless, it is so useful that I wanted to make sure I covered it.
Export To Excel
To explain, this feature allows users to take any grid on a form, and export data to excel in D365. Next, we will walk through the steps.
First, navigate to the D365 form that contains the grid you are interested in exporting. In this example, navigate to Accounts Receivable>Customers>All customers. Or, type ‘all customers’ in the top search bar, and hit enter.
Notice, you will see a grid that shows a list of customers.
Second, click on the ‘Open in Microsoft Office‘ button in the top right corner of your browser.
Then, select the name of the form in the drop down.
Next, click one of the buttons to save the file. In this example, I clicked the Download button.
Finally, the excel file will either be downloaded, or open in a tab for you to view, depending on what browser you are using.
Edge:
Chrome:
In this example, there are only 30 customers in this legal entity. In your case, you may have many more customers, and the export could take a long time.
View this Microsoft tutorial for more information.
Modify The Columns In The Grid
Importantly, you may have noticed that only the columns in the grid were exported. To make the ability to export data to excel in D365 more valuable, learn how to modify which columns show in the grid.
The ability to change to change what columns show on a grid is called personalization. You do not have to do this. However, this is very helpful if the information you want to export is not already shown on the grid. That said, the information does need to exist on the underlying data sources used by the form.
Insert Columns
First, if you see data that you want to export in the details version of the form, but not on the grid, you can likely add it.
Second, right click anywhere on the header bar of the grid. Then, select ‘Insert columns…‘
Third, click the ‘Select‘ checkbox of any field you wish to add to the grid. Finally, click the ‘Update‘ button.
Hide Columns
Similarly, there may exist columns on the grid that you do not want to export.
In that case, right click on the column header, and select ‘Hide this column‘.
Now that you have the data you want to export, return to the previous section to export data to Excel in D365.
For additional information, see this Microsoft article on more personalization options.
Conclusion
In summary, Microsoft Excel is incredible useful for doing quick review of data. You can sum numbers together, concatenate fields. Or even create a pivot table and charts. Even so, you typically should not use excel for data that you want to update within Microsoft Dynamics 365 for Finance and Operations. Nonetheless, it can be a great tool for a very specific investigation you are doing. Many of you likely are already aware of this functionality. But if not, this feature can be very useful.
