Microsoft Dynamics CRM 2011 - Dynamic Worksheets in Excel Feature

Microsoft Dynamics CRM 2011 has enhanced and improved many features from its predecessor Dynamics CRM 4.0. Dynamic worksheet feature was present in CRM 4.0, but had performance issues. Using intensive data in CRM 4.0 dynamic worksheets mostly led to denial of service. Users were not able to connect to CRM 4.0 through IE and we had to perform an IIS reset.

Things have changed in Dynamics CRM 2011. In Dynamics CRM 2011 the dynamic worksheets are much more bandwidth savvy. The performance is better as compared to CRM 4.0.

I will show how to use this feature (Dynamics worksheets) in CRM 2011. For this example I will export the data from one of the custom views in Accounts. The view has 2 records. The columns in this view are Account Name, Process Name, Created By and Created on. Just make a point that these 2 account records are of Process Name type 'Movies'. Later on I will change the Process Name and refresh the excel data.

I will export the 2 records by pressing the Export to Excel button at the top on Accounts Tab Ribbon. I have selected these 2 records just to highlight (selecting records are not necessary).

Pressing the Export to Excel button will open the following dialog. There are 3 options Static, Dynamics PivotTable and Dynamic worksheet. Select the third option 'Dynamic worksheet'. We can also open the edit columns screen and add/delete any number of columns we want to.

After pressing Export button, CRM will export it to excel. It will ask you where to save this file and I can even change the file name. I have saved the excel file on my desktop. I have kept the default file name.

When we open the excel file for the first time, we won't see any data. This data is dynamic and comes through the embedded connection which is open between excel and CRM. I am using Excel 2010 and the new security model gives me a security warning. Press Enable Content to view the data.

After you press enable content the data appears. This data has the two records (accounts) as we saw in CRM view.

Don't close the excel file. I will now change the 'Process Name' values of these two records in CRM. I have changed the values (as you can see below) from Movies to Entertainment.

Go back to the excel file and open the Data tab ribbon. Press the 'Refresh from CRM' button. Since there is a connection always embedded in this excel file (back to CRM), so this button press will refresh the data.

The data will show new values or updated values. See below that the 'Process Name' has changed from Movies to Entertainment.

Dynamic worksheets are very convenient for saving the required data once and just refresh to get the updates. Managers and CEOs can save this dynamic worksheets on their desktop and refresh whenever they want to have visibility across their team or projects.

I hope this blog about CRM 2011 feature of 'Dynamic Worksheets' was informative. Please feel free to leave comments, if any.