How to Use Excel in Microsoft Dynamics 365 Business Central

By Theo Kersten | February 28, 2020

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”

Related Posts

Recommended Reading:

Managing Your Business Through Uncertain Times Using Dynamics 365 Finance and Operations

  Dynamics 365 Finance and Operations (F&O) can help you make informed decisions on how to move your business forward. […]

Read the Article
5.13.22 Power Platform

Using Power BI Object Level Security

  The following article will demonstrate how to use Power BI Object Level Security to disable column data based on […]

Read the Article
5.12.22 Dynamics CRM

How to Use the Stoneridge Support Portal

Stoneridge Software’s support portal is an intuitive and useful function that makes it easy for you to access resources to […]

Read the Article
5.6.22 Dynamics GP

Dynamics GP Transaction Removal: Purchase Orders

  Are you having performance issues with Purchase Orders?  Do you find that there are old Purchase Orders on your […]

Read the Article
5.5.22 Dynamics GP

The Real Story about the Long-Term Future of Dynamics GP Support

I’ve seen a number of people put forward comment that Dynamics GP is going away and you have to get […]

Read the Article

New Features in Dynamics 365 Business Central 2022 Wave 1 Release – Financial Enhancements

The Dynamics 365 Businses Central 2022 Wave 1 Release has a lot of new and exciting features to help your […]

Read the Article
4.29.22 Dynamics GP

Dynamics GP Transaction Removals: Bank Reconciliation

  This is part 2 of a 3 part series on Dynamics GP Transaction Removals. These quick tips will hopefully […]

Read the Article
4.28.22 Dynamics GP

Uncommonly Used Features – Integrate Purchasing and Payables to Fixed Assets in Dynamics GP

Being able to integrate Purchasing and Payables to Fixed Assets in Dynamics GP will help you create visibility for your […]

Read the Article

New Features in the Dynamics 365 Business Central 2022 Wave 1 Release – Ease-of-Use Features

  There are many new features in the Dynamics 365 Business Central 2022 Wave 1 Release to get excited about! […]

Read the Article

Start the Conversation

It’s our mission to help clients win. We’d love to talk to you about the right business solutions to help you achieve your goals.

Subscribe To Our Blog

Sign up to get periodic updates on the latest posts.

Thank you for subscribing!