Viewing and Editing in Excel From Business Central

With pages that display a list of records in rows and columns, like a list of customers, sale orders, or invoices, you can also view the records using Microsoft Excel. Depending on the page, you have two options for viewing in Excel. You can either select the Open in Excel action or the Edit in Excel action on the page. This article explains the differences between the two actions.

Open in Excel

With the Open in Excel action, you can make changes to the records in Excel, but you can't 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 that limit the records shown. The Excel workbook will contain the same rows and columns that appear on the page in Business Central.

  • This action works on both on Windows and macOS.

  • Starting with update 18.3, you can also view lists that are shown in page parts, like the lines in a sales order. For now, this capability is an optional feature, which requires that you enable Export any list part to Excel in Feature Management. For more information, see Enabling Upcoming Features Ahead of Time.

Note

For Business Central on-premises, the Open in Excel action is available by default. However, if you set up Business Central on-premises for editing data in Excel, then the Open in Excel action is replaced by the Edit in Excel action.

Tip

What about reports? Well, you can export the report results to an Excel file for viewing the full dataset, including all columns. The Excel file can help you validate that the report returns the expected data. To export a report, run the report and select the Send to > Microsoft Excel Document (data only) on the request page. For more information, see Working with Reports - Send to Excel.

Edit in Excel

With the Edit in Excel action, 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, so the Excel workbook will contain almost the same records and columns.

  • It only works on Windows; not macOS.

  • You can switch the company that you are working with. To switch company, select the Options icon Excel add-in options. in the Excel Add-in pane, then select the company from the Company field.

    Important

    When changing the company, make sure that the Environment field is not empty. If it is, then set it to one of the available options; otherwise, the add-in will not work correctly.

If you make changes to the add-in, you must reload it to update the connection. To reload, use the Excel add-in menu menu in the top-right corner of the add-in. If you cannot load the add-in, talk to your administrator. If you are the administrator, see Setting up the Excel Add-In for Editing Business Central Data.

Note

For Business Central on-premises, the Edit in Excel action is only available if the Excel add-in has been configured by your administrator, and only available for the Web client. For administrators, if you want to learn how to install the Excel add-in, see Setting up the Excel Add-In for Editing Business Central Data.

See the differences between the options



See Also

Analyzing Financial Statements in Microsoft Excel
Working with Business Central
Enhancements to Excel integration in 2019 release wave 2