Excel is to accountants what a pocket knife, ball of twine, and stick of chewing gum is to MacGyver; it brings order to chaos and can be used to solve almost any problem. As Ramin Marghi put it: “It’s safe to say that Microsoft Excel is – by orders of magnitude – one of the most incredible applications of human ingenuity, occupying a nice, warm seat right next to the Polio vaccine, space travel, and those little clips that you put on bags of cereal to prevent your Lucky Charms from going stale.” Thus, one of the key functions of Dynamics 365 Business Central is the integration with Excel, allowing you to get data from Business Central to Excel, and even allow you to make changes to the data in Excel and publish those changes back into Business Central.

Working with Business Central data in Excel is an area where we get some questions, so we thought it’s worth a blog article to outline some of the options for getting your financial data into Excel from Business Central. Each of these is appropriate for different use cases. This post provides an overview of these options, including some new additions in recent releases.

Copy and Paste

One method for quickly getting a few selected rows from a list in Business Central to Excel is with the traditional copy and paste. This was an addition with the shortcuts added in the October release of Business Central. You can select multiple rows in a list with either Shift + Click or Ctrl + Click, then use Ctrl + C to copy the rows to your clipboard. The Ctrl + V shortcut then pastes then into Excel, or other applications you may be working with. You can also go the other direction, copying rows from Excel and pasting them into a list in Business Central.

For more information on copying and pasting, see Copying and Pasting in Business Central.

Open in Excel

Open in Excel is a new feature, released in Business Central in a recent update. Selecting this option on a list page in Business Central will take the list of data as it displays in the list, including filtered rows and columns, and send it to an Excel spreadsheet. You then have the data available in Excel to view. There isn’t an option to refresh the data or make edits that publish back to Business Central, but provides the ability to quickly get the data to a spreadsheet for analysis.

Edit in Excel

The Edit in Excel action available on list pages is a little more complex than the Open in Excel option. This action will open the data in an Excel workbook, and uses the Microsoft Dynamics Office Add-in to connect directly to the data through an OData web service. This allows you to refresh the data from Excel, as well as make changes to the data in the Excel spreadsheet, and publish those changes back into Business Central. The add-in uses your Business Central authentication, so you only get access to view and work with data that your Business Central permissions allow. Data validations also ensure that you only publish data that follow business rules in the application.

Click here for a short video on some of the differences between the Open in Excel and Edit in Excel options.

Filtering and Column Selection

When selecting the Edit in Excel action on a list page in Business Central, the data that gets exported is the full list, including all rows and columns of data from the source. The action doesn’t take any filters or column selections into consideration when exporting the list to Excel. This can be a challenge, but there are options for filtering the data in Excel.

From the add-in pane, you can select the Filter action to set a filter on the data source. On the Filter page, you can add one or more filters to the data to only show the data set that you want to view. Select the Design action to select which columns you want to display in the list, and the order in which they display.

Saving the Excel workbook to a local or cloud drive also saves the applied filters and columns, so it’s an option to have easy access to the data view if it’s one you work with regularly.

Reporting Data Setup

The Reporting Data Setup wizard, opened from the Assisted Setup page, allows you to define the exact data and view that you want to work with, and export the data to an Excel workbook. The wizard allows you to select the source table, select the columns and apply any filters you want to the data set so it shows only the data that you want to work with. You can then publish the custom data endpoint, and open it in Excel.

This option is similar to using the Edit in Excel action from a list page in Business Central in that it uses the Microsoft Dynamics Office Add-in to connect to the data. So you can still make changes to the columns and filters in the add-in pane in Excel. It also allows you to make changes to the data in Excel and publish the changes back to Business Central. Saving the workbook saves the data connection, including all the filter and column selections, allowing you quick access to the view.

Web Services

Another option is to use the flexibility that Business Central provides in creating and customizing OData web services. If you just want a refreshable, filtered list of data in Excel, and don’t need to publish changes to Business Central, this is a good option. Any web services you create in the Reporting Data Setup wizard are listed on the Web Services page in Business Central. You can also create new web service for Business Central pages directly from this page. Excel makes it easy to connect to Business Central data using these web services.

On the Data tab in Excel, select Get Data - From Other Sources - From OData Feed. Copy and paste in the URL of the web service from the Web Services page or Reporting Data Setup. Once you’ve entered credentials to authenticate against the web service, it gives you a live connection to the data. You can then refresh the connection to get any updates that have occurred to the data in Business Central.

Summary

There are several options for getting data from Business Central into Excel, each with its strengths and weaknesses that make it the best option for specific use cases. Give them a try, and, as always, provide feedback on how we can improve this experience on the Dynamics 365 Business Central Ideas site.