I am creating a series of blog posts that collectively are designed to help anyone preparing for the Microsoft Dynamics 365 Customer Engagement Core exam. (aka MB-200) In this post I will look at concepts around Excel templates.

You can see below that we have a section of the exam which covers managing the user experience. Within this section needing to know how to create Excel templates is referenced.

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.

Note:
In this post I am going to focus on how to create Excel Templates in Dynamics 365. Meaning I am not going to try to fully explain how to use Excel to create pivot tables, charts etc. However I may give a few Excel tips along the way!!

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

Having selected “Pipeline Management” you can see I get two options, one to open the template in Excel Online and another to download it.

The Excel Online option is perfect if you want to quickly view the data. Downloading lets you open the template in the full Excel App. Great if you want to make changes and then upload later.

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 download template option to begin the create process.

Having selected the download template option you’ll see a dialog similar to the one shown below. In the first instance I am going to click “Download”, this will create and open an Excel file. Notice that from this dialog you could opt to change the entity or initial view.

Once the download is completed, you can open the spreadsheet. It should look 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! (Don’t forget to click “enable editing” before you try to alter anything!)

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 excel 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! Choose your file and then click “upload”.

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 Dynamics 365.

View All Templates

You may have noticed above that my template didn’t have a very “pleasing” name. Additionally you may wish to upload new versions of a template and then maybe you’d want to delete the old version. Use the “view all my templates” option to make these types of changes.

Within this option you can delete, edit, share and download your templates.

Tip:
The share option might be really useful if one of your colleges would like to use a template you have created!

The edit option will allow you to enter a description of the template and rename it.

System Templates

So far I have only really made reference to personal templates. Personal templates can be shared but if you’d like to make a template available to the entire organization then converting it into a system template maybe a good idea.

Within the advanced settings option will find

The document templates option allows me to upload Excel or Word templates.

Having clicked upload I simply need to select either a spreadsheet or a word document template.

Once uploaded, as with personal templates I can change the name and add a desacription. I also have access to an “enable security roles” option.

By default your newly created template will be available to all security roles. But you will probably want to use the “enable security roles” option to restrict access to specific roles.

One thing you should be aware of is that document templates are not solution aware. Meaning if you create a template in a development instance moving it to production will involve manual steps.

Hopefully I have covered all of the basic concepts around Excel Templates that you’ll need to revise for your MB 200 exam. But as always I really encourage you to not rely on theory! Experiment by creating multiple templates to explore their capabilities and limitations fully. Enjoy!