Get the Business Central add-in for Excel

Business Central includes an add-in for Excel that lets users select a Edit in Excel action on certain pages to open the data in an Excel worksheet. This action is different than the Open in Excel action because it lets users make changes in Excel, then publish the changes back to Business Central

Overview

About the add-in

The add-in is called Microsoft Dynamics Office Add-in and it's available for installation from on the Office Store (AppSource). With the add-in installed, the Edit in Excel action is available on most list and list part pages from the Share icon Share a page in another app.. For more information about using the add-in, see Viewing and Editing in Excel From Business Central.

Note

Note:
The add-in works on Windows only; not macOS.

About deployment as an admin

With Business Central online, there are a few deployment options for getting the add-in to users. One option is individual acquisition, where you let users install the add-in themselves. With this option, users must have access to downloading files from the Office Store. Another option is to set up Centralized Deployment in the Microsoft 365 admin center to automatically deploy the add-in to your entire organization, groups, or specific users. Centralized Deployment provides a way to get the add-in to users if your organization doesn't give users access to the Office Store.

For the end-user, the installation experience is different for the two deployment scenarios:

  • With individual acquisition, the first time users choose the Edit in Excel action, the New Office Add-in pane opens in Excel. To install the add-in, the user chooses Trust this add-in, which in turn installs the add-in directly from the Office Store. Users then sign in to Business Central using their user name and password.

  • With Centralized Deployment, the first time users choose the Edit in Excel action, the add-in is automatically installed in Excel from Centralized Deployment; not the Office Store. The only thing users have to do is sign in to Business Central

With both these deployment options, the add-in is automatically configured to connect to Business Central. A third deployment option is a manual installation of the add-in directly from Excel. With this option, users will need to configure the add-in to connect to Business Central

Switching from individual acquisition to Centralized Deployment or the other way around

When you change from individual acquisition of the add-in to Centralized Deployment, or vice versa, Excel files that users created before the transition are affected. After the transition, users can still open any Excel worksheets previously created using the Edit in Excel action or created manually by configuring the Excel add-in. But they can't update the data in the file from Business Central or push updates to Business Central

This condition is caused by the fact that each Excel file gets assigned an "add-in" identifier. In the transition to or from Centralized Deployment, a different ID is assigned, so the earlier ID becomes blocked.

Preparation (on-premises only)

Business Central on-premises requires that your environment is configured for the add-in. If not, the Edit in Excel action won't be available to users. For more information, see Setting up the Excel Add-In for Editing Business Central Data in the Developer and IT Pro help.

Deploy the add-in by using Centralized Deployment

Centralized Deployment is a feature in Microsoft 365 admin center that you use to automatically install add-ins in users' Office apps, like Excel. To help you with Centralized Deployment, Business Central includes the Excel Add-in Centralized Deployment assisted setup.

Before you begin

Note

Note:
Enabling Centralized Deployment affects features that use the Excel add-in, such as the Edit in Excel action. It has no effect on other Excel-related features and or permissions assigned to users in Business Central

Set up Centralized Deployment of the add-in

You'll work in both Business Central and the Microsoft 365 admin center.

  1. In Business Central, choose the Lightbulb that opens the Tell Me feature. icon, enter Excel Add-in Centralized Deployment, then choose the related link.

  2. Read the information on the Business Central Excel add-in setup page and choose Next.

  3. Sign in to the Microsoft 365 admin center and go to Integrated Apps.

    Complete the following steps to configure the add-in to deploy from the Office Store:

    1. Choose Get apps to open Office Store (AppSource).

    2. Search for Microsoft Dynamics Office Add-in, then select Get it now.

    3. On the Add Users page, specify the users that you want to deploy the add-in for, then choose Next.

    4. Review the Accept permissions requests, then choose Next > Finish Deployment.

    5. Wait for the green check mark next to Deployed appears for the add-in, then choose Done.

      The add-in appears on the Add-ins page. For more information about deploying add-ins in the Microsoft 365 admin center, see Deploy add-ins in the admin center.

  4. Go back to Excel Add-in Centralized Deployment assisted setup in Business Central, and choose Next.

  5. Turn on Use Centralized Deployment, and choose Finish.

    If you don't turn on this switch, Business Central will get the add-in directly from the Office Store.

When finished, you can always change the deployment in Microsoft 365 admin center, like assigning more users. For more information about deploying add-ins in the admin center, see Deploy add-ins in the admin center.

Important

Important:
If you have more than one environment, you must run the Excel Add-in Centralized Deployment assisted setup on each environment that you want to use Centralized Deployment. However, you don't have to configure the Centralized Deployment in Microsoft 365 again. The only thing you have to do is turn on the Use Centralized Deployment switch in the assisted setup.

Note

Note:
It can take up to 24 hours before users the add-in deploys automatically in Excel of users.

Individual acquisition: Install the add-in manually for your own use

In most cases, when you open Excel from Business Central, the add-in will either be installed automatically for you or you'll be prompted to install it. There might be cases, however, where you have to manually install the add-in.

  1. Open Excel, then open any Excel workbook.
  2. On the Home tab, select Add-ins > More Add-ins.
  3. Go to Admin managed and look for Microsoft Dynamics Office Add-In. If you see there, select it, then choose Add. If you don't see it, go to Store, then search for Microsoft Dynamics Office Add-In and follow the instruction on screen to add it.

When the add-in is installed, it shows up as a panel in Excel. Next, configure the connection.

Configure the Business Central connection

If a user can't connect automatically, you can unblock them by asking them to follow these steps:

  1. In the Microsoft Dynamics add-in pane in Excel, choose Add server information. If you don't see it, choose the More option button in Excel. icon at the top to open the options dialog.
  2. For Business Central online, set Server URL to https://exceladdinprovider.smb.dynamics.com. For Business Central on-premises, set it the URL of the web client, like https://myBCserver/240.
  3. Choose OK, and then confirm that the app reloads.
  4. When prompted, sign in with your Business Central user name and password.
  5. Optionally, choose the environment and company that you want to connect to.

The add-in is now connected to Business Central, and you can edit data and publish the changes to Business Central.

Prepare devices and network for the Excel Add-In

Network services such as proxies or firewalls must allow routing between each client device on which the add-in is installed and many service endpoints. For a list of endpoints, see Preparing your network for the Excel Add-In.

Troubleshooting

Sometimes, users run into problems with the Excel add-in. This section gives some tips for how to unblock users in certain circumstances.

Problem Solution or workaround Comments
The add-in doesn't start

For example, the user gets the message "Add-in Warning: This add-in is no longer available." when trying to use the add-in. This particular problem can happen if centralized deployment is configured correctly, but the user wasn't assigned access.		 Check whether the add-in is deployed centrally. Or, check whether the user is blocked from installing it locally. The admin can configure Office so that users can't acquire add-ins. In those cases, the admin must deploy the add-in centrally. For more information, see Deploy add-ins in the admin center.
Data doesn't load into Excel Test the connection by opening another list in Excel from Business Central. Or, open the workbook in Excel in a browser. If the user has specified a company name that contains special characters, the add-in can't connect.
Data can't publish back to Business Central. Test the connection by opening the workbook in Excel in a browser. Sometimes an extension can block the publishing job. If the page is extended or customized, remove the extensions, and then try again.
The dates are wrong Excel might show times and dates in a different format than Business Central. This condition doesn't make them wrong, and the data in Business Central won't get messed up.
For some list pages, editing multiple lines in Excel consistently causes errors. This condition can occur if OData calls include FlowFields and fields outside of the repeater control. On the Web Services page, select the Exclude Non-Editable FlowFields and Exclude Fields Outside of the Repeater check boxes for the published page. Selecting these check boxes excludes non-editable FlowFields and field from the eTag calculation. These check boxes are hidden by default. To show them on the Web Services page, use personalization.
Users can no longer sign in to the add-in. When they try to sign in, the process stops without completing. This problem might be caused by an update that we made to the add-in, sometime in July 2022. For more information and a fix, see Modify the Excel Add-in Configuration to Support July 2022 Update. Applies to Business Central on-premises only
The add-in communicates using the API v2.0 for Dynamics 365 Business Central, and any limitations of this API are automatically inherited. An example limitation is if you try to edit a list and the underlying card uses a confirmation dialog in its AL logic, for example, as its validation logic. Sometimes there's nothing to do because it's a design choice that the user must explicitly confirm the change. If the confirmation is negligible when using Edit in Excel, then you can wrap the confirmation dialog call in an if-conditional statement that checks whether the client type is different from ODataV4, for example, if SESSION.CurrentClientType() <> ClientType::ODataV4 then. There might be other clients that you want to remove the confirmation dialog from, such as OData and SOAP.

Known limitations in business logic

Page Limitation Comments
Sales Orders Error message: 'Microsoft Dynamics 365 Business Central Data Services attempted to issue a client callback to run page 301 Ship-to Address List as modal'. Client callbacks aren't supported on Microsoft Dynamics 365 Business Central Data Services. The Ship-to Code on the Sales Order page is editable only with specific Ship-to options. Setting Alternate Shipping Address to Ship-to opens the Ship-to Address List modal dialog, which isn't compatible with Edit in Excel.
Project Journal Update of the Unit Price field doesn't trigger an update of the Line Amount. Instead, it updates Line Discount. Using the web client, you can update fields in any order&;price, amount, line discount. Other fields are updated automatically. To avoid cascade updates, the fields have some advanced logic that relies on xRec, which behaves differently when called via APIs.

See also

Analyzing Financial Statements in Microsoft Excel
Work with Business Central
Enhancements to Excel integration in 2019 release wave 2

Find free e-learning modules for Business Central here