How to Use Excel Add-In: Dynamics 365 Finance and Operations

The Dynamics 365 Finance and Operations Excel Add-In has greatly improved since earlier AX 2012 versions. The Excel Add-In in AX 2012 was very fickle and buggy to say the least. In Dynamics 365 Finance and Operations (D365) Microsoft has built the Excel Add-In on top of the data management framework, utilizing data entities to retrieve, update, and add data to D365. The improved Excel Add-In has some new features that I have not seen deep coverage on and in this article, I aim to show you some of the coolest features and tips I have come across while working with it. For more in depth help in other modules check out these user guides.

How to Open Excel Add-In

On various forms, list pages, and grids you will find a Microsoft office icon shown below.

On some list pages and forms when you click this icon you will see drop down that shows two options. One option header will be “Open in Excel” and the other option header will be “Export to Excel”.

“Open in Excel” means that there is a data entity which you can connect to using the Microsoft Excel Add-In. This creates a dynamic connection to the data entity which can be used to update, create, and refresh data to and from the system.

“Export to Excel” will launch a static export of the selected data to Microsoft Excel and does not contain any of the functionality that will be described in this article. For the purposes of this article I will focus my attention on the “Open in Excel” functionality.

In this first example I will use the “Sales Order Lines V2” found in Sales and Marketing > Sales orders > Open orders > Backorder lines.

• To use the Excel Add-In navigate to your desired data set and click the Microsoft Office icon. Click the data entity you want to connect to using the Excel Add-In and make sure you select the option under the header "Open in Excel".

• Click the “Download” button on the dialog to download the excel workbook.

• When the workbook finishes downloading click the Excel document to open the document. I am using Chrome so yours may look a little different depending on the browser you are using.

• To enable the Add-In click “Enable content”.

• If this is your first time using Excel Add-In you may need to enable the add-in by clicking the “Insert” tab in the Excel ribbon and click “My Add-ins”.

• Then select the “Microsoft Dynamics” Add-In and click “Add”.

The Excel Add-In may also ask you to sign in if this is the first time you are using it. Just sign in using the same credentials you used to log into D365.

Once you do this you should see all of you data populated in the spreadsheet. Getting the data in the spreadsheet is great but there is so much more you can do read on to find out.

Update Data

The Excel Add-In functionality allows you to update data and since the updates occur through the data entity all CRUD (Create, Read, Update, and Delete) operations go through the standard D365 security framework and respect the user’s security role and security restrictions.

In the example below I want to update the prices of multiple sales order lines. So all I need to do is make the updates directly in excel and click the “Publish” button. You can see the number of records published and if you go back to the data in D365 you will see the data has been updated.

If I go into the system I can see the records I am going to be updating.

After I publish the data I can go back into D365 and refresh the form and I will see the published changes to the data.

Create Data

The Excel Add-In also enables you to create data. In the same Excel sheet, you can add a record and fill in the relevant and required fields and click the “Publish” button and publish records to D365. For more details on how to do this check out my article How to Load Data. One tip I have picked up along the way is that if you add a record make sure you extend the table to include the new record. What I see sometimes is users add a record below the last row and try to publish it and they will get an error because the Excel Add-In does not see the new data. Drag the table edge as shown below to encompass the new record(s).

Filter

When you open the Excel Add-In and the data first populates in the Excel sheet the entity may be pre-filtered depending on which entity you connected to and the context where you clicked “Open in Excel”. For example, my “Sales Order Lines V2” entity is pre-filtered to just show sales order lines with status “Open order” which means the line has not been fully shipped. If I wanted to see all sales order lines or only shipped sales order lines, I can modify the filter to show me only the records I want to see. In this example I want to just remove the filter so I can see all the sales lines.

• To do this click the “Filter” icon in the data connector pane.

• Click the trashcan icon to remove the filter and click "Done".

• Click the “Refresh” icon to refresh the data and pull in the unfiltered data.

Modify Design

There are cases where you may want to add, remove, or rearrange the fields that are shown in the Excel sheet. You can do this by using the “Design” button in the data connector pane.

• Click the “Design” button in the connector pane.

• Click the “Edit” button in the connector pane.

In the connector pane you will now see two sections. The upper section is called “Available fields” the lower section is called “Selected fields”. The selected fields are the fields you currently see in the Excel sheet.

• You can add a new field to the sheet but double clicking any field in the “Available fields” section. You will then see it appear in the selected fields section.

• You can order the fields in the way you want to see the fields in the Excel sheet by clicking on the field you want to move and click the “Up” or “Down” arrows. The fields at the top are displayed first in the Excel sheet. The first field will have the A1 position the second field will have the B1 position and so on.

• If you want to remove a field from the Excel sheet, simply double click a field from the “Selected fields” section or click the “Remove” button. You will then see the field move up to the “Available fields” section.

• When you’re done with your changes you can click the “Update” button and you will see the changes you have made reflected in the Excel sheet.

Create a Calculated Field

There may be cases where you want to use the power of Excel to create a calculated field. If you just create the manually create the calculated field in the Excel sheet you will lose the field when the data entity is refreshed.

• To create a calculated field that will bind to the data entity and remain intact after a data refresh, go to the design form as shown in the section above.

• In the design form click the “Formula” button.

You will see a new column get created called “Column 1” you can rename this field by typing the label in the “Column label” field (with the tag icon).

• Click the “Update” button and you will now see your new field appear in the Excel sheet.

• Now what you can do is enter your formula in the Excel sheet and copy that formula into the “Formula” field (Sigma icon) in the connector pane.

• Then you just click the “Update” button and there you have it. You have a calculated field that will update along with the data you refresh directly from Dynamics 365 Finance and Operations.

Dynamic Reporting

Now that you’ve got you’re data, the fields you want, in the order you want them, and calculated fields you can save this file and save all the work you’ve done to your local computer or network drive. When you re-open the file it will automatically refresh the data in the file. How cool is that!? This enables what I call “Dynamic Reporting”. This means you could use the power of Excel to build sophisticated reports, KPI’s, and analytics to meet your business reporting needs. When you create your pivot or pivot chart make sure you use the select all triangle in the top left corner next to the A1 cell this will ensure that all new data is seen by your pivot or chart and all you need to do is refresh the pivot table to see the new data.

• If you do not want the data to automatically be refreshed, you can change this in the data connector settings in the connector pane by clicking the “Settings” button.

• Open the “Data connector” tab and un-check the “Refresh on open” option. Click “OK”.

Now the next time you open the spreadsheet, the data will not automatically refresh you will need to click the “Refresh” button in the data connector.

Takeaways

I hope you were able to see the power of the new Excel Add-In and how it can be used to not open manipulate data but serve a powerful reporting tool that can directly connect to the Dynamics 365 Finance and Operations ERP system.