To get the most out of your ERP system, you need to be able to extract data from your ERP system into Excel at the touch of a button. You need to be able to create journal entries in an Excel template and load them into your ERP system with the click of a mouse. You need live links between Excel workbooks and records within your ERP system so that when a record is updated by someone in your organization, your Excel spreadsheet reflects that change as soon as you refresh the link. These days, having the ability to pump stale data extracts from your ERP into Excel isn’t sufficient. Fortunately, the Excel Add-in for Finance and Operations provides robust integration between Dynamics 365 and Excel for Office 365.
The Dynamics 365 integration works only with a recent version of Excel; ideally, you should be using Office 365 along with Dynamics 365, both cloud-based versions. Make sure that your version of Excel is at least 16.0.6868.2060. To see the version number, start Excel, choose Account from the File menu, and then choose About Excel. The version number appears in the upper left corner of the About window.
The way that Excel works with Dynamics 365 for Finance and Operations is by way of an Excel add-in. An Excel add-in is a program you install that extends the functionality of Excel. These add-in programs are designed specifically for Excel, and they do not run outside of Excel; rather, they increase what Excel can do. Some of the ways that add-ins extend Excel is by including these items:- Web-based task panes and content panes: Display and allow you to interact with data from another application that’s related to data in your Excel spreadsheet
- Custom menu items and ribbon buttons: Provide you with more features and functionality
- Dialog windows: Allow you to provide additional information or verify processes before running them
- Start Microsoft Excel.
- On the Insert tab, click the Store button in the Add-ins group. Alternatively, if you’re running the Excel Online version, click the Office Add-ins button and then the Store button in the Office Add-ins window that appears.
The Office Store opens.
Check out the menu navigation method to the Add-ins button in the online version. versus the full Excel versions.
Store button used to find the Excel add-in.Check out the menu navigation method to the Add-ins button in the full Excel version.
Office Add-ins button found in Excel Online. - In the Store's Search text box, type Dynamics and press Enter.
- From the results, click the Microsoft Dynamics Office Add-in link.
- Click the Add button.
- In the dialog box that appears, select the Trust This Add-in option.
You need to specify that you trust the add-in only the first time you run it, not every time.
- Enter the URL of your Finance and Operations tenant instance.
You can copy-and-paste it into the Server URL field.
Delete everything after the hostname.
The resulting URL should have only the hostname, as in https://xxx.dynamics.com, where xxx stands for your hostname.
- Select OK and then select Yes to confirm the change.
The add-in restarts.
Metadata (the lists of tables and available fields that you can edit in Excel) is loaded.
You can switch from Excel Online to the full (installed) version of Excel (assuming that you have it installed) by clicking the Edit In Excel button in Excel Online on the top menu bar.
Adding, deleting, and updating entity data (such as vendors, customers, invoices, and sales orders) is normally done within the screens of the ERP system itself, not in Excel. The add-in, however, does let you create, update, or delete ERP entity data records from Excel, though this isn’t a simple matter to set up for a whole host of reasons, including these:- You cannot add records without ensuring that you have included all required fields.
- You need to specify the field that is the primary key (unique ID number) for that entity. For example, the customer ID may be the unique primary key of the customer master table, but when dealing with invoices or purchase orders or other, more complex tables, the primary key may be a composite key made up of multiple fields.
- You may receive errors if you try to delete a record that’s used in other related tables. This is called a foreign key constraint. For example, you cannot delete a salesperson if that salesperson is used on billing invoices that are part of your billing history.
Seek the assistance of your IT department or a qualified Dynamics 365 partner consulting firm when designing integrations that change the data in your Dynamics 365 ERP system from Excel. This functionality works great, but an expert must configure it properly. At the very least, start with read-only views of Excel data in Dynamics 365 for Finance and Operations.
If you do not have any experts to help you, you may wish to start with creating Excel integrations that are merely read-only views of Dynamics 365 ERP data displayed in Excel, with no update, insert or delete commands; these are safer because they cannot create any data corruption in the ERP system.After you, or someone from your IT department or your authorized solution provider, has set up a data connector using the Designer that is part of the Microsoft Dynamics Office Add-in, the data connector is displayed in the pane to the right of your spreadsheet. You can publish (save, in other words) updates to your data back to your Microsoft Dynamics systems. These updates are handled by a feature in Microsoft Dynamics called OData (open data protocol) services. Only touch points available in OData services are now supported in the Dynamics Office Add-in. If you receive an error when you publish your changes, the row that had an error is highlighted. Also, a message indicating the number of rows that were deleted, added, or updated is displayed.