Edit in Excel and the Excel integrations: what a developer needs to know

"Can I get this in Excel" is the most frequent request on any list page, and the answer is yes four different ways. Each one touches your extension's pages, fields and APIs differently.

1. Open in Excel

Any list page, Share → Open in Excel: a static export of the visible columns and rows, respecting the current filter and the user's personalisation. Read-only. It works on your pages with no effort, and it exports exactly the columns the user sees, so a column you hid for layout reasons is not exported. Captions become headers.

2. Edit in Excel

Same menu, Edit in Excel: a workbook with the Business Central add-in, bound to the page's underlying data through a generated OData web service, with the ability to change rows and Publish them back. Behind the scenes the platform creates a web service for the page (visible in Web Services, with a name like Page50120_Excel) and the add-in reads and writes through it, running the page's validation. So:

• Your field validations run on publish; errors come back per row in the add-in.
• Fields that are not editable on the page are not editable in Excel; use that to control what can be mass-updated.
• FlowFields export but cannot be written.
• Permissions apply as in the client.
• If the page has no editable fields a user should be mass-updating (a posted list, a ledger), consider Editable = false on the page, which makes Edit in Excel read-only.
• Enum values export as captions; dates and decimals in the user's format.

Edit in Excel is the fastest way to update a thousand item prices or customer credit limits, and it is also the fastest way for someone to blank a column on a thousand records. The second needs a conversation about permissions, not a technical block; the audit trail is the SystemModifiedBy (system fields) and the change log.

3. Excel report layouts

Any report can have an Excel layout (version 20 onwards): the dataset lands on a data sheet as a table and the user builds pivots and charts on other sheets that refresh when the report is rerun. For management reporting this replaces most "export then rebuild the pivot" work. As a developer, design the dataset for it: flat, well-named columns, no totals in the dataset (report layouts post).

4. The Excel add-in on APIs and Power Query

Excel's Data → Get Data → From Online Services → Dynamics 365 Business Central connects to the API (and to OData web services you publish) through Power Query: refreshable, filterable, joinable to other sources, and the basis of most finance teams' home-grown reporting. Your custom API pages appear here. Give the finance team an API query for the things they rebuild monthly and they stop exporting ledgers.

Making your pages Excel-friendly

• List pages with the useful columns visible by default, captions that make sense as headers, and the key first.
• Views (page design) so the export is already filtered.
• Editable only where mass update is intended.
• Validation that gives a clear per-record error (error handling), because that is what the add-in shows.
• For large tables, an API page or query rather than Edit in Excel, which pages through OData and is slow past tens of thousands of rows.

Related posts