First Step : Overview on export data to Excel within Dynamics 365.

2

min read

A- A+

read

Dynamics 365 CRM offers the possibilities to export data to Excel file. The data will be usually exported in the same format than in Dynamics 365 but in some case, the cell format will slightly change. Here is the cell format in excel following the format in D365 :

Data format in Customer Engagement	Cell format in Excel
Text, Ticker Symbol, Phone, Options set, and Look Up	Shows as Text and option set becomes drop-down list
Email, URL	Shows as General
Number	Shows as Number without group separator
Currency	Shows as Number and does not include “$” symbol
Date only, Date and Time	Shows as Date only
Calculated and Roll up fields	Editable in Excel but can’t be imported back to D365
Secured fields	Editable in Excel but can’t be imported back to Customer Engagement

Money values are exported to Excel as numbers. After you have completed the export, you will need to format the data as currency.

The data and time values that you see in Dynamics 365 show up as Date only when you export the file to Office Excel but the cell actually shows both the date and time.

You can export up to 100,000 records at a time.

If you’re going to make changes and import the data file back in to Dynamics 365, remember that secured, calculated, and composite fields (such as Full Name) are read-only and can’t be imported. You’ll be able to edit these fields in Excel but when you import the data back in the CRM, these fields won’t be updated. If you want to update these fields such as a contact’s name, it’s recommended that you use that view to export your data, update them in Excel, and import them back to Dynamics 365 for changes.

It exists three type of export. You must choose which type of export is best for you.

Excel static worksheet: Get a snapshot of the data at the current data and time or you want to share it with others.
Excel dynamic worksheet: Get the most update-to-date information and be able to refresh it in Excel and match what you see in Customer Engagement (on-premises) at any time.
Excel PivotTable: View Customer Engagement (on-premises) data in a pivot table.

You will find below tips about each types of export.

Static Worksheet

When you want to present CRM information to an individual who doesn’t have access to Dynamics 365 Customer Engagement, or you have data that doesn’t change often, consider using the Excel static worksheet.

You can export up to 100,000 records at a time. And by default, Dynamics 365 lists up to 50 records per page. select the “Page” arrows at the bottom of the list to view any additional pages.

You can email a static exported worksheet to anyone or store it in a shared file. Anyone who opens the file will see all the data in the file, whether or not they are a Dynamics 365 user or have privileges to view the data.

You can’t change the columns for a system view, such as “All Active Accounts”. You must either customize the view, which requires the System Administrator or System Customizer security role or use Advanced Find to create your own view based on the current view.

Some system views, such as Accounts: No Campaign Activities in Last 3 Months, can be exported only to a static Excel worksheet.

Dynamic Worksheet

Using this type lets users having the latest information any time they view the worksheet without connecting to the CRM.

You can email a dynamic Excel file or store it as a shared file if the recipients are in the same domain as you. When recipients open the dynamic file, they’ll see data they have permission to view in Dynamics 365, so the data they see may be different from what you see.

PivotTable

Use PivotTable to see patterns and trends in data. It is a great way to summarize, analyze, explore, and present your data.

If you export a list to a PivotTable that you think will be useful to other users, you can add the list as a report, and then share it with others or make it available to all Dynamics 365 users.

As for Dynamic Worksheet, you can email a dynamic Excel file or store it as a shared file if the recipients are in the same domain as you. When recipients open the dynamic file, they’ll see data they have permission to view in Dynamics 365, so the data they see may be different from what you see.