Export to an Excel dynamic worksheet

Export data to a Microsoft Office Excel worksheet so users can have the latest Microsoft Dynamics CRM information any time they view the worksheet. Imagine the CEO of your company getting the critical information they need without having to navigate Microsoft Dynamics CRM but instead, merely opening the Excel link on their desktop. You can export up to 100,000 records at a time.

Prerequisites

Dynamics CRM for Outlook is required to export data to a Dynamic PivotTable.

Export data to an Excel dynamic worksheet

You can’t export data to a dynamic worksheet in Excel for all CRM record types. If you don’t see the option, it’s not available for that record.

  1. Open a list of records in the CRM web application or in Microsoft Dynamics CRM for Outlook. Click or tap Export to Excel.

  2. Choose Dynamic worksheet.

  3. Under Common Tasks, configure the column settings and then choose Export.

  4. Choose Save and then save the . xlsx file. Make note of the location where you saved the file.


    Note

    If you’re going to edit the data file later, it’s recommended that you save the file before you open it. Otherwise, you may get this error message: Microsoft Excel cannot open or save any more documents because there is not enough available memory or disk space.

    To fix the issue do this:

    1. Open Excel and go to File > Options > Trust Center Settings Center Settings… > Protected View.

    2. In Protected View, uncheck all three items.

    3. Then choose OK > OK.

    We still strongly recommend that you save and then open the data file, rather than disabling protected view which may put your computer at risk.


  5. Open Microsoft Office Excel and then open the . xlsx file you saved in the previous step.

  6. If you see the security warning External Data Connections have been disabled, choose Enable Content.

  7. To refresh data in the file, on the Data tab, choose Refresh All.


    Note

    If you have a phone numbers that starts with + or , for example +1-123-456-7890, when you refresh the dynamic worksheet the phone number field will not display the number correctly.

    To avoid the issue, use a space or parentheses (), like this: +1 123-456-7890 or +1 (123)-456-7890


Tips

  • 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 Microsoft Dynamics CRM, so the data they see may be different from what you see.

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

  • In Microsoft Dynamics CRM, money values are exported to Microsoft Office Excel as numbers. After you have completed the export, to format the data as currency, see the Excel Help topic titled “Display numbers as currency.”

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

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

  • If you’re not on CRM Online 2015 Update 1 or Microsoft Dynamics CRM 2016 and are using the Office 365 Excel web app, you must save the file, open the file using the Excel desktop application, and then resave the file to the . xlsx format. You can then reopen the Excel document in Excel Online.

Privacy notice

If you use Microsoft Dynamics CRM 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 CRM Online to your computer by using a secure connection, and no connection is maintained between this local copy and CRM Online.

When you export to a dynamic worksheet or PivotTable, a link is maintained between the Excel worksheet and CRM Online. Every time a dynamic worksheet or PivotTable is refreshed, you’ll be authenticated with CRM 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.

Applies To

Dynamics CRM 2013

Dynamics CRM 2015

Dynamics CRM 2016

Dynamics CRM Online