POWEROBJECTS

718 Washington Ave. N. Suite #101
Minneapolis, MN 55401
View map and all Locations

Send us a message

Do you want unleash the power of Microsoft Excel to analyze your CRM data? Exporting to a dynamic pivot table can help. Excel provides sophisticated data analytics capabilities through pivot tables. Use them to quickly summarize data across a wide number of entities and fields. Excel 2010 onwards provides additional filtering capabilities using slicers.

crm-101

 

Let us step through how to extract data from CRM into a pivot table. In this example, we will focus on sales data. However, Microsoft Dynamics CRM provides out of the box functionality to export data from any view.

 

Step-by-Step

 

  1. Navigate to a view from which you would like to extract data. In this example, we navigate to Sales -> Opportunities.

 

Exporting Dynamic Pivot Tables out of Dynamics CRM

Exporting Dynamic Pivot Tables out of Dynamics CRM

 2.  In the command bar, click the “EXPORT TO EXCEL” text in the command bar to launch the export wizard.

Exporting Dynamic Pivot Tables out of Dynamics CRM

 

3.  Select the “Dynamic PivotTable” radio button to create an export that will update directly from CRM when you refresh the Excel worksheet. Click “Next >”.

 

Exporting Dynamic Pivot Tables out of Dynamics CRM

4. Select the record fields that you would like to include in the export and then click the “Export” button. CRM will save the export as an XML file.

 

Exporting Dynamic Pivot Tables out of Dynamics CRM

 

Tip A – select all fields/columns by clicking the checkbox in the upper left corner next to "Display Name" to maximize the analysis options within the Excel pivot table.

 

5. Navigate to the file within windows explorer. Open the file using Microsoft Excel.

Tip B – within the Internet Explorer browser, you can navigate to the containing folder by clicking the "Open folder" button.

 

Exporting Dynamic Pivot Tables out of Dynamics CRM

Tip C – within the Chrome browser, you can navigate to containing folder by clicking the inverted chevron and selecting "Show in folder".

Exporting Dynamic Pivot Tables out of Dynamics CRM

 

Tip D – top open the file without Excel being open, right click on the file and select "Open with Excel".

 

Exporting Dynamic Pivot Tables out of Dynamics CRM

 

Tip E – the pivot table "refresh" button is under the data menu.

 

Exporting Dynamic Pivot Tables out of Dynamics CRM

Note: We highly recommend that you have the CRM Outlook Client installed and properly configured. While there are workarounds including leveraging OData and ODBC, we do not recommend going down this path.

Congratulations! You now have a refreshable pivot table of your target CRM data. You can manipulate it as you would any other Excel pivot table. Make sure to check out our blog for even more Dynamics CRM tutorials!

Happy CRM’ing!

 

 

Avatar for Joe D365

Joe D365

Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.