Dynamics 365
Help & Training
Search

Export to an Excel PivotTable

You can export Microsoft Dynamics 365 data to a Microsoft Office Excel PivotTable to see patterns and trends in data. An Excel PivotTable is a great way to summarize, analyze, explore, and present your Dynamics 365 data. You can export up to 100,000 records at a time.

Prerequisites

  • Microsoft Dynamics 365 for Outlook is required to export data to a PivotTable.

  • To view and refresh dynamic data, Microsoft Dynamics 365 for Outlook must be installed on the same computer you're using to view the Excel data.

  • On a default Microsoft Dynamics 365 installation, before you export data to an Excel PivotTable, make sure that your Microsoft SQL Server allows remote connections.

    Allow remote connections to SQL Server

    1. Start Microsoft SQL Server Management Studio.

    2. Connect to the SQL Server instance.

    3. Right-click the SQL Server instance name, click Properties, click Connections, and then select the Allow remote connections to this server check box.

  • Windows Firewall allows remote SQL Server connections. More information:  How to: Configure a Windows Firewall for Database Engine Access.

Export to an Excel PivotTable

The option to export data to an Excel PivotTable isn’t available in all Dynamics 365 record types. If you don’t see the option, it’s not available for that record.

  1. Open a list of records in the Dynamics 365 web application or in Microsoft Dynamics 365 for Outlook. In the web app, click the arrow to the right of Export to Excel. In Dynamics 365 App for Outlook, click Data > Export to Excel.

  2. Click Dynamic PivotTable.

  3. In the Select PivotTable Columns list, select or clear the check boxes for the fields as needed, and then click Export.

    By default, the PivotTable Field List includes only fields that are displayed in the Select PivotTable Columns list.

  4. Click 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: 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

    2. Click Trust Center Settings, and then click Protected View.

    3. Under Protected View, clear the check boxes for all three items.

    4. Click OK, and then 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 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, click Enable Content.

  7. To refresh data in the file, on the Data tab click Refresh from CRM.


    Note

    To view and refresh dynamic data, Microsoft Dynamics 365 for Outlook must be installed. If it is already installed and configured, click Refresh from CRM to sign in to Microsoft Dynamics 365. If you do not want to be prompted again to sign in, click Save my email address and password in the Sign-In page.


  8. Drag the fields from the PivotTable Field List to the PivotTable. For more information, see Excel Help.

Tips

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

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

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

  • The data and time values that you see in Microsoft Dynamics 365 show up as “Date” only when you export the file to 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 Dynamics 365, remember that secured, calculated, and composite fields (such as Full Name) are read-only and can’t be imported in to Dynamics 365. You’ll be able to edit these fields in Excel but when you import the data back in to Dynamics 365 these fields won’t be updated. If you want to update these fields such as a contact’s name, it’s recommend that you use that view to export your data, update them in Excel, and import them back to Dynamics 365 for changes.

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

  • For anyone who is not on CRM Online 2015 Update 1 or Microsoft Dynamics 365 and you 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.

  • Your operating system region settings (in Windows, Control Panel > Region) and Dynamics 365 region settings (Settings ( ) > Options > Languages) should be the same. If not, refreshing dynamic data with Refresh from CRM might cause data changes.

Privacy notice

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.

Applies To

Dynamics 365 (online)

Dynamics 365 (on-premises)

Dynamics CRM Online

Dynamics CRM 2016

Dynamics CRM 2015

Dynamics CRM 2013