5 Tips for using Excel Templates in Dynamics 365
Now that the Excel templates for Dynamics 365 have been released and in the wild for several months, it seems an appropriate time to share some tips for getting the most out of them (even if you’re just getting started).
The following items are focused on general user experience rather than advanced Excel functionality. (Crawl. Walk. Run. We’re crawling :))
1) Make sure you don’t have duplicate column names in your view
When first testing the capabilities of Excel template functionality I ran into a generic “Failed to Generate Excel” error message when downloading the baseline excel file. After some searching I discovered that the error was thrown because my view had two columns with the same name listed. With a little re-work of the view I was able to consolidate the columns listed and successfully download the file.
The easiest way to avoid this is to be intentional with which fields are added to the excel template download. I had checked the box to select “all” fields for the opportunity entity, and there were some duplicates four out of the box vs. custom fields with the same name. In a real world use case it is important to make sure that the proper field has been selected. So I recommend taking the extra time to validate specifically which fields have been added to the view (and understand why each field is being included).
2) Set PivotTables to automatically refresh data
By default, PivotTables are not set up to automatically refresh based on the new data in the CRM Data table, forcing you to right click on each pivot table and click refresh after opening the template. This hassle is completely avoidable and very simple to fix when setting up the excel template.
- Right click on the PivotTable->PivotTable Options
- Select the Data tab
- Check refresh data when opening file
BONUS TIP: Uncheck the save data option to make sure the exported CRM data is not stored with the template you’re building.
3) Use Multiple sheets in the workbook
In the examples provided by Microsoft, they have inserted their charts and tables above the data set (by adding blank rows above the CRM data table). This approach is valid and is certainly a simple way to get started, however, using additional worksheets inside of the excel workbook allows greater control over formatting and can help keep the report looking nice and clean. Using this multi sheet approach will also save you from headaches when adding in pivot tables as the number of rows and columns will be variable in many common use cases.
Doing this is simple. At the bottom of the excel window find the list of tabs (those are the sheets) and click the plus arrow to add a new one. As a best practice, remember to give good names to each sheet.
BONUS TIP: You CAN rename the tab that has the dynamic data table from CRM. Doing this doesn’t cause problems when people use the templates.
4) Choose the default Sheet a user will open to
This tip is all about the user experience (even if you’re the only person who will use it :)). If you have multiple sheets there’s probably one that makes sense to have open first. There’s a very simple and low-tech way to do this:
When you save the final excel template for uploading, make sure the “landing page” worksheet is active-at-that-exact-moment-of-the-final-clicking-of-the-save-button. This worksheet will now be the one that will be active when a user opens it in Excel (via download or in Excel Online). In this way you can guide their experience to the appropriate sheet without forcing them to click another tab.
5) For Excel Dashboards, consider hiding the “data” worksheet
Sometimes it’s important to see all the details of the records behind your charts graphs and analysis, but other times all you need is the summary. You can help further improve the user experience by hiding the worksheet with the CRM data table.
Doing so will not cause issues when using the final uploaded template. Additionally, for users who do want to see this data they could still unhide that tab if they’re feeling curious.
The utility of the Excel Templates functionality opens doors to many quick-wins in CRM 2016 (online, cloud-hosted, or internally-hosted). These tips are a few of the basics to keep in mind when getting started, but we’ve barely scratched the surface with what we can do.
Matthew’s post originally appeared on the Hitachi Solutions blog.