Add additional fields to Dynamics CRM/365 Excel Template

Roz Millar, 19 January 2022

Have you ever been asked to add an additional field or fields to an excel template that you’ve spent time creating calculations, pivot tables and charts for? You would have found that one of the limitations of excel templates is that you can’t change the uploaded document via UI. You may have found the trick for using the VBA view in excel to show the hidden sheet that contains XML. There is another option that is easier if you don’t have a lot of pivot tables to update

Step 1: Update your view to add the new column and download template

Step 2: Generate the template you want to update – in my case I want to update a template called GL Report Dec20

Step 3: With both excel files open, copy the tab with your pivot tables from the original file to the new template file

Step 4: In the new template file, check to see the name the table was given. Usually this is Table1. You will need the name for the next step

Step 5: On the pivot table page, select one of your pivot table and select Change Data Source from the Pivot Table Analyze ribbon. Once the pop up appears, replace the Table/Range with the name from Step 4. Then Click OK.

You will need to do this for all your pivot tables. Also don’t forget to update the Pivot Table Options on one of your tables to “Refresh data when opening the file”

Last Step: Save and Upload your new template. You have two options here – you can create as a new template which means if you do need to roll back the original it is still there or you can use a tool from the awesome XRMToolBox called Document Template Manager to upload to the existing D365 Template record. You can also use this tool to download templates if you need to make other types of changes i.e. add new pivot table or chart.

Hope these steps help with updating your excel templates