Edit in Excel for Dynamics 365 Business Central

Edit in Excel in Dynamics 365 Business Central helps accountants and others make bulk updates to records like credit limits or salesperson assignments quickly and accurately. Instead of editing each record manually, you can export data to Excel, make changes in a familiar spreadsheet format, and publish those updates directly back into Business Central.

In this article, you’ll learn how to use Edit in Excel effectively, including step-by-step instructions and best practices to avoid common errors and ensure your updates are successful.

What Is “Edit in Excel”?

Edit in Excel is a built-in feature within D365 Business Central that allows users to:

• Export list data from Business Central to Excel.
• Make changes directly in Excel using the familiar spreadsheet interface.
• Publish those changes back into Business Central with a single click.

This functionality is especially useful for mass updates, like adjusting credit limits or assigning salespeople, without navigating through individual records.

How to Use Edit in Excel

Step 1: Export Your Data

Navigate to the Customer List page.

Personalize the view to include the fields you want to update, such as Salesperson Code and Credit Limit.

Click the Share icon in the top-right corner and select Edit in Excel.

An Excel file will be downloaded to your device.

Tip: Make sure the columns you need are visible before exporting. If they’re not, use the “Personalize” option to add them in Business Central.

Step 2: Edit in Excel

Open the downloaded Excel file and click Enable Editing. A blank Excel copy will appear; you will need to log in to your Microsoft account to view the data connection from D365 Business Central.

In the right-hand pane labeled “Microsoft Dynamics”, sign in with your Microsoft 365 credentials to activate the Data Connector add-in.

Once signed in, your Business Central data will populate in the spreadsheet.

While updating your records, remember to enter the correct information type – in this case, numbers for Credit Limits, and the correct code for Salesperson.

Important: Do not rearrange the layout of the file. Avoid moving, hiding, deleting, or renaming columns. Also, do not delete any rows, even if they appear blank.

Step 3: Publish Your Changes

Once you’ve made your desired changes:

1. Scroll to the bottom of the Data Connector pane.
2. Click Publish.
3. If successful, you’ll see the message: “Publish successful – workbook updated.”

After publishing, you can close your Excel file (you don’t need to save your file as the changes have already been imported back into Business Central).

Back in Business Central, refresh your page to see the changes.

If You Get “Didn’t Publish” Errors

You may see an “Error Publishing” message containing details, “X errors were encountered while publishing. 0 change(s) were successfully published” when you try to publish back from Excel to Business Central.

This typically means that some of the data in the Excel sheet is either invalid or missing required values. For example, if you enter text instead of a number in the Credit Limit field or use an unrecognized Salesperson Code, the publish will fail.

The system will flag these issues by highlighting the incorrect rows in red and displaying an error message indicating how many errors were encountered. No changes will be published to Business Central until all errors are resolved.

A good approach to solving this is to review the error messages in the Data Connector pane, correct the highlighted rows, and then try publishing again.

Keep the Layout Intact When Using Edit in Excel

When you use Edit in Excel in Business Central, the exported file reflects the system’s underlying table structure, not your personalized view. It may include more columns and a different order than what you see on screen. To avoid errors when publishing changes back, do not move, delete, or rename any columns. Just update the cell values as needed.

Think of the Excel sheet as a structured form – your job is to fill in the blanks, not redesign it. The data you enter is validated before it’s accepted into Business Central, so keeping the original layout ensures everything works smoothly.

If the Edit in Excel Button Is Missing

If you don’t have the Edit in Excel button at all, that is probably a user permission issue. To check and resolve this, you should ensure that your user role includes the EXCEL EXPORT ACTION permission set.

Note that the Edit in Excel button isn’t available in every window. For instance, this feature is most commonly available for master data like customer or vendor lists, not posted transactions.

Please contact us if you have any questions about leveraging Excel with Dynamics 365 Business Central.