Add calculated columns to D365 Excel Templates

Most of you are probably familiar with the ability Dynamics 365 has for users to create Microsoft Excel templates in Dynamics 365. Excel templates in Dynamics 365 allows users to create pivot tables, pivot charts, etc so that Dynamics 365 data is pushed into the excel template, automatically populating the configured pivot tables and charts with just one click of a button. I feel these excel templates have a lot of value for Dynamics 365 users, because most people are very familiar with excel, and this gives them a way to quickly report on data without the need to write a custom report. Another really neat feature is that you can actually utilize the excel capabilities in conjunction with the data from Dynamics 365. Huh? What do you mean by that? Well, users can add custom columns to the excel template with formula’s that will run it’s logic once the Dynamics 365 data has been pushed into the excel sheet. So if you’re a user and there are certain fields that are not tracked in Dynamics 365, this is a very handy tool!

The great thing about excel templates in Dynamics 365 is that they are not tied to a specific app. This functionality is available everywhere, whether you’re using Dynamics 365 Sales, Dynamics 365 Customer Service, Dynamics 365 Field Service, etc. Users (with access) can create personal excel templates, which are only accessible by them. But… (here’s the kicker) if they want to share their excel template with other users, they can! Sharing works exactly the same way as the sharing of (other) records in Dynamics 365. Users can share records by navigating to a view, clicking on ‘Excel Templates’ and selecting ‘View all my templates”. From here they can select an excel template and share it by clicking the share button and selecting the folks they want to share the template with. Don’t forget to click on the user(s) you’re sharing the template with, to give them the right privileges!

Creating an excel template from Dynamics 365

The easiest way to create an excel template from within Dynamics 365 is to navigate to a view and clicking the ‘Download Template’ option on the command bar.

On the pop-up window that appears you’ll see the entity picker, this is where you will pick the table that you want to use as the data source. The field below allows you to also pick a view for the table. Once the entity and view are selected, you can click ‘Edit columns’ on the bottom right which will allow you to add or remove columns to the excel template. Once all the columns are selected, click download to store a local copy of the excel template on your laptop or PC. This is the skeleton of the excel template and we’re going to build on top of the file we just exported.

Adding the calculated columns in the excel template

I am not going to discuss how you can add pivot tables and pivot charts to the excel template in this article, instead I am going to show you how we can add columns with formula’s to the template that was downloaded in the previous step. I will calculate the values by using formula’s and data in the existing cells. In my example I am using the opportunity table and I am going to add 3 columns:

• A custom ‘Rating‘ field that will calculate the opportunity rating field based on another field
• Month Name: A field that will get the month name from Dynamics’ ‘Est. close date’ field
• Weighted Revenue: A field that will calculate the weighted revenue of the opportunity based on the probability and Est. Revenue fields.

In order to add a column to the excel template, you will have to click next to a column (header) in the excel sheet and right click your mouse button. Select ‘Insert’ from the menu to add the column. NOTE: I recommend inserting the new column next to a column that has the data type you’re looking for. Once the column has been added you can select the paint brush icon to format the new columns the same as the left or right column.

Since this column is going to hold a text value, I am going to format the new column the same as the left column. I changed the name to ‘Custom Rating’ and I want the data in this column to display text based on the data in the ‘Probability’ field (which came from Dynamics 365). I also want to make sure that if there is no data in the ‘Probability’ field, the cell in the Custom Rating column stays blank. My logic is that if the probability is smaller than 31, I want the Custom Rating field to show ‘Ice‘, if the probability is between 32 and 61 I want the Custom Rating field to show ‘Cozy‘, and if the probability is between 60 and 100 I want the Custom Rating field to show ‘Fire‘. The excel formula I am using in the Custom Rating Column is shown below:

=IF(ISBLANK([@Probability]),””,IF([@Probability]<31,”Ice”,IF(AND([@Probability]>31,[@Probability]<61),”Cozy”,IF(AND([@Probability]>60,[@Probability]<101),”FIRE”,””))))

For the second calculated column I am creating another new column, this time next to the Est. Revenue field, and I am using the formatting of that field as well. The name of the column is ‘Weighted Revenue’ and I will be using a formula to calculate the values in this column. I am going to divide the data in the ‘Estimated Revenue’ field by the number in the ‘Probability’ field. These are both fields that will be populated with data from Dynamics 365 when someone uses this excel template in Dynamics 365. Since the cells will have all the data, the Weighted Revenue column will automatically be populated based on my formula. This formula is very easy, but we also want to make sure that if either of the fields (Est. Revenue and Probability) is blank, we want to show a 0 value in the ‘Weighted Revenue’ column.
The formula I used is:

=IF(OR(ISBLANK([@[Est. Revenue]]), ISBLANK([@Probability])), “0”, [@[Est. Revenue]]/[@Probability])

Now all you have to do is add some pivot tables and pivot charts and your excel template is ready to go! In order to upload your excel template into Dynamics 365, you’ll need to go to a view (I used the opportunity view) and click on the excel templates button on the command bar, just like we did when we downloaded the template. Instead of clicking on ‘Download Template’, you’re going to click on ‘Upload Template’ and select the excel file. NOTE: If you’re a system admin and you want to create excel templates for the entire organization you will need to go to admin.powerplatform.com > Select your environment > Settings > Document Templates to upload your template. Once the file is uploaded you can try it out on your data!

Conclusion

One of the added benefits of being able to add calculated columns in an excel sheet is that users can calculate the data using their own formula’s. No need to get an administrator involved to create calculated fields in Dataverse. Which brings me to the second point: this is a lot quicker! Usually it takes a while before a user request is reviewed and decided on, this gives users the power to get what they need instantly. I hope you found this article informative! Be sure to check in again next week for a new article or subscribe here to never miss another post!