Edit in Excel Is Not Working: What Business Central Is Actually Telling You

Edit in Excel almost never fails because of Excel. The action publishes the page you are standing on as a web service, and the add-in reads and writes through that endpoint. So when it breaks, what broke is one of three things: the metadata generated from your page, the add-in’s install path on the user’s machine, or a piece of AL that cannot run outside a client session.

Those three have different symptoms, different fixes, and no overlap. Telling them apart takes about two minutes if you check them in the right order, and most of a day if you don’t.

What actually happens when you select Edit in Excel?

Business Central has two Excel actions and they share almost nothing. Open in Excel asks the platform to produce a workbook containing the data, read-only. Edit in Excel loads the Business Central add-in for Excel, which reads the data through the page exposed as an OData web service endpoint and writes your changes back through that same endpoint.

If nobody intended to write data back, Open in Excel has none of the failure modes below, and a good number of tickets end there.

The add-in is called Microsoft Dynamics Office Add-in, is distributed through the Office Store, and appears on most list and list part pages behind the Share icon.

Here is the part that explains everything else. The generated web service is built from the card page, not from the list you were looking at. Microsoft’s own worked example: standing on the Customer List (page 22) and choosing Edit in Excel generates a web service that exposes the fields of the Customer Card (page 21). It includes every page field defined on that card, and table fields only when they correspond to a page field or belong to the primary key.

Which gives the sentence worth keeping: Edit in Excel binds to the shape of your page, not to your table. Every extension touching that card page changes the surface the add-in has to describe.

So work symptom-first: does the add-in load, does data arrive, does one edited row publish, does a multi-row edit publish. Each step fails for a different reason.

Why won’t the add-in load or connect at all?

Start with the client, the cheapest thing to rule out. Microsoft states plainly that the add-in works on Windows only. Opening the workbook in Excel in a browser is Microsoft’s documented test when a connection fails, but the desktop add-in itself is Windows-only. A user on a Mac reporting that Edit in Excel is broken is usually not describing a bug.

If the message is “Add-in Warning: This add-in is no longer available”, the add-in is deployed centrally but that user was never assigned access, or the user is blocked from installing add-ins locally.

Then the failure nobody connects to its cause. Every Excel file created through Edit in Excel is stamped with an add-in identifier. When an organisation switches between individual acquisition and Centralized Deployment — in either direction — a different identifier is assigned and the earlier one is blocked. Old workbooks still open, they just stop refreshing from Business Central and stop publishing to it. The symptom looks like file corruption; the cause is a deployment decision made weeks earlier by someone who never opened those workbooks.

Three more that produce “it doesn’t work” tickets with unrelated fixes:

• Centralized Deployment can take up to 24 hours to reach a user’s Excel. Nothing is wrong yet.
• With more than one environment, the Excel Add-in Centralized Deployment assisted setup must be run on each environment, even though the Microsoft 365 side is configured once.
• If the data never loads, check the company name. A company whose name contains special characters stops the add-in connecting.

On-premises adds two of its own. If the environment was never configured for the add-in, the Edit in Excel action isn’t available at all — nothing to troubleshoot on the client. And a sign-in that starts then halts without completing points at the July 2022 add-in update, which requires a Microsoft Entra configuration change — on-premises only, online tenants were never affected.

What does “Metadata was unable to be retrieved for entity” mean?

The full message is Metadata was unable to be retrieved for entity <entity name> as it was not found, and it means Business Central could not generate the web service metadata for that page, which has become too complex to process.

The primary documented cause is a name collision between extensions. Microsoft’s example uses two apps that each add a page field called ShoeSize to the Customer Card, backed by two different table fields. An extension cannot create a page field with the same name as one that already exists — but nothing stops two extensions colliding with each other, and neither vendor can see the other’s field.

A single extension can also block it. If a CardPart is embedded on the page and its SourceTable doesn’t match the host page’s source table, the metadata algorithm evaluates the part’s fields and stops. The fix is to set the matching SourceTable on the part. When a part genuinely has to be embedded on several pages with no single source table, Microsoft’s recommendation is a separate CardPart per host page with the shared logic refactored into a codeunit.

The only documented isolation method is disabling the extensions that touch the page one at a time, then reading the code of whichever one breaks it. That is why the error keeps coming back: every app you install is another chance at a collision on a page you never touched — the standing maintenance cost of Business Central customization, paid by a feature nobody thought was related.

Why do the edits refuse to publish back?

Data arrives, the user edits, publish fails. Four separate families sit behind that one symptom.

An extension blocks the publishing job. Microsoft’s documented test is to open the workbook in Excel in a browser, and if the page is extended or customized, remove the extensions and try again.

The add-in inherits the API’s limits. It communicates using API v2.0, so any limitation of that API applies here — the same OData layer a Business Central API integration rides on. AL that needs a client cannot run: a confirmation dialog used as validation logic stops the publish. Where that confirmation is negligible here, the documented workaround is to guard it, for example if Session.CurrentClientType() <> ClientType::ODataV4 then.

Two standard pages have named limitations. On Sales Orders, setting Alternate Shipping Address to Ship-to opens the Ship-to Address List as a modal, producing an error about a client callback attempted to run page 301 as modal — client callbacks are not supported. On the Project Journal, updating Unit Price updates Line Discount instead of Line Amount, because the cascade logic relies on xRec, which behaves differently through APIs.

Single rows publish, several rows fail. This one has a real fix rather than a workaround. It happens when the OData calls include FlowFields and fields outside the repeater control, which are then part of the eTag calculation. On the Web Services page, for that published page, tick Exclude Non-Editable FlowFields and Exclude Fields Outside of the Repeater. Both checkboxes are hidden by default and have to be surfaced through personalization, and both are set per published page — there is no global switch.

One more worth recognising. If Excel warns that filters applied on the page are not available in Office and lists them as removed, and the field came from an AL extension, the page field name and the underlying table field name have to be identical.

When should a report stop going through Edit in Excel at all?

A large share of what people use Edit in Excel for is not editing. It is getting a filtered list out of Business Central, often on a schedule, into a file somebody else consumes. For that job the whole failure surface above is a cost with no return, because it all traces to one root: the channel binds to your page shape, and that shape changes every time you install an app.

For that half we build a small export module instead. It binds to the table, not the page — a table number, a saved filter view, an explicit ordered column list, run on demand or from the job queue. Nothing in it depends on which extensions touch which page.

The price is why this is not a replacement: there is no write-back. Nothing returns to Business Central. If the user has to change the data, Edit in Excel is the answer and has no substitute. Only the read-only reporting half moves.

Writing it yourself also means taking on three things the standard action hides:

1. Not every field can be a cell. Our column picker restricts the field list to enabled, non-obsolete fields of class Normal or FlowField, and excludes BLOB, Media, MediaSet, TableFilter, Binary, OemCode and OemText outright. Offer a user a field of one of those types and the export writes junk or fails.
2. FlowFields do not calculate themselves. Read one through a RecordRef without calling CalcField first and you export a column of zeros that looks exactly like real data. It is the same FlowField Microsoft has you exclude from the eTag, for the opposite reason: there it is too much, here not enough.
3. Cell type is a decision, not a default. Decimal, Integer and BigInteger go in as Number, Date and DateTime as Date, Time as Time, everything else as Text. Skip that mapping and the recipient gets numbers stored as text, which is the real origin of most “the totals don’t add up in Excel” complaints.

And one that only appears in production: the same code cannot deliver the file the same way in both contexts. Run interactively, it streams to a browser download. Run under the job queue, there is no client session at all, so it has to write to a configured destination. That branch is the difference between a scheduled export and a nightly job that succeeds every time and delivers nothing.

How we approach it

Most Edit in Excel tickets handed to us are not Excel tickets. They are a card page that accumulated extension fields until the metadata stopped generating, or a deployment change nobody connected to the symptom. The order matters more than the knowledge: establish whether the channel is broken or the data is, before touching an extension. When the page really is the problem, the fix belongs in Business Central development work, not in a workaround the next app will break again. If you would rather go through it with someone, ask us for a clarity assessment.

FAQ

Does Edit in Excel work on a Mac? The Microsoft Dynamics Office Add-in works on Windows only. When a connection fails, Microsoft’s documented test is to open the workbook in Excel in a browser, but the desktop add-in itself is not available on macOS.

Why did my existing Excel files stop refreshing after nothing changed? Most likely your organisation switched between individual acquisition of the add-in and Centralized Deployment. Each file carries an add-in identifier, and the transition assigns a new one, blocking the old. The files still open, they just can’t refresh or publish.

Why does the metadata error come back after we fixed it? Because the cause is usually two extensions adding page fields with the same name to the same card page, and installing another app is another chance at a collision. Fixing one collision does not immunise the page.

Can I edit several rows at once reliably? Yes, once the published page excludes non-editable FlowFields and fields outside the repeater from the eTag calculation. Both are checkboxes on the Web Services page, hidden by default, set per published page.