Welcome to Part 2 of my series, An Effective Partnership between Microsoft Dynamics CRM and Microsoft Excel. This blog will cover one of the highlights of the partnership: Excel Templates.
The Key Benefits
- Recreate your company’s Excel reports within Dynamics CRM to leverage CRM data.
- Utilise Excel’s familiar analytical functionality (Pivot table and maps) with small to medium size datasets.
- Ability to produce complex reports without the need of a developer.
How to Create an Excel Template
Creating an Excel Template is a simple process. In a nutshell, you only need to:
- Select the target entity and configure the columns
- Download the data into Excel
- Configure your template by inserting charts and creating Pivot tables, etc
- Upload to Dynamics CRM and configure access
Detailed steps are as follows:
- Navigate to the desired entity and select the Excel Template button. In this example we have selected the Create Excel Template button.
- Select Excel Template.
- Determine which entity you wish to create the template for.
- Select a Used Save View (this includes any personal views).
- “Edit Columns” enables you to add/remove table columns that will be export to Excel.
- Once you have configured your entity, view and columns, select Download File.
- Once the template has downloaded, select an open file to view the data exported from Dynamics 365. (I’m using the sample data from a Microsoft Dynamics 365 30-day trial.)
- You can now leverage Excel’s Pivot tables, Bing or 3D Mapping ability
- Once you have finished configuring your template, save the file then navigate to Dynamics 365 Settings, select Templates then Document Templates. Once the template list displays, select Upload Template.
Use the browse functionality to select your template file and upload to Dynamics 365.
The Excel Template will appear in the list.
Dynamics 365 includes four out-of-the-box Excel Templates Opportunity: two for the Case and one for Campaigns, so be sure to check these out!
Managing access to each of the templates is simple. Open the template then select “Enable Security Roles”.
Select the applicable security roles then press OK.