Editing data in Excel directly from Microsoft Dynamics 365 Business Central can significantly streamline your business processes, allowing for a more flexible and efficient way to handle data.
I always like to demonstrate the simplicity that Edit in Excel offers with powerful results by telling a story of when I was an end user from Dynamics GP.
We used to have hundreds of customers and every customer had a salesperson assigned. If that salesperson left or a group of customers changed, we had to either 1. Update the salesperson on hundreds of customer cards one by one or 2. Engage with our partner to update the salesperson in the SQL database. Neither was super desirable. But, with Business Central, you don't have to do either! Instead, you can utilize Edit In Excel to do the hard work for you! Here's how you do it:
Firstly, ensure that you have the necessary permissions to edit data in Business Central. Once confirmed, navigate to the list page that contains the data you wish to edit. This could be a list of customers, orders, or any other data set relevant to your business needs. Look for the 'Edit in Excel' option, which is typically located in the ribbon or under the 'Share' icon at the top of the page.
Upon selecting 'Edit in Excel', Business Central will prompt you to open or save an Excel file that contains the data from the list page. Open this file in Excel, and you'll notice that it may open in a protected view. To edit the data, you'll need to enable editing by clicking the button at the top of the Excel workbook.
With editing enabled, you can now make changes to the data as you would in any Excel file. Utilize Excel's powerful data manipulation features, such as sorting, filtering, and using formulas to update multiple records simultaneously. This is particularly useful for making bulk changes or updates to your data.
After making the necessary edits, it's time to publish the changes back to Business Central. To do this, use the 'Publish' button in the Excel Add-in pane, which should be visible on the right side of your Excel workbook. This action will upload the changes you've made back into Business Central. It's important to note that Business Central will perform validation checks to ensure that the data you're uploading is correct and adheres to the system's rules.
Once you've published your changes, it's a good practice to go back to Business Central and refresh the list page to verify that your edits have been successfully applied. This step ensures that the data in Business Central is up-to-date and reflects the changes you made in Excel.
In conclusion, the 'Edit in Excel' feature in Business Central is a powerful tool that can enhance your data management capabilities. By following these steps, you can efficiently update and manage your business data, leveraging the full potential of both Business Central and Excel. Remember to always check for the latest updates and best practices to ensure a smooth and productive experience with this feature.
Tips!
DO NOT SLEEP ON DESIGN. Design lets you change the order of columns and even add/remove columns. Utilize Design to make Edit in Excel even more amazing!
Edit in Excel updates, creates new and deletes records. Save a copy of the data before you publish if you are deleting records, just in case you need to recreate them!
If you can't do something by hand in BC, you can't do it in Edit in Excel. What I mean by that is, if you can't delete a customer in BC because it has records posted to it already, then you can't do it in Edit in Excel either; it follows the same rules as BC.
Excel is still Excel. If you are trying to update 1,000,000 records, you are still dealing with Excel limitations on data and speed. Keep that in mind before exporting and importing large amounts of data. You can filter your list pages before exporting to excel to make your data run faster.
Comments