As I prepare for my Dynamics 365 certification in sales (MB2-717), I am creating blog posts based on my revision. I hope that collectively these posts may prove useful to anyone also preparing for the MB2-717 exam. In this post I will focus on 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 options more varied and flexible than earlier versions.

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

Excel Export Options

Below you can see that in my ribbon bar I have two Excel buttons. “Excel Templates” and “Export To Excel”. The Export To Excel button contains 5 options;

My five options for exporting to Excel are;

1. Open in Excel Online – opens the spreadsheet directly in Excel online.
2. Static Worksheet – exports data to a static worksheet providing a simple list of the data currently displayed in a view.
3. Static Worksheet (Page only) – exports data to a static worksheet as with the first option but only for the current page of data.
4. Dynamic Worksheet – a worksheet that once saved can be refreshed to reflect the latest version of the data.
5. 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.

Tip: If you are working we very large data sets you might need to know that the maximum n umber of rows that can be exported to Excel is 100,000

Note: open in Excel online is not available for Dynamics 365 on premise.
Excel Online

Using Excel online gives a very immersive feel to viewing data in Excel, as you can see below the spreadsheet opens directly within my Dynamics 365 interface.

Excel on line is useful when you want to interact with the data directly from the spreadsheet. Having worked with the spreadsheet you have two options available, “Return to Dynamics 365” which simply returns you to the previous view. Or “Save Changes to Dynamics 365”, this option will update Dynamics 365 with any alterations made in Excel.

The Save changes to Dynamics 365 option actually creates a data import job that will process your changes in background. Typically a process that will just take a few seconds.

It may be important to understand that a data import job is created in background. As any changes which create errors will not get imported. For example, setting a lookup field like contact to the name of a contact that doesn’t exist. In settings, data management you can view the status of import. Below you can see that I have two import jobs created by editing data within Excel Online. Notice that one of the import jobs contains an error.

Tip:
Opening the import job will enable access to a “Failures” option that will detail the reason for any errors.

Static Worksheet & Static Worksheet (Page Only)

The static work sheet options are useful when you want to quickly export data and store it off line. Or maybe you need to create a report that can be distributed to people who do not have access to Dynamics 365. When you select the option you simply see a simple dialog that indicates a spreadsheet is being created.

After the export to a static spreadsheet is complete you can simply open the file created.

When you open the spreadsheet you may see a security warning. This is simply a warning as the data have been downloaded from the internet. Just click “Enable Editing”.

Import Static 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 Dynamics 365 from Excel. This essentially does the as saving changes from an Excel Online spreadsheet, the process is just not quite as seamless.

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.

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.

Tip: You can actually select the columns an then use the unhide option to reveal this hidden data. What you would see is the records GUID, a check some and data modified. None of this fields should be changed.

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 Dynamics using the data import option. You can see below that I have selected import and the chosen the file I want to import.

Having clicked next, I simply click submit.

Just line an Excel Online import a data import job will be created and your data will be available in a few seconds.

Dynamic Worksheets and Pivot Tables

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.

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 an advanced find. It gives you the ability to tailor the columns to be returned and sort order of data before exporting to Excel.

When I open a dynamic worksheet in Excel it will look very similar to a static worksheet! I will however get an additional security warning as I need to enable the dynamic content.

Dynamic and static worksheets may look similar but they are quite different! Statics worksheets (as the name suggests) simple copies that data into Excel. A Dynamics worksheet or pivot table actually gets Excel to query the data. Meaning next time your spreadsheet is opened the data can be re-queried and the latest results viewed.

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. Then when the spreadsheet opens you will be able to pivot by these columns.

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.

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

Opening an Excel Template

First off let’s look at how to open an Excel template. I’ll cover creating and maintaining templates in a second! First find the Excel template option in the ribbon, below you can see its location on opportunities. Now simply select the template you want to open and if you want to open in Excel or Excel Online. An out of the box templates option exists for opportunities called “Pipeline Management”.

Dynamics 365 does come with a number of pre-built templates the “Pipeline Management” template on opportunity being one example, shown below opened in Excel Online. This is great to get you going but you’ll quickly want to create your own templates. So I’ll discuss that next.

Creating a template

Use the Create Excel template option to begin the create process.

Having selected the create excel template you will see a dialog similar to the one shown below. Notice you can select either a Word or Excel template. I’m looking at Excel templates (this time) so that option is selected.

In the first instance I am going to click “Download File”, this will create and open an Excel file. Notice that from this dialog you could opt to change the entity, initial view and edit the columns. (Very much like the standard Excel export.) In fact, clicking download file in this manner will behave exactly like a “normal” Excel export.

Once the download is completed, you should see a spreadsheet similar to the one below. You can now edit this to include whatever formatting you need. Typically, this formatting will include adding charts and pivot tables!

Tip: You could add your pivot tables directly above (or below) the data grid, just insert a few rows. However, in my opinion, this might not always be a good idea. If the length of your pivot tables will differ then you’ll need a varying amount of space above the grid. A cleaner approach might be to add a second worksheet. Maybe calling it “Dashboard” or some such thing. In my test I decided to create a “dashboard” that showed details of opportunities.

One tip is, when naming the ranges for the pivot tables make sure you select the whole range of the table returned from CRM. Meaning the pivot table is referencing the table name not the column / row range. This is because the amount of data returned will differ each time your template is used.

I’m not going to cover how to use Excel here. I’ll assume you know that already! I ended up with a test spreadsheet which looked like the one below. This is obviously a test, I’m sure you can create a better looking and more meaningful spreadsheet!

One useful tip is that any pivot tables you create will probably need to show the latest data when you load your template. (Removing the need for users of your template to have to manually refresh them. To do this right click on the pivot table, select pivot table options. Then in the data tab of the options ensure the option to “refresh data when opening the file” is selected.

Now save your spreadsheet. And you are ready to turn it into a template. Back in Dynamics 365 select the create template button again but this time opt to upload a file.

Tip:
When you save your spreadsheet give it a meaningful name! As the name of the template in Dynamics 365 will come from the name of the spreadsheet.

This time you’ll see a dialog similar to the one shown below. Do as instructed! Either browse for a file or drag one in! Essentially browse for the spreadsheet you just created. Then click “upload”.

Now you will be presented with a page similar to the one below. The name of the template will be the name of your spreadsheet, which you could edit here it needed!

You are now ready to test your template. Return to opportunities and you should see your newly created template. If you don’t see it you may need to refresh your browser session! Notice below that a personal Excel template has been created. And I have an option to download in Excel or Excel Online.

Below you can see my newly created spreadsheet shown in Excel online directly from CRM.

I hope I have covered most (or all) of the options for exporting to Excel, you will need to be aware of all of these for your Mb2-717 exam. Additionally you will need to appreciate the differences and understand quick option fits what circumstance. As always I suggest you get plenty of hands on time and experiment with all the various approaches. Enjoy.