microsoft dynamics crm microsoft excelWelcome 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:

  1. Navigate to the desired entity and select the Excel Template button. In this example we have selected the Create Excel Template button.

excel templates create excel template

  1. Select Excel Template.
    1. Determine which entity you wish to create the template for.
    2. Select a Used Save View (this includes any personal views).
    3. “Edit Columns” enables you to add/remove table columns that will be export to Excel.
    4. Once you have configured your entity, view and columns, select Download File.

create-template-from-dynamics

  1. 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.)

excel-templates-create-excel-template-2

  1. You can now leverage Excel’s Pivot tables, Bing or 3D Mapping ability

excel-pivot-tables

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

available-templates-view

 

Use the browse functionality to select your template file and upload to Dynamics 365.

available-templates-view

The Excel Template will appear in the list.

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!

Security

Managing access to each of the templates is simple. Open the template then select “Enable Security Roles”.

enable-security-roles

Select the applicable security roles then press OK.

enable-security-roles-2
Overall, the Excel Template functionality is a very simple but effective way to create powerful reports. Check it out!