Open in Excel vs Edit in Excel for retail data in Business Central

Data & integrations · 8 min read

Both actions sit behind the same Share icon on a list page, both respect the filters on that page, and both put Business Central rows into an Excel workbook. Only one of them can send changes back. Choosing the wrong one wastes work in one direction — edits that can never publish — and creates risk in the other: bulk changes published to live retail data without the checks that direction demands.

Data-accuracy note: Edit in Excel publishes to live Business Central data. Filter first, work on the smallest set that proves the change, and verify the result in Business Central after publishing — not just in the workbook.

The one difference that decides everything

Microsoft Learn states the distinction directly: Business Central includes an add-in for Excel that lets users select the Edit in Excel action to open data in an Excel worksheet, and that action is different from Open in Excel because it lets users make changes in Excel and then publish the changes back to Business Central. Open in Excel has no publish-back path: changes made in the workbook can only be saved to the Excel file on the computer. If the change must exist in Business Central afterwards, Open in Excel was never going to get it there — the change would have to be entered manually in Business Central, or done again through Edit in Excel where that action is available.

Both actions start from pages that display a list of records in rows and columns, such as a list of customers, sales orders, or invoices. That shared starting point is why the two are so often confused: the export looks similar, while the connection behind the workbook is completely different.

Side-by-side: Open in Excel vs Edit in Excel

One platform note sits behind that table: for Business Central on-premises, the Open in Excel action is not available if the Edit in Excel action is used. And Edit in Excel depends on an add-in that works on Windows only and is not available in the mobile apps — a real constraint for a store team working from mixed devices, and a reason the choice is sometimes made by the device, not by preference.

A decision framework for retail data work

Work through four questions, in this order. First: must the change exist in Business Central afterwards? If not — a one-off analysis, a filtered list for a meeting, numbers to share outside the system — Open in Excel is the right tool, and its inability to publish is a safety feature. Second: if the change must go back, is the list one where Edit in Excel is available? The action is available on most lists, but not all, so absence on a page is a documented possibility, not a fault to work around. Third: is the add-in in place? Edit in Excel requires the Business Central add-in installed in Excel; without it, the publish route does not exist yet. Fourth: is this data safe to bulk-edit at all? If the answer to that last question is no, neither action is the editing tool — use Open in Excel for a read-only look, and make the change through the normal Business Central pages where the full behaviour of the record applies.

Use Edit in Excel safely: the full procedure

1. Filter the list page first. Apply the filters that limit the records to the exact set to be changed. Edit in Excel respects most filters on the page, so the workbook holds almost the same records and columns — a narrow filter is the first guardrail.
2. Choose Edit in Excel from the Share icon. With the add-in installed, the action is available on most list and list part pages from the Share icon.
3. Handle first-time setup if it appears. If the organization has configured OneDrive for system features, the workbook opens in the browser using Excel for the web; otherwise it is downloaded to the device. On first use, either sign in to Business Central in the Excel Add-in pane, or — where the add-in is not pre-installed — choose Trust this add-in in the New Office Add-in pane, which installs the add-in directly from the Office Store.
4. Refresh before editing. Choose Refresh in the Excel Add-in pane to get the latest data from Business Central, so edits start from current records rather than the moment the file was first opened.
5. Edit only the intended fields, on the intended rows. Keep the change to the filtered set and the columns the task actually requires.
6. Publish, then verify in Business Central. Publish the changes back from Excel, then reopen the list in Business Central and confirm the records show the new values. The workbook saying the edit exists is not the check — the published record is.

What success looks like: after publishing, the filtered list in Business Central shows exactly the intended new values, on exactly the filtered records, and no records outside that filter have changed.

What happens to data on the way back

Edit in Excel does not write a file back into Business Central. The community documentation describes the mechanism: the workbook uses the Microsoft Dynamics Office Add-in to connect directly to the data through an OData web service, which is what allows both refreshing data from Excel and publishing changes back. Two documented protections travel with that connection. The add-in uses the user's Business Central authentication, so a user only gets access to view and work with data that their Business Central permissions allow — Excel does not widen anyone's access. And data validations ensure that users only publish data that follows the business rules in the application. A value that would be rejected in Business Central does not become acceptable because it arrived from a spreadsheet.

Administrators should also know the deployment trap. The add-in can be deployed by individual acquisition, where users install it themselves, or by Centralised Deployment in the Microsoft 365 admin centre — and Centralised Deployment can take up to 24 hours to deploy automatically in Excel for users. Switching between those two modes changes the add-in identifier assigned to Excel files: files created before the switch can still be opened, but they cannot be updated from Business Central or publish updates to it. A team that suddenly cannot publish from existing workbooks should check whether the deployment mode changed before assuming the data or the users are at fault. For a manual connection, the documented Business Central online Server URL for the add-in is the exceladdinprovider address published in Microsoft Learn's add-in guide at Getting the Business Central add-in for Excel.

Guardrails: what never to bulk-edit

• Never bulk-edit around a modal or callback behaviour. Microsoft documents a concrete case: on Sales Orders, setting Alternate Shipping Address to Ship-to opens the Ship-to Address List modal dialogue, which is not compatible with Edit in Excel because client callbacks are not supported on Business Central Data Services. Fields whose behaviour depends on that kind of dialogue belong in the Business Central page, not a bulk edit.
• Never assume a price edit cascades the way it does on screen. On the Project Journal, Microsoft documents that updating Unit Price in Excel does not trigger an update of Line Amount and instead updates Line Discount, because the cascade logic relies on behaviour that differs when called via APIs. Any field whose neighbours normally recalculate should be verified field-by-field after a first small publish — or edited in the client.
• Never bulk-edit a page that errors on multi-line edits without fixing the documented cause first. For some list pages, editing multiple lines in Excel consistently causes errors when OData calls include FlowFields and fields outside the repeater. The documented fix is on the Web Services page: the Exclude Non-Editable FlowFields and Exclude Fields Outside of the Repeater checkboxes, which exclude those fields from the eTag calculation. Repeated publish errors are a setup signal, not a reason to retry the same bulk edit harder.
• Never treat a filtered workbook as permission to widen the change. The filter defined the job. Adding unrelated rows or columns in Excel expands a reviewed change into an unreviewed one.
• Never bulk-edit from a stale workbook. Refresh in the add-in pane exists for this reason: publishing over records that changed in Business Central since the file was opened turns a tidy edit into an overwrite.

When neither action is the right tool

On-premises adds one more documented condition: the environment must be configured for the add-in, or the Edit in Excel action is not available to users at all. Where that configuration is missing, the honest options are the normal Business Central pages for the edit, or Open in Excel for analysis — not a workaround that pretends a publish path exists. The same honesty applies to any list where Edit in Excel is simply not offered: most lists have it, not all, and that gap is part of the product's documented shape.

FAQ

Does Open in Excel change Business Central if the file is saved? No. Changes can only be saved to the Excel file on the computer; nothing publishes back.

Why does an old Edit in Excel workbook no longer publish? A documented cause is a switch between individual acquisition and Centralised Deployment, which changes the add-in identifier: older files still open but cannot update or publish.

Does Edit in Excel bypass Business Central permissions or validation? No — the add-in uses the user's Business Central authentication and permissions, and validations ensure published data follows the application's business rules.