Tip #983: Why Excel templates are better than dynamic spreadsheets

With the deprecation of the Outlook Client, some users are wondering about dynamic spreadsheets. If you are moving to the cloud or have IFD on premises, you need to run Dynamics 365 for Outlook (the Outlook client, not the new app) to use dynamic spreadsheets.

I’ve had this conversation with multiple clients, and while they have been initially disappointed that they won’t be able to use dynamic spreadsheets after they go to the new Outlook App, once they get their hands on Excel templates, they aren’t so sad anymore. Some say they are actually a better solution.

The big value of Dynamic spreadsheets is that you can export data to Excel from CRM with an embedded query. Every time you open the spreadsheet, you can refresh the data from CRM, without having to log in to CRM.

That can be a nice timesaver; however, the thing about people who are Excel junkies is they typically don’t want to just get the data in Excel–they want to do something with it. Add formulas, pivot tables, charts, calculations. And while you could add these items to a dynamic spreadsheet on a separate tab, it was never a truly supported feature, and you definitely couldn’t change the table in a supported manner. If you got your dynamic spreadsheet extended with charts and pivot tables and then you needed the same spreadsheet with a different query, you would need to re-export a second copy of the spreadsheet from a different view.

Enter Excel templates.

First thing to note, Excel templates are NOT dynamic. They don’t refresh each time you open them.

But what they do is give you the ability to export data from Dynamics 365, modify the export table (add columns, calculations, totals, etc) and pivot tables, charts, and anything else your little Excel loving heart desires. Then you can upload that template back into Dynamics 365 and run it from any view of the selected entity in Dynamics 365. Even if the view doesn’t include all of the fields in your template, it will work with the view.

So as a trade-off for giving up the dynamic query, you get the ability to more extensively modify the spreadsheet, including the table, you can run it with any view from the entity, and (IMO) best of all, you can run it from mobile.

Fair trade I would say.

One thought on “Tip #983: Why Excel templates are better than dynamic spreadsheets

  1. Rob says:

    I’m a data dork through and through, however I politely disagreed that this is a fair trade-off. In an environment where CRM security controls are set by IT staff that do not understand the needs of the business unit staff, the full set of CRM features are not accessible. (Don’t get me started on the level of dysfunction this creates among all from executive leadership down to support staff.) This creates an environment where Excel (or MSAccess) continues to be needed in order to create dashboards and reports that can not be obtained in CRM.

Leave a Reply

Your email address will not be published. Required fields are marked *