Leverage Microsoft’s Modern Workplace tools

Microsoft Dynamics 365 Business Central and Microsoft 365 Excel work together in the Microsoft technology stack to seamlessly make your life easier and more productive. The Excel feature in Business Central provides the ability to export data to Excel with one click and create a report without any enhancements or modifications.

Today, we’ll show you how to export the Customer data from Business Central and create a simple Customer report summarised by State in Excel. We will use the Pivot Table feature in excel as it is a great tool to reorganise and summarise data.

Step 1: Select the Data 

In Business Central, navigate to Customer’s list page. Click on the first icon located towards the right of the action ribbon.

Exporting data to Excel from Business Central

 

Step 2: Export the File 

From the drop-down list, select “Open in Excel”. Click on “Open in Excel” to download the exported Customer list data on your local machine.

Exporting data to Excel from Business Central

Step 3: Find and open the downloaded excel file.

 

Step 4: Create the Pivot Table

  1. Hover over the columns to select data in the Customer List that you want to. You may also delete the columns that you do not require prior to selecting the columns. In our case we select the No., Name and State.
  2. On the Excel menu ribbon, select Insert.
  3. Click on Pivot Table. This will open the Create Pivot Table and the Table/Range on the window will be automatically set to the selected columns.
  4. Click OK.

 

Step 5: Design the Summarised data in Pivot Table

  1. A new Sheet will be added in Excel.
  2. On the Pivot Table Fields you can select the State and Name.
  3. Drag them one at a time on the Rows and Values respectively.
  4. As a result, the table will be summarised by State and the count of customers in each state with a grand total.

Step 6: Enhance the data

To further enhance the Customer Report summarized by State you can insert a chart next to the summarised table.

  1. Select any cell on the Pivot Table and then select Insert.
  2. Click on the Chart Icon and this will automatically create a Pivot Chart with the data of the Pivot Table.

Was this article helpful?