One of the great new features of CRM2016 is the ability to create Excel (and Word templates). In this post I will explain the process to load and create an Excel templates. (With a few 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. (Other entities are available!!!!)

Now simply select the template you want to open and if you want to open in Excel or Excel Online.

CRM2016 does come with a number of pre-built templates the “Pipeline Management” template on opportunity being one example, shown below. 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!

Now save your spreadsheet. And you are ready to turn it into a template. Back in CRM 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 CRM 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.

All is good right??? Well the first time I did this no! I was surprised to see that my pivot table contained incorrect data. I needed to manually refresh the pivot to view the latest results. Therefore, a good tip will be to set your pivot table to auto-refresh when Excel loads.

To ensure your pivot tables are always correct. Before you upload the template, in Excel right click on the pivot table, select options and in the data table select the option to “Refresh data when opening the file”. Doing this is essential to ensure the correct results are displayed as soon as the spreadsheet loads.