Using Excel in Business Central
If your organization has implemented Microsoft Dynamics 365 Business Central, you’re already aware of what a powerful tool it is for managing operations, finance, and customer data—all in one place. But with any ERP system, importing and exporting data efficiently can really impact your workflow.
Did you know that there’s a way to make Business Central even more efficient? A simple, yet powerful tool is available to further streamline your experience as a Business Central user—the Excel add-in. This integration enables users to view, manipulate, and share data with ease, flexibility, and control. The structure of Business Central and the familiarity and versatility of Excel join forces in the Excel add-in, allowing users to experience the best of both worlds. The add-in boasts several features that collectively streamline your processes and allow you to do more with less effort and time. Let’s dive in!
Edit in Excel
The Edit in Excel feature is located in the dropdown menu of the Share button, which is located near the top right corner of your Business Central window on most lists. When you select “Edit in Excel,” the connection to your live environment is maintained, meaning that any changes you make will be published back to Business Central upon selecting the Publish button. With that in mind, we encourage using extreme caution when utilizing this feature as you will alter live data when editing in Excel. This functionality respects most of the filters you can apply in Business Central, so your workbook will display most of the same filters and columns that you’ve applied to your data view in Business Central.
To make this feature even more representative of your live data, you can select Refresh in the add-in pane to sync the latest data from Business Central, just in case any changes have been made since you began editing in your excel workbook. We recommend selecting Publish and Refresh as you go, so that you can see how the updates you’ve made are sticking in the system.
To locate the add-in, navigate to the share options and select Edit in Excel.
The add-in tab—At the top of the add-in tab within Excel, you can view the add-in name, user, selected field source, and the field’s name. At the bottom, you have the following options:
New, which allows you to enter a new record.
Refresh, which syncs any new info from Business Central, or you can use after publishing to ensure that your entries have been pushed to the Business Central database.
Publish will send any changes you’ve made to Business Central. A pop-up window may appear to alert you if there are any errors in the changes you’ve made.
Filter helps you to find entries quickly.
Design allows eligible users to add tables, fields, labels & make edits to those selections, all within Excel. This feature is a bit more complex, and we recommend working with your Business Central partner or refer to other learning courses to best utilize this feature.
The Add-in tab is located on the right side of your window.
Open in excel
This feature allows you to export and manipulate records in Excel without affecting the data within Business Central, as it does not connect to your live environment. This is particularly useful for analyzing, storing, or transforming data without affecting the data in Business Central. It is perfect for exploring what-if scenarios, collaborating with other departments, and bulk updates. The data that you open in an Excel workbook will display most of the same rows and columns as shown in Business Central, so there’s no need to reorganize or reformat your data once you have opened it in Excel.
The remaining two options under the Share button aren’t related to Excel but are worth mentioning here—Share to Teams and Copy Link. Selecting either of these options will allow you to share your current data view via Microsoft Teams or a link to be pasted in an email or other communication channel. Anyone with access to your system with the same or higher permissions will have the ability to open the link and see what you see.
If you’re interested in a video demonstration of the features detailed in this blog, be sure to log in to your free Syvantis Learn account and watch the video here.
Beyond convenience, the Excel add-in for Business Central is beneficial for boosting productivity and bridging the gap between the structured nature of ERP data and the flexibility of spreadsheet editing. Users tend to appreciate the ability to interact with their Business Central data in the familiar environment of Excel, and the add-in facilitates efficiency, reduced manual errors, and streamlined collaboration across teams and departments. It really is a win-win for everyone.
Whether you’re updating bulk records, conducting a brief what-if analysis, or preparing reports, the Excel add-in maintains data integrity while saving you time. And user-friendly tools like this are essential for remaining agile as your business needs evolve. If you’re looking to learn more about Business Central beyond the Excel add-in, we have some great tips and tricks for you, as well as information about new features that were recently added to Business Central.
If you’d like to discuss further how Business Central with the Excel add-in can streamline your processes, get in touch with us. We’d love to show you more!