Business Central makes analyzing and working with your financial data
simple with integration to Microsoft Excel.
This video provides an overview of how you can export
your financial data to Excel as well as modify your
data in Excel and publish the changes back to your
database and Business Central. Export data to Excel.
Business Central allows you to easily export data from a
list page to Excel, enabling quick analysis of your data
using the extensive data analysis tools Excel provides.
For example let's say you want to create a report
from your customer ledger entries that shows quarterly sales by
customer. Start by searching for and opening your Customer Ledger
Entries page. On each supported list page you will see
an Edit in Excel button on the Home tab of
the ribbon. Click this button to open the data in
Excel. When the workbook opens the Microsoft Dynamics Office Add-in
opens,
creating a connection to the data and providing controls for
working with the data connection including the ability to refresh
the data.
Once the data is open in Excel we can then
use the application's powerful reporting and charting functionality to work
with the data. We can view the quarterly sales by
customer we wanted to see by creating a simple
PivotTable.
Choose to insert a PivotTable from the Insert tab
on the ribbon. We then have a simple drag and
drop experience for creating the charts and graphs we need.
From the available fields we'll drag them to the appropriate
sections of the report.
The Customer Ledger Entries page contains all document types for
customers.
To view sales we can create a filter for the
table to view just the posted invoices this week. We can then
do some quick data formatting and make any other visual
changes we want, and we're done.
This gives us a table with the information we wanted
to see.
We can also easily add a chart to visualize this information.
In a few clicks we can add a PivotChart
to our report.
Getting the data to Excel enables powerful reporting abilities, all
of which can be refreshed through the data connection to
ensure that the reports and charts you create are always
up to date. Edit data in Excel.
Business Central also enables you to easily update records.
Let's start on the Customers list page. In this example
we'll assume we want to update the document sending profile
for all our customers. This could take a while if you
had to update the field one by one on each
customer card.
We can speed this up by making the updates to
the list of data in Excel.
We can open the data in Excel by clicking the
Edit in Excel button on the ribbon.
Once the data is refreshed in Excel we can make
the changes we need.
For the Coho winery let's change the document sending profile
value to Email and publish the change.
Note that the process to publish the data also includes
validation of the data. If the data we had
entered and tried to publish is invalid, the row with
invalid data is highlighted and a message provides information on the
changes made.
In this case Email isn't a profile that has been
set up in the system so we need to enter
a different profile. We'll change the document sending profile to
DirectFile and quickly fill down the changes to all
customers.
We can then publish the changes again now that a
valid profile has been entered for the lines.
Once the changes have been published successfully I can then
go back to the customer card and see that the
field is been updated correctly for my customers.
Conclusion, integration with Microsoft Excel provides quick and powerful data
analytics as well as simple options for updating your data.
Enjoy working with Microsoft Dynamics 365 Business Central.
Business solutions reinvented.