Working with business data in Excel

Completed

With pages that display a list of records in rows and columns, like a list of customers, sale orders, or invoices, you can view your records using Microsoft Excel. To do this, you have two options. You can either select the Open in Excel action or the Edit in Excel action on the page. The differences between the two actions is as follows:

  • Open in Excel

    • You can make changes to the records in Excel, but you cannot publish the changes back to Business Central. You can only save the changes to Excel file on your computer.

    • With this action, Excel respects any filters on the page to limit the records shown. This means that the Excel workbook will contain the same rows and columns that appear on the page in Business Central.

    • For Business Central on-premises, the Open in Excel action is not available if the Edit in Excel action is used.

  • Edit in Excel

    • The advantage of the Edit in Excel action is that it lets you make changes to records in Excel and then publish the changes back to Business Central.

    • With this action, Excel respects most filters on the page that limit the records shown. This means that the Excel workbook will contain almost the same records and columns.

    • This includes being able to view and edit fields that are added through extensions.

    • The Edit in Excel action has also been enabled on various Sales and Purchase document subpages, such as Sales Order Lines.

    • For Business Central on-premises, the Edit in Excel action is only available if the Excel add-in has been installed by your administrator.

You can open data in Excel from any journal, list, or worksheet. You just open the page that you want, and then choose Open in Excel. For example, open the list of customers (search for Customers), and then choose Open in Excel.

Screenshot of the Open In Excel feature on the customer list.

Your browser will prompt you to open or save the generated Excel workbook.

Screenshot of the generated Excel workbook open.

Use this option when you do not want to make changes and publish those changes back to Business Central.

Each list includes several columns, and the export to Excel will include any columns that are in your current view. If you want to add or remove columns before you open the list in Excel, you must personalize the current page and then specify the columns that you want to see.

Screenshot of the Settings with Personalize option.

Screenshot of the Personalization page displayed.

If you have set up more than one company in Business Central, you must export the relevant data from each company.

Edit in Excel

You can also export your Business Central data to Excel, edit it, and then publish it back to Business Central.

Screenshot of the Edit In Excel export feature.

Your Business Central experience includes an add-in for Excel. You may be logged in to Business Central automatically, or you may need to specify the same login details that you use for Business Central when you use the plugin.

With the add-in, you can get fresh data from Business Central, and you can push changes back into Business Central.

Screenshot of the Excel Add-In feature.