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 to your computer.

This is a great feature for Business Central users who are used to building 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 query in Business Central using Web services

  • Querying your Business Central data from within Excel using “Get Data From Odata feed”

If you have any questions regarding any of these options, please don’t hesitate to leave a comment or contact a member of our team. We’d love to hear some of your experiences with using Excel and Business Central.

Check out our training services, too, if you’d like to learn more about integrating Excel into Business Central.

Theo Kersten

Theo is the Director at the Center of Excellence for Advanced Business Systems (ABS). He earned his degree in Economics and started working with Dynamics NAV in 2003 in the Netherlands, shortly after it had been acquired by Microsoft and rebranded to Dynamics 365.

Microsoft Gold Partner Logo_edited.png
  • LinkedIn Social Icon
  • Twitter Social Icon
  • Facebook Social Icon
  • YouTube Social  Icon

Austin  |  Dallas  |  Houston  |  New Orleans  |  Nashville