Edit in Excel - An Introduction | Business Central

Overview

Edit in Excel is used to Insert, Modify or Delete records in Business Central.

This provision is available throughout the system. Pages that display records in the form of matrix like masters, transactions, archived pages and also the entry pages like journals (except Recurring Journals) support Edit in Excel functionality.

To understand the process, let's take an example of General Journals.

figure 1.0

As shown in the figure 1.0, we have created a few journal lines manually.

Now, creating and posting several journal lines in bulk becomes cumbersome for a user. In such scenario, Edit in Excel comes in handy.

Assisted Process

The steps explained below will take you through the process.

• Select Action

figure 1.1

To select the Edit in Excel action go to action group Page and select the appropriate action as shown in figure 1.1.

• Generate Excel

System will generate an Excel workbook as shown in figure 1.2.

figure 1.2

• Data Connector Add-In

Through data connector add-in for Microsoft Dynamics in Excel, the system will fetch the records from page (figure 1.3).

When using Edit in Excel for the first time, the user must provide their Dynamics Business Central credentials through Sign in process (figure 1.4).

figure 1.3

figure 1.4

On successful authentication, the Excel gets populated with existing rows from page.

figure 1.6

• Inserting New Lines

To create more journal lines, user just needs to use New action from Data Connector window.

figure 2.0

figure 2.1

User can simply copy & paste from existing rows in Excel and create as many records as required with minimum efforts.

• Modifying Existing Lines

User can not only add new journal lines but they can modify existing lines also, if needed. For example, amount of Advertising Expense needs to be changed from 60 to 80.

figure 2.2

figure 2.3

• Deleting Existing Lines

User can remove existing row(s) from Excel, if needed. For example, journal line for Interest Expense needs to be removed. Here, user just needs to delete the respective row from Excel.

(Notice the difference between figure 2.3 & figure 2.4. Document No. JOU003 has been removed)

figure 2.4

• Publish Records

User can simply execute the Publish action from Data Connector window in Excel (figure 2.5).

figure 2.5

On executing the action, user will receive a confirmation alert (figure 2.6).

figure 2.6

On confirming the action, the Data Connector will populate the rows in General Journal lines in Business Central. User just needs to execute F5 to refresh the page (figure 2.7).

figure 2.7

figure 2.8

Notice the lines populated in Journal. All activities that we did in Excel have been reflected here (figure 2.8).

• Validations

Along with the facility to synchronize records in the Journal, Edit in Excel tool facilitates validation of respective records in Excel itself.

figure 3.0

figure 3.1

Here, as shown in figure 3.1, we have changed Bal_Account_No from 10000 to 11111 (Note: Account No. 11111 is not available as Vendor).

When we try to publish the records, data authentication process will be executed and show error(s), if any (figure 3.6).

figure 3.2

figure 3.3

List of all validations can be found through Flag icon at the bottom of the Data Connector window (figure 3.5).

figure 3.5

figure 3.6