Add additional fields to Dynamics CRM/365 Excel Template

Recently I was asked to put together a sales forecast Excel Template to include a few pivot tables, charts and sliders to analyse our open opportunities.  Took a couple of hours to get it right after which I presented it back to the team.  “Looks good, Luke.  You couldn’t just add in an extra field though, could you?”.  Alas, once you’ve made your initial selection of fields your only obvious option is to start again from scratch – once you have exported the initial template there is no way to add additional fields through the UI.  Due to the time taken to produce I thought surely there must be an easier and less time consuming way.  Fortunately there is.

On every Excel template there is a hidden sheet, aptly named hiddenDataSheet.  In this hidden sheet you may find the CRM query in cell A1, which you can edit.  To view this hidden sheet you’ll need to open the sheet’s VBA view (Alt+F11).  Once there you’ll see the hiddenDataSheet listed.

In the properties for this sheet the Visible property is set to 2 – xlSheetVeryHidden.  Change this property to -1 – SheetVisible and close the VBA window.  Once done you’ll see a new tab at the bottom of your Excel Template.

In cell A1 of this worksheet you will find the query.  I found it easier at this point to copy and paste it into a text editor to decipher it.

The important bit to note from this query is that each field has a format like &crmfieldname=Spreadsheet%20Column

The %20 in the above snippet represents a space in the Excel field name.  To add a new field we simply need to create the field first in our worksheet and then add it on to the end of the query.  For example if we wanted a new column for Status Reason we would add:

&statuscode=Status%20Reason

Adding fields from related entities are a lit bit more tricky as the CRM field name is proceeded with a guid for the relationship.  You will need another field already from that entity in your template in order to copy this guid to add before the new field.

Once done paste your new query back into cell A1.  Then hit Alt+F11 again and set the hiddenDataSheet Visible back to 2 – xlSheetVeryHidden, close the VBA window, save your document and reupload*.

*There is a cracking XrmToolBox plugin called Document Template Manager that will help here.