Share

First Day of CRMmas – Excel Templates will Brighten the Report Grinch’s 2016

For regular readers of the Hitachi Solutions blog, it will be no surprise that my number one favorite gift this CRMmas is finally here! Satya Claus at the North Pole (well…his remote office in Redmond) definitely got my letters about how good we’ve been because we now have Excel Templates in CRM 2016, which are a huge game changer for end users and managers alike when it comes to the standard out-of-the-box Dynamics CRM offering. Even the Report Grinch will smile once he sees how easy it is to use Excel Templates.

Let’s build one already!

I’ll start with an example that summarizes Account records as well as aggregate opportunity data (taking advantage of rollup fields), allowing the user to analyze a list of accounts to see who is actively working deals and which customer industry segments that activity falls into.

We’ll start out by pulling up a list of records that you will summarize. In this case I will use a listing of Accounts to summarize their distribution by Account Rep, Sector, and Geography. In the actions menu near the top of the page, choose Excel Templates->Create Excel Template.

Use the dialog to select the view you want to use for building out the template and which fields will be available in the template. Important Note: you will be able to use your template later to summarize ANY view of this record type, so for right now think of the view selection as your way to pick your “sample data” while building the template.

CRMmas2015day1-01

Clicking Download File will save the Excel file that is the foundation for the template. Open it up in Excel–go ahead and add columns in for calculated fields and KPIs. Go nuts!

For my example I’m adding:

  • Pivot charts – these pivot charts summarize industry and active deal information using CRM data and (Excel) formula results
  • Pivot table – this is a dynamic data table with account details that responds to the slicers
  • Slicers – Adding intuitive interactivity, these filter the data in all of the pivot charts and pivot tables

Tip: As a best practice, insert your charts above the data table to avoid issues with overlapping data, or better yet add your charts to another worksheet entirely (as in my example). When you upload the template, all of the sheets will be preserved and the data you export will go into the table (preserving formulas, etc.)

CRMmas2015day1-06

Once you’ve put those finishing touches on the workbook (and after a save), it’s time to get it uploaded to CRM. Again as before we use the actions menu and choose Excel Templates->Create Excel Template. Now when we’re in the dialog we can choose to upload the new template. From here, we set the name, description, and sharing settings.

CRMmas2015day1-07

Now that the template has been built and uploaded, things come to fruition by pulling up the list of records to summarize and choosing Excel Templates->Account Distribution. In CRM Online, users also have the option of displaying the final product inside of CRM using Excel Online, otherwise the file is downloadable for online and on-premise customers.

Voila!
Voila!

Did anyone say mobile?

Once you’ve uploaded your template, it’s available on your mobile device too! I’ll give an example from my Start by pulling up your list of records. Use the actions menu at the bottom to select Excel Template->Account Distribution.

CRMmas2015day1-09

That’s it, the data from the view is now populating the model we built out in the template!

You may need to confirm opening with the Excel mobile app
You may need to confirm opening with the Excel mobile app

Final thoughts

Now, excuse me while I sit next to the CRMmas tree and play with my new toy. What will day two of CRMmas bring?