Today's post is written by Argyris Anargyros, Development Principal at Sonoma Partners.
If you find yourself creating the same old Excel document using CRM data time and time again; here is a way for you to do it once, and let others get what they need from your Excel experience.
Struggling with user adoption of your Microsoft Dynamics CRM system? Deploy Control Tower to get the data you need to measure and drastically improve end user adoption.
With Dynamics CRM 2016, Microsoft has made this easier by making it possible for folks to be self-sufficient. This new Feature can be found at the top of most entities command bar.
Here you have the option to use an existing template or create your own. To create your own, select the Create Excel Template option and you will be presented with a summary of what this will do.
Click on Try it out and you will be presented with the screen below.
At this point we are given the option to select the view for that entity. If you need to modify the columns of your view, you can click on the Edit Columns link and the view editor will present itself.
Once your view is ready, click on the Download File button. This will generate and download an excel file that is connected to your CRM instance with the view of you data in Excel.
Now you can start formatting this excel file as needed. Let's say you want to add a tab with a pivot table, leaving your data in the original tab. All you need to do is click on the Insert tab in Excel and click on the Pivot table button. There you will be given the option to point to an external data source as represented in the screen shot.
Click on Choose Connection. Select the Tables tab and there you will find the table created from your view in CRM.
Select that table and click Open and then OK.
A new tab should open with the standard Pivot table setup in Excel. Select the fields for your pivot and you should end up with something like this.
Once we have our Excel file ready and formatted, we can upload it to CRM.
Follow the steps above to get back to the Create template from CRM data screen and click the Upload button.
Drag or browse to your saved Excel file and click on the Upload button.
Once the template is uploaded, you will be taken to the template’s CRM record, where you can rename it if you like, and add a description.
Now your excel template is ready for anyone to use. Users can go to the Excel Template button and select to open the template you created.
Downloading the template will bring it locally and you will be able to refresh the data by refreshing the connection at any time. If you have Office 365 you can open in Excel Online and do the same.
This was a very simple example of what you can do and CRM has an out-of-the-box template with even more.
For more details on Excel templates, you can access Microsoft’s how-to here: https://www.microsoft.com/en-us/dynamics/crm-customer-center/analyze-your-data-with-excel-templates.aspx
Check out other new features from the 2016 update here: http://www.microsoft.com/en-us/dynamics/crm-customer-center/what-s-new.aspx