Create and manage Excel templates

Microsoft Excel provides powerful ways to analyze and present your Dynamics 365 Sales data. With Excel templates, you can easily create and share your customized analysis with others in your organization.

You can use Excel templates for:

  • Sales forecasting

  • Pipeline management

  • Leads scoring

  • Territory planning

  • And much more…

License and role requirements

   
License Dynamics 365 Sales Premium, Dynamics 365 Sales Enterprise, or Dynamics 365 Sales Professional
More information: Dynamics 365 Sales pricing
Security roles System Administrator, Sales Manager, Sales Professional Manager, and above
See Predefined security roles for Sales

Create an Excel template

Templates display information from the view defined for a record type (entity). Depending on the Dynamics 365 Sales license you have, select one of the following tabs for specific information:

  1. From the Sales Hub app, select the Settings icon Settings icon., and then Advanced settings.

  2. Follow the instructions in Analyze and share your data with Excel templates to create an excel template.

Best practices and considerations for using Excel templates

Here are some things you need to be aware of to create and make the best use of Excel templates in Dynamics 365 Sales.

  • Test your Excel templates.

    Excel has lots of features. It’s a good idea to test your customizations to see that all Excel features work as expected in your templates.

  • Data in templates and privacy concerns.

    By default, pivot chart data is not updated when a spreadsheet is opened. This can create a security issue if certain pivot chart data should not be seen by users with insufficient permissions.

    Consider the following scenario:

    • A Dynamics 365 Sales administrator creates a template where the view contains sensitive data in a pivot chart which is uploaded into Dynamics 365 Sales.

    • A salesperson who should not have access to the sensitive data in the pivot charts uses the template to create an Excel file to do data analysis.

    The outcome of this scenario is that the salesperson might be able to see the pivot chart data as it is uploaded by the Dynamics 365 Sales administrator, including access to views for which the salesperson does not have permissions.

    In addition, iOS does not support updating pivot data and pivot charts when using the Microsoft Excel app on iOS devices.

    Important

    Sensitive data should not be included in pivot tables and pivot charts.

  • Set pivot chart data to automatically refresh

    By default, pivot chart data does not automatically refresh when you open the spreadsheet. Other types of charts do update automatically.

    In Excel, right-click the pivot chart, and then select PivotChart Options > Refresh data when opening the file.

    Define PivotTable options.

  • Placing new data

    If you want to add content to the Excel template, place your data above or to the right of the existing data. A second option is to place your new content on a second sheet.

  • Excel templates with images can cause an error

    If you attempt to view Dynamics 365 Sales data with an Excel template that has an image saved in it, you might see the following error: “An error occurred while attempting to save your workbook. As a result, the workbook was not saved.” Try removing the image from the template and reloading it into Dynamics 365 Sales.

  • Excel templates and Office Mobile app in Windows 8.1

    Excel templates will not open in Windows 8.1 devices with the Office Mobile app. You’ll get the following error message: “We’ve recovered as much of your document as we could, but you can’t edit it. Try to open and repair the document on your PC to fix the problem.”

    This is a known issue. Use table column names and range names in formulas. When you create Excel formulas, don’t use column titles or cell numbers. Instead, use the table column names, and define names for cells or cell ranges.


Can't find the options in your app?

There are two possibilities:

  • You don't have the necessary license or role to perform the steps.

  • Your organization might be using a custom app and hence the site map and UI will be different. Check with your administrator for the exact steps. The steps described in this documentation are specific to the out-of-the-box Sales Hub or Sales Professional app.

See also

Create and manage Word templates in Dynamics 365 Sales