What CRM 2016 Excel Templates Mean for a Regular CRM User

When I’m out chatting with clients, prospects, and other CRM users, one of the important focus points for me is enabling the average person to do great things with Dynamics CRM. Those things that don’t require an administrator’s time or special exception. Over the course of the last handful of product updates, Microsoft has come through with many of these enhancements, and there are more on the way. We see these teased throughout Bob Stutz’s September blog post about the upcoming CRM 2016 release. It’s no mistake that the new Excel template functionality is the first bullet point in that blog post. Excel is the gold standard when it comes to analyzing your data. Even with minimal experience with it, an average user can summarize their data, create formulas, and build charts via a wizard interface. With a little time and experience, the sky becomes the limit in building these models. In his post Bob writes that the upcoming release will include:

1)     An enhanced Excel experience within CRM, including new Excel templates to automate many core tasks.  This helps our customers across the board but particularly in the sales as it allows them to do things like calculate commissions or manage a sales forecast.  They get analysis and insights directly in their sales processes, without the need to export or switch – just easy toggling to reduce countless steps and eliminate the complexity.


“I’m an average CRM user, why should I care about this?”

Perhaps the following scenario sounds familiar.

Each week, I go into CRM and pull up a view of active Opportunities for this quarter. I export that list to Excel. Next, I copy and paste those records into another Excel file where I have some formulas and graphs set up. Once I paste the data in, I make sure that my pivot tables and charts have refreshed. Now I can start working with the data.


For many of us, we go through a series of steps like this more often than we’d like to admit, spending a few minutes here and there on these rather menial tasks before we can actually get to the analysis we wanted to do. That time adds up, especially when you want to do that same analysis on several data exports. You wind up with spreadsheet sprawl with multiple files downloaded, multiple copies of your Excel file that has all of the formulas and charts, and on top of that, you may not even care about keeping any of these files once you’ve answered the question the data helps answer (and if you’re like me, you forget to delete them and inevitably wind up with a Downloads folder chock full of CRM exports that I only kind of remember what they were for). With Excel templates in CRM 2016, instead of going through the process above, you can upload a template that includes the stuff from the “other Excel file”, and by doing so you can cut out the steps of doing the 1) export, 2) copy/paste, and 3) refresh.

“That sounds interesting but what’s the catch?”

Great question! The first thing that comes to mind for me is that:

There’s a difference between doing something “one time” in Excel vs. creating a template that will consistently behave the same way in the future.


You’ve probably experienced this yourself. Take for example a simple formula where you want to compare the number of Cases for a customer between last year and this year. Assume that the Account record has two fields, “No. of Cases Last Year” and “No. of Cases This Year”. When you first build the model looking at a small data set, you use the formula of “[No. of Cases This Year]/[No. of Cases Last Year]”.

Excel table with formula and no errors
My basic Excel formula…everything looks kosher here.

Things look fine and dandy. You then upload this template to CRM and run it against all records, but you start seeing errors in the data. We’ve run into a situation where some Accounts had zero cases last year, and dividing by zero throws an error.


This example is easy enough to address, but it illustrates the larger issue of what works “one time” and the additional thought that should go into a template.

Final Thoughts

Whether you’re an individual contributor, manager, or executive within your organization, once the Excel template functionality is released I expect that it will become a part of how you get things done. When the new release is available, expect me to be writing about this topic in more detail with ideas and examples. Do you have ideas or questions about how this could be used? Please let me know in the comments.

Free Guide
On-Premises vs. Cloud CRM

A guide to help you determine the best solutions for you.