How-ToTips & Tricks

How to Use Edit in Excel in Business Central

Edit Business Central records in Excel and publish changes back to the system using the Edit in Excel add-in.

Stein Sæstad
7 min read

Edit in Excel lets you open Business Central list data in Excel, make changes to multiple records at once, and publish those changes back to Business Central, all from the Excel workbook. It works through an OData connection managed by the Business Central Excel add-in.

This is different from the standard Send to Excel export, which produces a static file with no connection back to BC.

Common use cases include bulk price updates on items, mass edits to customer or vendor fields, and updating item descriptions across a large catalogue.

Prerequisites

  • The Business Central Excel Add-in must be installed in Excel. In cloud environments, it is typically deployed automatically via Microsoft 365 admin. If the add-in pane does not appear, ask your system administrator or install it from the Office Add-ins store.
  • Your Business Central user account must have edit permissions on the records you want to change.

Opening Edit in Excel from a List Page

  1. Open the list you want to edit, for example, the Item List or Customer List.
  2. Apply any filters to limit the data to the records you need. Edit in Excel respects the current filter, so set it before opening.
  3. Select the Share icon in the top-right corner.
  4. Choose Edit in Microsoft Excel.
  5. Business Central downloads an .xlsx file. Open it in Excel.

When the file opens, the Business Central add-in pane appears on the right side of the Excel window. If it does not appear automatically, go to Insert → Add-ins and open it from there.

Making Changes in the Workbook

The data loads into the Excel sheet with one row per record. Each column corresponds to a field in Business Central.

Edit cells directly in the sheet as you would any Excel file. You can:

  • Change values in individual cells
  • Use Excel formulas to calculate new values before pasting them in as text
  • Sort and filter the sheet to find specific records

Do not add or delete columns. The add-in uses the column structure to map data back to the correct fields in BC. Changing column headers or reordering columns will cause errors on publish.

Publishing Changes Back to Business Central

  1. After making your edits, go to the Business Central add-in pane.
  2. Select Publish to send the changes back to Business Central.
  3. The add-in validates each row before writing it. Rows with validation errors are highlighted.

If a row fails validation, the error message appears next to that row in the sheet. Fix the value and publish again. Successfully published rows are written to Business Central immediately, you do not need to publish the entire sheet again, only the rows with errors.

Filtering Data in the Add-in

The add-in pane includes a Filter section that lets you change which records are loaded without closing and reopening the file.

  1. In the add-in pane, select Filter.
  2. Adjust the filter criteria, for example, change the item category or posting group filter.
  3. Select Get Data to reload the sheet with the new filter applied.

This is useful when you need to work through a large dataset in sections rather than loading everything at once.

What Can and Cannot Be Edited

Edit in Excel works on editable master data and setup records. There are important restrictions:

  • Posted entries cannot be changed. General Ledger Entries, Customer Ledger Entries, and other posted records are read-only. The edit option will not be available on those pages, or the rows will not publish if you attempt to modify them.
  • Calculated fields (such as balance amounts or quantity on hand) are displayed for reference but cannot be written back.
  • Primary key fields (such as item number or customer number) can usually not be changed via Edit in Excel, changing a primary key is a structural change that should be done carefully within BC.

If a field is not editable in the Business Central page, it will also not be editable through the add-in.

Notes on the Add-in Connection

The add-in connects using your Microsoft account credentials. If your session expires while the file is open, the add-in will prompt you to sign in again before publishing.

The connection goes through the same OData API used by the Business Central REST API. Any data the add-in reads or writes is subject to your BC user permissions.

For a comparison of Edit in Excel and the standard Send to Excel export, see How to Export Data to Excel in Business Central.