How to Use Excel to Bulk Update Records in Dynamics 365 Finance & Operations

Using Excel with Dynamics 365 Finance & Operations is incredibly helpful for making bulk updates to transactions with numerous lines. The functionality of Excel offers you the convenience of easily copying rows into new records or reviewing data for accuracy. You will save time when making simple changes to many rows at once, eliminating the need to manually go line by line in F&O. Additionally, you can easily create a new record copied from another record.

Any standard user can access the Excel Data Connector Add-In to easily leverage these capabilities in F&O; it does not require a System Administrator to set up.

Note: Even though most customers, commentators, and consultants still call it Dynamics 365 Finance & Operations or F&O, Microsoft now technically licenses it as Dynamics 365 Finance and Supply Chain Management.

Table of Contents

• Open Excel from Finance & Operations
• Add Rows, Undo Changes, and Publish Data in Excel
• Edit Finance & Operations Fields with Excel
• Resolve Errors When Publishing

Open Excel from Finance & Operations

You can easily access the Excel Data Connector in F&O directly from any transaction or record you wish to make changes to. For example, if a Procurement Specialist finds a purchase order with incorrect pricing that needs to be updated, you can access the Excel template connector option by clicking the Office Icon in the upper right hand corner. Be sure to select the entity you wish to make changes to. In the example below the entity is ‘Purchase Order Lines V2’.

Note: Make sure to select the entity under ‘Open In Excel,’ not ‘Export to Excel’. Selecting ‘Open In Excel’ maintains the link to F&O, allowing the user to make changes that sync back to F&O. Whereas ‘Export to Excel’ simply dumps the data to a static spreadsheet with no link to F&O.

Once selected, a pop up will appear. Select ‘Download’ to launch Excel.

Once you open the downloaded file, notice that the spreadsheet is empty. There will be a yellow warning bar at the top. Click ‘Enable Editing’ to launch the spreadsheet with the connector tool.

You may be prompted at this point to sign in; simply use your Office credentials to do so. Once you have successfully logged in, you should see the transaction data for the entity selected, in our example – purchase order lines.

The data will match exactly what is in F&O.

Add Rows, Undo Changes, and Publish Data in Excel

The Excel Connector Tool allows you to use all the tools available in Excel to modify and edit records and push them back into F&O.

In the lower right hand corner, there is a menu with options available in this tool that allows you to manage what and how the data will be pushed back into F&O.

• New – This button will create a new row in the record. New rows will automatically be added to the bottom of the list. This can also be achieved by using standard Excel functionality, such as right click > insert.
• Refresh – There is no need to undo all changes manually. If when making changes, you make a mistake and want to start fresh with the original transaction from F&O, the Refresh button will clear all unpublished changes in your workbook, returning the transaction to its F&O state.
• Publish – Publish the data changes in the workbook back to F&O. If there are any data validation errors, you will see them at this point.

Edit Finance & Operations Fields with Excel

The right hand menu will provide much of the information a user would need to update fields. It will tell you which fields are read-only, and which can be edited.

For example, in purchase order lines, procurement categories are a read-only field in the Excel Connector Tool and cannot be updated here:

The spreadsheet data format must align with the format expected by F&O. For instance, the date format must match (like YYYY-MM-DD) to sync successfully.

It’s recommended to manually create a single line in your transaction in F&O before opening the Excel Connector tool. This way, you can see how F&O expects to receive the data, making it easier to copy, rather than guessing the format of a new transaction.

Similarly, when a field has a lookup or enumeration drop down list associated with that cell, the value options will be displayed in the add-in menu. For example, when you select the date field, a calendar will appear in the add-in menu. This helps to avoid selecting incorrect values in fields.

There are limitations to what changes can be made. For example, if a field in F&O has a set list of values, you cannot type a value that doesn’t exist in Excel and have it sync make to F&O. This would result in a failed update and trigger an error message stating that.

Resolve Errors When Publishing

All rows without error will be published back into F&O, but rows showing errors will not be published. Partially publishing is possible, so be sure when you are ready to click publish.

Below is an example of this error. Notice the message says, ‘1 error was encountered and 1 change was published’.

The tool will clearly identify which row has an error by highlighting it in red, as shown above. If you click the ‘Open Message Center’ hyperlink, you will be shown which field(s) specifically has the issue. Here is the example message detail from above:

Messages

• 11:00:02 AM 11/7/2023
  Error Publishing Details: 1 error(s) were encountered while publishing. 1 change(s) were successfully published.
• Error Publishing Row Details: A row update in data set PurchaseOrderLinesV2 was not published. Error message: ‘Write validation failed for table row of type ‘PurchPurchaseOrderLineV2Entity’. Infolog: Warning: Warehouse 13 is not connected to site A.’

Installing the Add-In (If You Don’t Already Have It)

If you are given a work laptop it is likely that this add-in is already available for you. If not, it’s free and simple to install from Microsoft App Source.

Video

Watch this recorded webinar for a complete walkthrough on how to use the Excel Data Connector in Dynamics 365 Finance & Operations:

Conclusion

In general, this tool is very handy and can save users tremendous amounts of time by eliminating the need to manually make changes to records. Anyone who is more comfortable in Excel will be able to effectively leverage its tool to better manage transactions and records in F&O.

There are more complex capabilities than can be achieved with filtering and template design, but this brief overview was intended to show how simple the Excel Connector Tool is and that anyone working inside of F&O has access.

For information on how to leverage the Excel Connector Tool and more advanced capabilities in F&O for your business, please contact us.