MB2-713 Certification (Microsoft Dynamics CRM 2016 Sales) – Sales Analysis (Excel) …. in this post I am going to continue my series on preparing for the MB2-713 exam by looking at exporting data to Excel.

You have been able to simply export data to Excel for some time but CRM 2016 introduced a new additional feature of Excel and Word templates, making the CRM 2016 options more varied and flexible than earlier versions of CRM.

NOTE:
Whenever exporting data from CRM it is imported to recognize that the CRM security model still applies. Users can only work with information shared with them within their CRM role.

Excel Export Options

Below you can see that in my ribbon bar I have two Excel options. Templates and export. And that the export contains four options. (FYI: Screen shot is from an on premise version of CRM.)

My four options for exporting to Excel are;

1. Static Worksheet – exports data to a static worksheet providing a simple list of the data currently displayed in a view.
2. Static Worksheet (Page only) – exports data to a static worksheet as with the first option but only for the current page of data.
3. Dynamic Worksheet – a worksheet that once saved can be refreshed to reflect the latest version of the data.
4. Dynamic PivotTable – very similar to a dynamic worksheet but creates an Excel pivot table instead of a data list.

Tip:
If you only have one page of page of data the “Static Worksheet” option doesn’t display. The number of rows displayed on a page can be controlled in user options and can be set to 25, 50, 100, 200 or 250.

Note: You will need to be aware that for the Dynamic Worksheet and Dynamic PivotTable options to work you must have the Microsoft Dynamics CRM add-in for Outlook installed.

Note:
With CRM online a further Excel option export exists! To open and even edit the document directly in Excel Online. This option is not included in CRM on premise. Also an additional benefit of Excel Online is that it not only works with CRM but also with mobile devices or tablets and phone applications.

Import Data Back From Excel

Notice below on the static worksheet export that that tooltip points to a further piece of functionality, as it is possible to import data back into CRM from Excel.

When you look at an exported worksheet you will notice that the spreadsheet starts at column D! This is because columns A-C hold some hidden information allowing the import of data.

It is possible to edit existing records and even add new ones in the static worksheets. The newly created spreadsheet can then be imported back into CRM using the data import option.

If a spreadsheet is imported back into CRM using the import option or if the data is changes in Excel Online an import job is created. The progress of these jobs can be seen under settings / data management Imports.

Here you will be able to see the status of import jobs can how many records were imported. Plus, importantly, how many records failed. It is then possible to open those failures and view the reasons.

Dynamic Worksheets and Pivot Tables

When you opt to export data to a Dynamic Excel worksheet the following dialog is displayed. This screen works exactly the same as the edit columns option available whilst creating a advanced find. It gives you the ability to tailor the columns to be returned and sort order of data before exporting to Excel.

Tip:
When exporting to a Dynamic Excel sheet you may see a SECURITY WARNING! This is because the resulting Excel sheet will contain a data connection joining it directly to your CRM data. Click “Enable Content” if you see this message.

Opting to export data to an Excel pivot table also triggers a dialog, this time to allow you to confirm the columns to be included before continuing.

Excel Templates

An Excel template allows greater formatting options than a standard Excel export. A template can be created and imported, data can then be repeatedly presented from differing views using the same layout.

This video shows how to create an Excel template;

Templates can be held as personal templates or uploaded as system templates. Follow THIS link for details on how to manage templates.

I hope this post has given a good explanation of the features connected with Excel that you should be aware of for the MB2-713 certification. In my next post I will continue this theme by looking at Word templates.
J