Dynamics 365
Help & Training
Search

Export to an Excel static worksheet

When you want to present Dynamics 365 information to an individual who doesn’t have access to Microsoft Dynamics 365, or you have data that doesn’t change often, consider exporting your Dynamics 365 data to a Microsoft Office Excel static worksheet.

If you’re on CRM Online 2015 Update 1, you can export up to 100,000 records at a time. And by default, Dynamics 365 lists up to 50 records per page. Choose the Page arrows at the bottom of the list to view any additional pages.

Export data to an Excel static worksheet

You may have the option to export data to an Excel static worksheet in all record types however, in some cases the format may be legacy, or the data may not be filtered by what you see in Dynamics 365 view.

  1. Open a list of records in the Dynamics 365 web application or in Microsoft Dynamics 365 for Outlook. Choose Export to Excel, and then choose Static worksheet (Page only).

  2. By default, an exported worksheet includes the fields that are displayed in the list, using the same field order, sorting, and field widths. To make changes to the columns in an Advanced Find View, choose Edit Columns. More information:  Create, edit, or save an Advanced Find search

  3. Choose Save and then save the . xlsm 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 CenterSettings 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.


  4. Open Microsoft Office Excel and then open the . xlsm file you saved in the previous step.

By default, an exported worksheet includes the fields that are displayed in the list, using the same field order, sorting, and field widths.

Tips

  • 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 Microsoft Dynamics 365 user or have privileges to view the data in Microsoft Dynamics 365.

  • 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.

  • If you’re not on CRM Online 2015 Update 1 and you’re using the Office 365Excel 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.

  • 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 Microsoft Office Excel Help 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 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 Dynamics 365, remember that secured, calculated, and composite fields (e.g. 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 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 Dynamics 365 for 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