Many users have data that gets exported to Excel and then manipulated into charts, formulas, or tables to display the details needed. In CRM 2016 you now have the ability to save your Excel document as a template so that when you pull the data in the future you can see the data displayed without recreating the formulas/charts etc every time. This article covers some basic steps, but since Excel reports can be highly customized we can't address everything in a single article. Microsoft has some excellent documentation on additional details and setting security roles in CRM for who can create, view, and edit templates which you can access by clicking here.
Navigate to Settings or a list of records.
From the Settings page. Go to Settings > Templates > Document Templates > New (New or Add button). You must have sufficient permissions to access to the Settings page, such as System Administrator or System Customizer. This method will result in a template available to all system users.
From a list of records. For example, go to Sales > Opportunities > My Open Opportunities. On the menu bar, click Excel Templates > Create Excel Template. This will result in a personal template only available to the creator unless it is shared with other users.
Click Excel Template
Select the Entity you want to pull your data from and select a Saved View
The template starts from an advanced find view just like the traditional export to Excel functionality. We suggest setting up your view with all your columns in advance but if you do not do so you have the opportunity to add columns as part of the template creation process.
Click Edit Columns (if you have not previously included all desired data in your Advanced Find View)
Click Download File to Create the template file.
Manipulate your Data
Set up your Excel Report as you normally would. Though there may be some minor modification depending on the complexity of your formulas. If the formulas do not work when you test your template you can search on-line for specifics. Microsoft has some suggested Best Practices though specific issues can likely be addressed by an online search as well.
A Few Tips -- For formulas you will want to name your columns, fields, and ranges and do the calculations on a new sheet. It takes more time to set up but the template will not effectively account for new data otherwise.
Place Charts above the original data or on their own sheet.
When formatting remember that data can change in a future export of the template so leave room at the bottom for new records. Add charts or other details above the data set not below.
Repeat Step One and Two and this time click Upload instead of Download File
Test your Template from the List View
