How to Use Excel in Microsoft Dynamics 365 Business Central
Thanks to Microsoft Excel’s popularity and long-standing presence in the office, the spreadsheet tool has a large, loyal following. If you’ve been working with Excel for many years, you’ll be happy to know that Microsoft Dynamics offers a variety of ways to use it in combination with Business Central.
Microsoft Dynamics 365 Business Central users can connect Excel into Business Central for flexible, familiar data editing and reporting. Let’s explore the different options for using Excel with Business Central.
How Excel Can Be Used in Business Central
Excel is used in Business Central for:
- Reporting purposes
- Importing data
- Copying and pasting data directly into your Business Central environment
- Editing data directly before publishing it to Business Central
If you love Excel, you’ll love Business Central. The following illustration shows the item list in Business Central. It visually explains the three main concepts of using Excel with regard to your items in Business Central.
1. Open Excel in Business Central
The “Open in Excel” option sends the Business Central page to Excel for further analysis. It’s important to note that this is one-way. Therefore, use this feature only when you don’t plan to make changes and publish them back into Business Central. The option to open your data in Excel is available from any Business Central journal, list, or worksheet. Simply open the page you’re interested in, and click “Open in Excel.” From here, you’ll have the option to open the resulting Excel workbook immediately or save it to your computer.
This is a great feature for Business Central users who are used to build reports in Excel and prefer its interface for their data analysis.
2. Edit Business Central Data in Excel
The “Edit in Excel" option, like the “Open in Excel” option, creates an Excel file with the data on the Business Central page. Additionally, you now have the option to make changes to the data in Excel and send it back to Business Central. On our eLearning portal, you can find a training video on how to edit in Excel, based on the customer master data as an example.
Microsoft also has a short video on how to work with Excel in Business Central. The “Edit in Excel” feature is great for making mass updates to your data or for analyzing transactional data.
3. Refresh Excel Data in Business Central
You have the option to refresh Excel data in Business Central, too. The Excel file that you created by using the “Edit in Excel” feature can be saved with its link to the data source in Business Central. This way, it can be used to refresh the data at any point in time.
Other options that let you work seamlessly with Business Central and Excel include:
- Default Excel reports directly from your role center
- Copy and paste from Business Central to Excel and back (ideal for journals)
- Budgeting in Excel
- Financial reporting and consolidating in Excel
- Create a query in Business Central using Web services
- Querying your Business Central data from within Excel using “Get Data From Odata feed”
Under the terms of this license, you are authorized to share and redistribute the content across various mediums, subject to adherence to the specified conditions: you must provide proper attribution to Stoneridge as the original creator in a manner that does not imply their endorsement of your use, the material is to be utilized solely for non-commercial purposes, and alterations, modifications, or derivative works based on the original material are strictly prohibited.
Responsibility rests with the licensee to ensure that their use of the material does not violate any other rights.