Business Central How-To : What is Edit in Excel?

Have you heard about the Edit in Excel feature in Business Central?

This function is a powerful feature that allows users to export data from a list page, edit it in Excel, and then publish the updated data back to Business Central. You can even use it in Journal pages.

Here’s a super-quick overview:

1. Export the Data: Users can export data from BC to Excel. This is particularly useful for list pages like customers, sales orders, or invoices.
2. Edit in Excel: Once the data is in Excel, users can make changes using Excel’s robust editing features. This includes updating records, adding new data, and even utilizing Excel’s calculation functions.
3. Publish Your Changes: After editing, users can publish the changes back to BC. The system performs validation checks to ensure that the data is correct and prevents the entry of invalid data.
4. The Excel Add-In: When opening Excel through this function, users will see the Excel Add-In pane on the right side – this facilitates the interaction between Excel and BC.

Sounds good, right? Of course, everything has some limitations. Whilst it is possible to deploy this function in an On-Prem environment, there are extra steps and it’s important to ensure these are correctly in place or users could have trouble in publishing back their data. Another point to keep in mind is that, where used on list pages with many columns, the resulting workbook may have too many columns for viewing in Excel for the web. BC automatically limits the exported workbook to 100 columns when OneDrive is configured for system features.

So, what’s the big difference between Edit in Excel and Open in Excel?

The Open in Excel action allows you to view data from Business Central in Excel. With this feature, the user can export data to Excel, and the resulting workbook will contain the same rows and columns as displayed in BC. The user can make changes to the records in Excel, but these changes do not affect the data in BC.

You’d want to use this when you need to analyse or manipulate data in Excel without updating the central database. This function can be helpful for validating report results or exploring data.

Based on the above, you can probably already see where Edit in Excel provides a wider functionality, but let’s explore a little further on the differences. Did you know that you can switch the company you’re working with within Excel? The Edit in Excel functionality is best used for where you need to update data in BC quickly and efficiently, allowing you to make bulk changes & calculations.

If you’d like to discuss more about how Edit in Excel and Business Central can transform your business, reach out today to Brookland Solutions for a no obligation discussion. Get in touch!