Dynamics 365 for Finance and Supply Chain Management Excel add-in

One of the coolest things added to Dynamics 365 was a proper way to export data to and push data from Excel. When I was using Dynamics AX 2009, I always wanted the ability to do this, and it was always something that my department managers assumed happened because "Dynamics is Microsoft and so is Excel; they should work together." Well, they didn't, but they do now!

Export to Excel vs. Open in Excel

First things first, where do you find the Open in Excel button? Almost every form in Dynamics has a little icon in the upper right of the ribbon bar that looks like a little Office window.

Depending on which form you're on, you'll get one or two options. What you'll always see is an "Export to Excel" section and usually a single option underneath it. This option is great - it takes what you're looking at and exports a static version of it to Excel. If you're looking at a list of vendors, and you want to export it to Excel so you can count the number or vendors, or filter on a subset and send them to someone in an email, Export to Excel is what you want.

If you want to open the vendor list in Excel and edit vendors or create / delete records from Excel, you'll need to pick one of the options from the "Open in Excel" section. Ignore the ones that say "[Obsolete]" and pick the highest version of the choice you're looking for. In the screenshot above, that'd be "Vendors V2 (usmf)". If you don't see an "Open in Excel" option, you can ask a developer or system administrator "is there an entity for the data associated the {insert form name} form? if so, do I have access to it?" If the answer to either of those questions is no, you might not be able to see the "Open in Excel" option.

Open in Excel

As you can see in the screenshot above, you'll see that Excel opens with the data that you're looking for and a fancy little add-in on the right. Before we go too far, here's the process if you want to edit a record in Excel and push it back to Dynamics 365.

1. Click the "Open in Excel" button
2. Download the Excel file and open it
3. Make sure the "Microsoft Dynamics" add-in is installed and connected - it might make you login to Dynamics 365 first or trust the app - do both.
4. Wait for the file to update and refresh. If you're neurotic like me, you might even click the "Refresh" button in the add-in to make sure that the data in the spreadsheet is the most up-to-date data possible.
5. Edit the cells that you want to change
6. Click the publish button

This file that gets created when you open in Excel is asynchronously connected to Dynamics 365. What this means is, you can see and make changes to the cells inside Excel, but until you hit the "Publish" button, these changes don't get pushed to Dynamics 365.

That's it.

Things to watch out for / more to know

• The Excel add-in can do all the CRUD operations (create, read, update, and delete). If you delete a row from the Excel sheet and hit the publish button, the Excel add-in will try to delete the record. I've seen people Open in Excel, delete the rows in the spreadsheet they're not interested in, make the changes they want to update, and then hit publish. They must have ignored the note saying the add-in was going to try to delete a bunch of records, but they deleted all the records in Dynamics 365 associated with the rows in Excel they deleted!

• You can change the fields that are included in the Excel spreadsheet if there's some values you want to update but didn't show up when you initially downloaded the file. To do this, you click on the "Design" button in the Excel add-in and choose from the fields that are available in the data model. If you make changes (either adding or deleting fields), make sure you hit "Refresh" before you start to change data. The Excel add-in will require that you refresh before you Publish, and when you do that, it'll wipe all your changes out. If you can't find fields that you were looking for that you think should be there or are related to the data you're trying to edit, you'll probably need to have a developer add the fields to the OData entity.
• Before you publish your data, make sure that you don't have any selections or filters on your data in Excel. It's hard to replicate, but I've run into issues before when trying to publish data after bulk updating something and still having a range selected. After deselecting the data, the add-in was able to publish successfully.
• The Excel add-in is meant for smaller data sets. If you need to update tens or hundreds of thousands of records, try DIXF. It uses the same OData data entities that the Excel add-in does, so it's pretty straightforward to use. Remember, though, to Test, Test, Test. If you have the privileges required to change the data, you also have the possibility to royally screw things up. Make sure that you test using DIXF in a test environment. DIXF needs values in each of the columns that are mapped in the spreadsheet you're importing (and the Excel add-in doesn't), so make sure you know what you're doing (maybe I'll write a little article about DIXF).

If you like this type of content but you'd rather hear it in person from an awesome presenter with a few thousand of your closest Dynamics 365 professionals, join us at Dynamics Con in Chicago on May 13-16, 2024.

Happy Excel-ing.