The Excel Connector allows users to edit Business Central data directly in Excel and publish those changes back to the system. It provides functionality similar to Configuration Packages but requires less setup and is designed for more frequent, day-to-day use. Benefits of the Excel Connector include:

• Faster data entry and maintenance
• Simplified mass updates
• Reduced navigation between pages in Business Central
• Direct integration between Excel and Business Central

Required permissions

Before using Edit in Excel, users must have the appropriate permissions assigned on their user card. To verify access:

1. Open the user record in Business Central.
2. Review the assigned permission sets.
3. Confirm the user has access to the Edit in Excel functionality for the appropriate company.

Users will also need:

• A Microsoft 365 license
• A Business Central license
• The Microsoft Dynamics Office add-in for Excel

Opening data in Excel

Many Business Central pages include an Edit in Excel action. To begin:

1. Open the desired page or journal.
2. Select Edit in Excel.
3. Open the downloaded workbook.
4. Enable editing if prompted.
5. Sign in to the Microsoft Dynamics Office Add-in.

The workbook creates a live connection to the selected Business Central data source.

Installing the Microsoft Dynamics Office add-in

If the add-in is not already installed:

1. Open Excel.
2. Enable the Developer tab if necessary.
3. Select Add-ins from the Developer tab.
4. Open the Office Store.
5. Search for Dynamics.
6. Install the Microsoft Dynamics Office Add-in.

Once installed, the add-in appears on the right side of the Excel window when working with Business Central data.

Understanding the Excel add-in

The add-in provides several key functions:

• New: Create new records
• Refresh: Pull the latest data from Business Central
• Publish: Send changes back to Business Central
• Filter: Limit the displayed records
• Design: Customize visible columns and layout

Refresh retrieves current data, while Publish updates Business Central with any completed changes.

Filtering data

Users can filter exported data directly within the add-in. For example, filters can be applied to:

• Account Numbers
• Balance Account Numbers
• Dates
• Dimensions
• Other available fields

Filtering allows users to narrow the data set and focus on only the records they need to update.

Customizing the Excel layout

The Design feature allows users to modify how data appears in Excel. Users can:

• Add fields
• Remove unnecessary columns
• Reorder columns
• Customize layouts for future exports

These customizations help streamline data entry and reduce clutter.

Adding and editing records

The Excel Connector supports both new records and updates to existing data. Common tasks include:

• Creating journal entries
• Updating account information
• Modifying amounts
• Adding dimensions
• Correcting existing records

Changes can be entered manually or copied and pasted into multiple rows to improve efficiency.

Publishing changes to Business Central

After making updates:

1. Review the data for accuracy.
2. Select Publish.
3. Wait for the update process to complete.
4. Review any messages or validation errors.

Successful publishing updates the Business Central records immediately. If errors occur, the add-in identifies the affected fields so they can be corrected before republishing.

Avoiding common errors

When editing data in Excel:

• Use valid values for fields such as dates and option fields.
• Review available values from dropdown selections when provided.
• Avoid editing read-only fields.
• Confirm that required fields are populated.
• Verify account balances and journal entries before publishing.

Read-only fields are identified within Excel and cannot be updated through the connector.

Best practices

To reduce the risk of large-scale data errors:

• Filter records before exporting.
• Remove unnecessary columns.
• Work with the smallest practical data set.
• Review changes carefully before publishing.
• Refresh Business Central after publishing to verify results.

Because large numbers of records can be updated simultaneously, careful review is important before committing changes.

Key takeaway

The Excel Connector provides a fast and convenient way to manage Business Central data directly in Excel. By combining filtering, customization, and direct publishing capabilities, users can perform bulk updates more efficiently while maintaining data accuracy through proper validation and review.