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 export the list to Microsoft Excel, and view it there. Depending on the page, you have two options for viewing in Excel. Both options are available from the Share icon at the top of a page. You can either select the Open in Excel action or the Edit in Excel action on the page. This article explains 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, without affecting data in Business Central.

• 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.

• If your organization has configured OneDrive for system features, the Excel workbook is opened in your browser by using Excel for the web. If you're not using OneDrive for system features, the workbook is downloaded to your device. From there, you can open it using either the Excel desktop app or Excel for the web, depending on what you have.

Important

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 Work with Reports - Save a report to a file.

Note

In Excel, whole numbers in columns will have a decimal symbol at the end (like a period . or comma ,) even though the decimal symbol isn't shown in Business Central. The decimal symbol depends on your device's region settings. For example, 10 in Business Central could appear as 10. or 10, in Excel. You can change the format in Excel by selecting the values, then selecting Ctrl+1. To learn more about changing the number format in Excel, go to Format Numbers.

Edit in Excel

The Edit in Excel action is available on most lists, but not all. With the Edit in Excel action, you make changes to records in Excel and then publish the changes back to Business Central. When Excel opens, you'll see the Excel Add-in pane on the right.

• 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.

• To get the latest data from Business Central, choose Refresh in the Excel Add-in pane.

• If your organization has configured OneDrive for system features, the Excel workbook is opened in your browser by using Excel for the web. If you're not using OneDrive for system features, the workbook is downloaded to your device. From there, you can open it using either the Excel desktop app or Excel for the web, depending on what you have.

First-time sign-in

The Edit in Excel action requires that the Business Central add-in is installed in Excel. In some cases, your administrator may have set up the add-in to install automatically for you. In this case, you just have to sign in to Business Central in Excel Add-in pane with your user name and password. Otherwise, the New Office Add-in pane opens. To install the add-in, choose Trust this add-in, which will install the add-in directly from the Office Store.

If the add-in doesn't install, either contact your admin or try to install it manually. For more information, see Install the add-in manually for your own use.

Work across environments and companies

You can switch the company that you're working with. To switch company, select the Options icon 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 menu in the top-right corner of the add-in. If you can't load the add-in, talk to your administrator. If you're the administrator, see Get the Business Central Add-in for Excel.

Note

The add-in works with Excel for the web (online) from any device as long as as use a supported browser. It also works with the Excel app for Windows (PC); but not for macOS.

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.

Limits when using Excel for the web

When Edit in Excel is used on list pages for tables with many columns, the resulting workbook may have too many columns for the file to be viewed in Excel for the web. Business Central automatically limits the exported workbook to 100 columns when OneDrive is configured for system features.

See Also

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

Find free e-learning modules for Business Central here