Using Excel to Mass Update 365 Business Central

Excel to Mass Update 365 Business Central is one of the fastest practical ways to update many Business Central records without opening each card one at a time. A common example is changing the Item Category Code for 100 items from the Item List, editing the values in Excel, and then publishing the changes back to Microsoft Dynamics 365 Business Central.

Why Use Excel to Mass Update 365 Business Central?

Business Central users often need to clean up or standardize records in bulk. Item categories, customer posting groups, blocked flags, descriptions, dimensions, tax group codes, and other editable list fields can become inconsistent over time. Opening each Item Card individually works for a few records, but it is slow and error-prone when the list grows.

Microsoft provides two Excel-related actions from Business Central list pages: Open in Excel and Edit in Excel. Open in Excel is useful for viewing and analysis, but it does not publish changes back to Business Central. For updates, use Edit in Excel, which opens list data in Excel, lets authorized users edit records, and publishes the changes back to Business Central.

Back to Table of Contents

Before You Start with Excel to Mass Update 365 Business Central

Before using Excel for a mass update, confirm that the data should be changed, that you are working in the correct company, and that you have permission to edit the records. The Edit in Excel action is controlled by Business Central permissions, and it is available on many list pages for users who have the right access.

Prerequisites for Excel to Mass Update 365 Business Central

• Use the Business Central web client and open the correct company.
• Confirm the list page supports Edit in Excel.
• Confirm your Business Central account can edit the field you plan to change.
• Use Excel with the Business Central Excel add-in available.
• Filter the list before exporting if only certain records should be updated.
• Consider testing the same process in a sandbox company before changing production data.

For the example below, the goal is to update Item Category Code for 100 items. The same basic approach can apply to other editable fields, but validation rules still apply. If a value is invalid in Business Central, the publish step may reject that row.

Back to Table of Contents

Step-by-Step: Excel to Mass Update 365 Business Central from a List Page

1. Open the Business Central list page.
   Use Search in Business Central and open the list you want to update, such as Items, Customers, or Vendors.
2. Filter the list to the records you want to change.
   Apply filters before using Excel. For example, filter the Item List to the vendor, item range, old category, blocked status, or other criteria that identify the 100 items you want to update.
3. Review the visible records.
   Make sure the filtered list contains only the records you intend to edit. This is the most important control step because the Excel workbook is based on the list data you send from Business Central.
4. Select the Share icon.
   On the Business Central list page, use the Share icon at the top of the page.
5. Select Edit in Excel.
   Choose Edit in Excel, not Open in Excel. Business Central creates an Excel workbook connected to the list data.
6. Open the workbook in Excel.
   If prompted, sign in with the same Microsoft account used for Business Central. The Business Central add-in pane should appear in Excel.
7. Find the column you need to update.
   For the item example, find the Item Category Code column. Freeze panes, sort, or filter in Excel if that makes the workbook easier to review.
8. Edit the values directly in Excel.
   Enter the new Item Category Code for each item that needs the change. You can copy and paste values down the column, but avoid changing unrelated records.
9. Do not change the workbook structure.
   Do not delete required columns, rename column headers, or leave temporary formula columns in the final workbook. If you insert a helper column for calculations, remove it before publishing.
10. Select Publish in the Business Central add-in pane.
    After reviewing the edits, use the Publish action in the Business Central Excel add-in pane. This sends the edited values back to Business Central.
11. Resolve validation errors if any rows fail.
    If a value is not valid, correct the row in Excel and publish again. Use Business Central validation messages to identify which value needs attention.
12. Refresh and verify in Business Central.
    Return to the Item List, refresh the page, and confirm the Item Category Code was changed for the intended records.

Back to Table of Contents

Example: Use Excel to Mass Update 365 Business Central to Update Item Category Code

Assume you have 100 items that should move from an old item category to a new category. Instead of opening 100 Item Cards, use the Item List and Edit in Excel.

Item Category Example for Excel to Mass Update 365 Business Central

1. Search for Items in Business Central.
2. Filter the Item List to the 100 items that need the new Item Category Code.
3. Review the filtered rows to confirm the list is correct.
4. Select the Share icon and choose Edit in Excel.
5. Open the workbook and sign in if prompted.
6. Find the Item Category Code column.
7. Enter the new category code in the first applicable row.
8. Copy the new category code down through the remaining applicable rows.
9. Review the Item No., Description, and Item Category Code columns before publishing.
10. Select Publish in the Business Central add-in pane.
11. Return to Business Central, refresh the Item List, and confirm the category values are updated.

This approach is especially useful when the business already knows the exact category code to assign. It is still important to confirm that the category code exists in Business Central before publishing. If the code does not exist or the user lacks permission to change the field, the publish process may fail for those rows.

Back to Table of Contents

Common Mistakes When Using Excel to Update Business Central

The safest way to Excel to Mass Update 365 Business Central is to treat Excel as a connected editing tool, not as an unrelated spreadsheet import. The workbook has a structure that Business Central expects when publishing changes back.

• Using Open in Excel instead of Edit in Excel: Open in Excel is for viewing and analysis. It will not publish changes back to Business Central.
• Updating too many records at once: Filter first and work in manageable batches.
• Changing column headers: The add-in needs the expected structure to map values back correctly.
• Leaving temporary columns in the workbook: Helper columns can be useful, but remove them before publishing if the workbook structure no longer matches the original data layout.
• Using invalid codes: Item Category Code and similar fields must use valid Business Central values.
• Skipping verification: Always refresh the Business Central list and confirm the results after publishing.

Back to Table of Contents

When Not to Use Excel to Mass Update 365 Business Central

Edit in Excel is excellent for controlled list-page updates, but it is not the right tool for every data change. Do not use it as a substitute for a properly designed import, integration, approval workflow, or data migration when the business process requires validation beyond a simple field update.

For recurring vendor files, complex transformations, multi-table updates, or changes that must be staged and approved, a dedicated import process or Business Central extension may be more appropriate. However, for a clean list update such as changing Item Category Code on a filtered set of items, Excel to Mass Update 365 Business Central can be a quick and practical option.

Back to Table of Contents

Related System Solutions Resources

Back to Table of Contents