Quick Tips: Excel Templates
In a previous post we looked at how we can generate documents using word templates. Dynamics 365 also includes the ability for users to extract data into pre-built Excel templates, and in this post we’re going to take a quick look at how this works.
As with the word templates, we need to start by downloading a blank template which we can do from the entity that we want to generate the excel report from.
So let’s navigate to the opportunity entity and on the command bar select “Excel Templates” and then “Download Template”.
In the dialog, we need to confirm the entity (which should already be selected for us) and the view that we want to base our report on. So let’s select “Open Opportunities” for now and click “Download”.
After we have saved the excel template to our local hard drive, we can open it up and enable editing. As you can see, our template simply looks like a regular excel export, but as it’s a template we can start to build our report based on the data that’s been exported.
Let’s start by inserting an Estimated Revenue by Owner chart and let’s also add a pivot table to count the number of opportunities by Owner.
By default, pivot chart data is not automatically refreshed when you open a workbook. So we are going to want to change this, by right clicking on our pivot tables, selecting “PivotTable Options…”, navigating to the “Data” tab and adding a checkbox to “Refresh data when opening the file”.
Now with our edits in place, lets save our template. As with the word templates, you might want to rename your template to something a bit more descriptive at this point.
But once the template is saved, navigate back to the opportunity entity, select “Excel Templates” from the command bar again, and this time select “Upload Template”.
Let’s browse to our saved template and “Upload”.
Once uploaded, we can now generate our Excel report from the “Excel Templates” menu we used before. We can choose to either download the report or open it in Excel online.
Even though our template was generated from the “Open Opportunities” view, we can use any opportunity view and run our template against it, loading the appropriate data into the report for the view that we have selected. So here you can see I’m doing this with “Closed Opportunities” view instead and the data in our charts is updated appropriately.