Export data to Excel

Dynamics CRM 2016
 

Updated: November 1, 2016

Applies To: Dynamics 365 (online), Dynamics 365 (on-premises), Dynamics CRM 2013, Dynamics CRM 2015, Dynamics CRM Online, Dynamics CRM 2016

Do you need to analyze your data from Microsoft Dynamics 365 and convert that data into actionable items that help you drive more sales? Now you can do this when you export your data to Microsoft Excel or Excel Online to do a quick data analysis. Also, analyzing large datasets is not a problem because you can export up to 100,000 rows of data.

You can choose to export static worksheets or dynamic worksheets, which you can import back into Dynamics 365. If you need more advanced functions, you can export a dynamic PivotTable, which makes it very easy to organize and summarize data.  

Export data to a standard Excel file that that you can use on any device such as your phone, tablet, or desktop computer. The data is exported in the same format as you see in Dynamics 365. Text will remain text, numbers will remain numbers, and dates will remain dates. However, when you export data from Dynamics 365 to Excel the some cell format may change. The table below summarizes how you’ll see the data in Dynamics 365 and how the cell format changes when you export the data to Excel.

Data format in Dynamics 365

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 Dynamics 365

Secured fields

Editable in Excel but can’t be imported back to Dynamics 365


Task

Learn more

Do an ad-hoc or what if analysis without modifying Dynamics 365 data. Or, quick bulk edit to multiple Dynamics 365 records.

Analyze your Dynamics 365 data in Excel Online

Get a snapshot of the data at the current data and time or you want to share it with others.

Export to an Excel static worksheet

Get the most update-to-date information and be able to refresh it in Excel and match what you see in Dynamics 365 at any time.

Export to an Excel dynamic worksheet

View Dynamics 365 data in a pivot table.

Export to an Excel PivotTable

  • When you export data in Excel (.xlsx format) and then add or modify columns, you can’t import the data back in to Dynamics 365. This is not support for .xlsx file format.

  • If you’re using Excel 2010, \ you may get this error message when you export data from Accounts area: “The file is corrupt and cannot be opened.”

    The error message occurs due to a setting in Excel. To fix the issue, do this:

    1. Open Excel 2010

    2. Go to File > Options.

    3. Go to Trust Center > Trust center settings.

    4. Click Protected view. Then clear the check boxes for the first two options.

    5. Click OK and then close the Options dialog box.

If you use Microsoft Dynamics 365 (online), exporting data to a static worksheet creates a local copy of the exported data and stores it on your computer. The data is transferred from Dynamics 365 (online) to your computer by using a secure connection, and no connection is maintained between this local copy and Dynamics 365 (online).

When you export to a dynamic worksheet or PivotTable, a link is maintained between the Excel worksheet and Dynamics 365 (online). Every time a dynamic worksheet or PivotTable is refreshed, you’ll be authenticated with Dynamics 365 (online) using your credentials. You’ll be able to see the data that you have permissions to view.

An administrator determines whether or not an organization’s users are permitted to export data to Excel by using security roles.

Show: