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 the Customers list page and click on the mini dropdown located next to the action group called Page. From the drop-down list, select Open in Excel. This will download the exported Customers list data on your local machine.
Step 2: Export the File
From the drop-down list, click on “Open in Excel”. This will export the Customer list data to excel and automatically download the file on your local machine
Step 3: Find and open the downloaded excel file.
Step 4: Create the Pivot Table
- 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.
- On the Excel menu ribbon, select Insert.
- 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.
- Click OK.
Step 5: Design the Summarised data in Pivot Table
- A new Sheet will be added in Excel.
- On the Pivot Table Fields you can select the State and Name.
- Drag them one at a time on the Rows and Values respectively.
- 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.
- Select any cell on the Pivot Table and then select Insert.
- Click on the Chart Icon and this will automatically create a Pivot Chart with the data of the Pivot Table.
Rishi is a Solution Specialist at EBS. He has experience in IT with a broad knowledge within the education, manufacturing, supply chain, retail and customer service industries. With extensive experience in Microsoft Dynamics NAV and Business Central with consulting experience, running business process workshops, extending standard functionality and integrating external applications to the base product.