Study guide
15.3 Microsoft Word & Excel Document Templates
Microsoft Power Platform Functional Consultant PL-200
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Dataverse Tables, Relationships & Core Columns
1.1 Dataverse Table Types, Properties & Ownership 1.2 Table Relationships & Cascading Rules 1.3 Column Types, Formats, Primary Keys & Alternate Keys 1.4 Rollup, Calculated & Formula Columns with Power Fx
2 Chapter 2: Dataverse Data Management & Governance
2.1 Dataverse Search, Quick Find & Relevance Indexing 2.2 Auditing Configuration & Log Management 2.3 Duplicate Detection Rules & Settings 2.4 Bulk Record Operations, Data Import, Export & Templates
3 Chapter 3: Dataverse Security Model & Access Control
3.1 Business Units, Ownership Models & Modern BU Matrix 3.2 Security Roles, Privileges & Depth Levels 3.3 Teams Architecture: Owner, Access, and Microsoft Entra (AAD) Group Teams 3.4 Column-Level Security & Hierarchy Security Models
4 Chapter 4: Model-Driven App Fundamentals & Information Architecture
4.1 Model-Driven App Design, Site Map Navigation & Modern App Designer 4.2 Form Types: Main, Quick Create, Quick View & Card Forms 4.3 View Configuration, System Views, Personal Views & Filters
5 Chapter 5: Advanced Model-Driven App Components & Custom Pages
5.1 Custom Pages & Modern Commanding with Power Fx 5.2 Form Component Controls & Specialized UX Elements 5.3 Embedding Canvas Apps in Model-Driven Forms 5.4 Embedding Power BI Reports, Dashboards & Tiles
6 Chapter 6: Canvas Apps Structure, Formulas & Data Operations
6.1 Canvas App Architecture, Screens, Containers & Controls 6.2 Power Fx Formulas, Delegation & Data Source Limits 6.3 Variables (Context, Global), Collections & State Management 6.4 Triggering Power Automate Cloud Flows from Canvas Apps
7 Chapter 7: Power Pages Site Design, Navigation & Content
7.1 Power Pages Design Studio & Site Structure 7.2 Power Pages Templates, Styling & Responsive Layouts 7.3 Web Pages, Navigation Menus & Content Snippets
8 Chapter 8: Power Pages Data Integration, Forms & Web Roles
8.1 Power Pages Lists, Basic Forms & Advanced Forms 8.2 Table Permissions, Web Roles & Page Access Security 8.3 External Identity Providers & Authentication Configuration
9 Chapter 9: Power Automate Cloud Flow Fundamentals & Triggers
9.1 Cloud Flow Types: Automated, Instant, Scheduled & Desktop Overview 9.2 Dataverse Connector Triggers & Operational Filters 9.3 Standard Connectors, Custom Connectors & Connection References
10 Chapter 10: Cloud Flow Actions, Expressions & Logic Controls
10.1 Logic Controls: Conditions, Switches, Apply to Each & Do Until Loops 10.2 Variables, Data Operations & Dynamic Content Manipulation 10.3 Power Automate Expression Language (WDL / Functions) 10.4 Error Handling, Configure Run After & Scope Controls
11 Chapter 11: Business Process Flows & Guided Processes
11.1 Business Process Flow Architecture, Stages, Steps & Data Steps 11.2 Branching Rules & Multi-Entity Business Process Flows 11.3 BPF Action Steps, Workflows & Cloud Flow Execution 11.4 BPF Security Roles, Order & Process Lifecycle
12 Chapter 12: Classic Workflows, Business Rules & Modern Logic Extensibility
12.1 Classic Dataverse Workflows: Real-Time vs. Background (Async) 12.2 Business Rules: Scope & Actions 12.3 Low-Code Plug-ins in Dataverse: Automated vs. Instant 12.4 Troubleshooting Automations, Run History & Analytics
13 Chapter 13: Power Platform Environment Architecture & Security
13.1 Environment Types: Default, Sandbox, Production, Trial, Developer 13.2 Environment Governance, Groups, Routing & Capacity Management 13.3 Data Loss Prevention (DLP) Policies & Connector Classifications
14 Chapter 14: Application Lifecycle Management (ALM) & Solutions
14.1 Unmanaged vs. Managed Solutions & Solution Layering Mechanics 14.2 Solution Components, Dependencies, Publishers & Prefixes 14.3 Exporting, Importing, Upgrading & Patches 14.4 Solution Checker, App Checker & ALM Best Practices
15 Chapter 15: Enterprise Interoperability & Service Integration
15.1 Exchange / Server-Side Synchronization & Email Integration 15.2 SharePoint & OneDrive Document Management Integration 15.3 Microsoft Word & Excel Document Templates
16 Chapter 16: Copilot, AI Capabilities & Exam Strategy
16.1 Copilot Studio & Generative AI across Power Platform 16.2 AI Builder Prebuilt & Custom Models in Apps and Flows 16.3 PL-200 Exam Strategy, Time Management & Question Analysis
15.3 Microsoft Word & Excel Document Templates
Key Takeaways Explain This Topic AI
Document Templates in Microsoft Dataverse provide standardized, branded document generation (Word Templates) and analytical grid reporting (Excel Templates) directly from model-driven apps.
System Templates are organization-wide, solution-aware components created and deployed by administrators/customizers; Personal Templates are user-owned, created via the command bar, and visible only to the creator unless explicitly shared.
Word Templates utilize the Word Developer Tab and the XML Mapping Pane to bind Dataverse entity schemas to Content Controls, supporting single-entity attributes, N:1 parent lookups, and 1:N repeating line-item rows via Repeating Content Controls.
Excel Templates support Static analysis (data snapshot) and Dynamic analysis (Office Data Connection queries that refresh live from Dataverse in Excel Desktop while enforcing Dataverse security trimming).
Document generation can be triggered on-demand by users from model-driven command bars or automated via Power Automate Cloud Flows using Dataverse unbound actions or the Word Online (Business) connector.
Last updated: August 2026
Microsoft Word & Excel Document Templates
Standardized business documents—such as branded sales quotes, customer invoices, onboarding packets, NDA agreements, and analytical performance workbooks—are vital to enterprise operations. Microsoft Dataverse provides built-in Document Templates for Microsoft Word and Microsoft Excel . These templates allow functional consultants to merge live Dataverse data into professionally styled office documents with a single click. On the PL-200 exam, you must master template types (System vs. Personal), the Word XML mapping architecture (including 1:N repeating tables and N:1 lookups), dynamic Excel data refresh with security trimming, and solution-based ALM deployment.
1. Document Template Types & Architecture
Dataverse organizes document templates into two distinct formats and two distinct scope levels: +-----------------------------------------------------------------------------------+
- | DOCUMENT TEMPLATES ARCHITECTURE |
- | |
- | +-------------------------------------+ +------------------------------------+ |
- | | MICROSOFT WORD TEMPLATES | | MICROSOFT EXCEL TEMPLATES | |
- | | (.docx Document Generation) | | (.xlsx Analytics & Reporting) | |
- | | - XML Schema Binding | | - Pivot Tables & Charts | |
- | | - Single Entity Fields | | - Static Worksheets (Snapshot) | |
- | | - N:1 Parent Lookups | | - Dynamic Worksheets (Live ODC) | |
- | | - 1:N Repeating Line Items | | - Dataverse Security Trimming | |
- | +-------------------------------------+ +------------------------------------+ |
- | |
- | +-----------------------------------------------------------------------------+ |
- | | TEMPLATE SCOPES | |
- | | | |
- | | SYSTEM TEMPLATES PERSONAL TEMPLATES | |
- | | - Scope: Entire Organization - Scope: Individual Creator | |
- | | - Author: System Admin / Customizer - Author: Any Authorized User | |
- | | - Management: Settings -> Templates - Management: Form / View Command Bar| |
- | | - ALM: Solution-Aware Components - ALM: Cannot be added to Solutions | |
- | +-----------------------------------------------------------------------------+ |
- +-----------------------------------------------------------------------------------+
- System Templates vs. Personal Templates
System Templates:
Available to all users across the organization who have read access to the underlying table.
Managed centrally in Advanced Settings -> Settings -> Templates -> Document Templates (or PPAC).
Solution-Aware: Can be packaged into Dataverse unmanaged and managed solutions, enabling automated deployment across Development, Test, and Production environments via Application Lifecycle Management (ALM) pipelines.
Requires administrative privileges ( prvCreateDocumentTemplate , prvWriteDocumentTemplate ) to create or publish.
Personal Templates:
Created and uploaded by individual business users directly from the command bar ( Word Templates / Excel Templates -> Upload Template ).
Visible only to the user who uploaded the template .
Can be shared with individual colleagues or teams using the standard Dataverse Share dialog.
Limitation: Personal templates cannot be added to Dataverse solutions or deployed via ALM pipelines.
- Architectural Attribute System Document Template Personal Document Template
- Audience Entire organization Owner only (until shared)
- Upload Location Settings -> Templates -> Document Templates Entity Form / View Command Bar
Creation Privilege System Administrator / System Customizer Basic User (with Create Personal Template privilege)
- Solution Packaging (ALM) Yes (Exportable in Solutions) No (Non-solution component)
- Primary Use Case Corporate quotes, invoices, compliance reports Individual custom analysis, personal letters
- 2. Authoring Microsoft Word Document Templates
Microsoft Word templates enable automated mail-merge operations where Dataverse field values dynamically populate pre-formatted Word documents. +-----------------------------------------------------------------------------+
- | WORD TEMPLATE AUTHORING WORKFLOW |
- | |
- | [1. DOWNLOAD] [2. DEVELOPER TAB] [3. XML MAPPING PANE] |
- | Select Primary Table Enable Developer Tab Open XML Mapping Pane; |
- | and 1:N / N:1 in Word Ribbon Options. select custom Dataverse |
- | relationships. XML part. |
- | | | | |
- | v v v |
- | +---------------+ +------------------+ +-----------------+ |
- | | Dataverse | | Microsoft Word | | Map Content | |
- | | Template File | =====>| Ribbon -> | ====>| Controls: | |
- | | (.docx) | | Developer Tab | | - Plain Text | |
- | +---------------+ +------------------+ | - Repeating (Table) |
- | +--------+--------+ |
- | | |
- | [5. GENERATE DOC] [4. UPLOAD TEMPLATE] | |
- | User clicks 'Word Upload .docx to Dataverse <--------+ |
- | Templates' on form. as System or Personal Template. |
- +-----------------------------------------------------------------------------+
- Step-by-Step Authoring Process
- 1. Download the Template Template Shell from Dataverse
In Dataverse, navigate to Settings -> Templates -> Document Templates (or click Word Templates -> Download Template on a record).
Select the Primary Table (e.g., Quote or Account ).
Select the Relationships to include in the XML schema:
1:N (One-to-Many): Used for repeating line items (e.g., quote_details for Quote Products, contact_customer_accounts for Account Contacts).
N:1 (Many-to-One): Used for parent lookup fields (e.g., quote_customer_accounts to pull Account details onto the Quote, or system_user_quotes to pull the Sales Rep's profile).
N:N (Many-to-Many): Used for peer relationships.
Download the generated .docx file.
2. Enable the Developer Tab in Microsoft Word
Open the downloaded .docx file in Microsoft Word Desktop.
Navigate to File -> Options -> Customize Ribbon .
Check the Developer checkbox in the right-hand column and click OK.
3. Open the XML Mapping Pane
On the Word ribbon, click Developer -> XML Mapping Pane .
In the Custom XML Part dropdown list, select the Dataverse schema part (starts with urn:microsoft-crm/document-template/... ).
Expand the primary entity tree to reveal all attributes and relationship nodes. +-----------------------------------------------------------------------------+
- | XML MAPPING PANE HIERARCHY |
- | |
- | Custom XML Part: urn:microsoft-crm/document-template/quote/1084/ |
- | └── quote |
- | ├── name (Primary Column) |
- | ├── quotenumber |
- | ├── totalamount |
- | ├── quote_customer_accounts (N:1 Lookup to Account) |
- | │ ├── name (Account Name) |
- | │ ├── telephone1 |
- | │ └── address1_line1 |
- | └── quote_details (1:N Child Products) |
- | ├── productname |
- | ├── quantity |
- | ├── priceperunit |
- | └── extendedamount |
- +-----------------------------------------------------------------------------+
- 4. Map Single Fields and Parent Lookups (N:1)
Position the cursor in the Word document where the text should appear.
In the XML Mapping Pane, locate the desired field (e.g., quotenumber or quote_customer_accounts -> name ).
Right-click the field -> select Insert Content Control -> Plain Text (or Rich Text if mapping multi-line formatted columns).
5. Map Repeating Child Line Items (1:N Relationships)
To generate a dynamic table of products, invoice lines, or contacts:
Insert a standard Word Table containing a header row and a single data row.
In the document, highlight/select the entire data row (the <tr> row).
In the XML Mapping Pane, locate the 1:N relationship entity node (e.g., quote_details ).
Right-click the quote_details entity node -> select Insert Content Control -> Repeating .
Click inside each individual cell of the repeating row, right-click the specific child field (e.g., productname , quantity , priceperunit ), and select Insert Content Control -> Plain Text .
Result: When generated, Word dynamically duplicates the table row for every related child product attached to the Quote.
Important
Repeating Content Control Placement: For 1:N repeating tables, the Repeating content control must be applied to the entire table row , not to individual cells. Inside that repeating row, individual column cells are mapped to child attributes using Plain Text controls.
3. Authoring Microsoft Excel Document Templates
Excel templates provide high-impact analytical reporting, calculation models, and summary dashboards built on top of Dataverse entity views. +-----------------------------------------------------------------------------+
- | EXCEL TEMPLATE ARCHITECTURE |
- | |
- | [STATIC WORKSHEET / TEMPLATE] [DYNAMIC WORKSHEET / TEMPLATE] |
- | - Point-in-time snapshot of data - Office Data Connection (ODC) query|
- | - Does NOT refresh from database - Live refresh via 'Data -> Refresh'|
- | - Safe for external distribution - Enforces Dataverse Security Roles |
- | - Fully editable without auth - Requires Dataverse authentication |
- | - Requires Excel Desktop client |
- +-----------------------------------------------------------------------------+
- Static vs. Dynamic Excel Templates
Static Excel Template:
Contains a snapshot of data extracted at the moment the template was downloaded.
The data table does not contain connection strings or queries to Dataverse.
Best for fixed reporting templates, formulas, and visual calculators where external data connections are prohibited.
Dynamic Excel Template (Dynamic Worksheet / Dynamic PivotTable):
Embeds an Office Data Connection (ODC) that links directly back to the Dataverse environment.
When a user opens the workbook in the Microsoft Excel Desktop application and clicks Data -> Refresh All , Excel connects to Dataverse and pulls real-time record updates.
Dataverse Security Trimming: The dynamic connection authenticates the user running the refresh. If User A has access to 50 Accounts and User B has access to 500 Accounts, running Refresh All on the identical Excel template displays only 50 rows for User A and 500 rows for User B.
Creating and Styling an Excel Template
In Dataverse, open the entity view (e.g., Active Opportunities ).
On the command bar, click Excel Templates -> Download Template .
Open the downloaded .xlsx workbook in Excel.
Design Pivot Tables, Pivot Charts, summary KPI scorecards, conditional formatting, and Excel formulas ( SUM , AVERAGE , XLOOKUP ) referencing the underlying Dataverse data table.
Save the .xlsx file.
Upload the file back to Dataverse as a System Template (for organization-wide deployment) or Personal Template .
Tip
Dynamic Refresh Boundary: Dynamic Excel workbooks cannot execute live data refreshes inside Excel Online (web browser) because Excel Online does not support external ODC data connection refreshes. Users must open the file in the Excel Desktop Application to refresh dynamic Dataverse connections.
4. Document Generation & Power Automate Integration
Dataverse templates can be consumed interactively by users or fully automated through cloud flows. +-----------------------------------------------------------------------------+
- | DOCUMENT GENERATION CONSUMPTION MODES |
- | |
- | INTERACTIVE (MANUAL): |
- | Model-Driven App Form / Grid -> Click 'Word Templates' -> Select Template |
- | -> Browser downloads populated .docx file immediately. |
- | |
- | AUTOMATED (POWER AUTOMATE): |
- | Trigger: When a Quote is marked 'Approved' |
- | -> Action 1: 'Perform an unbound action' (Action: `SetWordTemplate`) |
- | -> Action 2: Convert Word Document to PDF (OneDrive / SharePoint) |
- | -> Action 3: Send email with PDF attachment to customer via Exchange |
- +-----------------------------------------------------------------------------+
- Interactive Document Generation
Users navigate to a record form (e.g., an Opportunity or Quote), click Word Templates on the command bar, and select the template.
Dataverse retrieves the record data, resolves 1:N and N:1 relationships, merges values into the content controls, and downloads the finalized .docx document to the user's browser.
Automated Generation via Power Automate
In enterprise ALM and automated sales pipelines, document generation is frequently automated without human intervention:
SetWordTemplate Action: Power Automate flows utilize the Dataverse connector's Perform an unbound action step with the action name SetWordTemplate , passing the Target record reference and SelectedTemplateId .
Word Online (Business) Connector: Alternatively, flows use the Populate a Microsoft Word template action to inject dynamic values into content controls, followed by Convert Word Document to PDF and Send an email actions to deliver customer-facing proposals instantly.
5. Summary Comparison of Interoperability Technologies
Capability Word Templates Excel Templates SharePoint Integration
Primary Purpose Branded document generation (Quotes, Invoices, Contracts) Analytical reporting, Pivot Charts, KPI modeling Large file storage, versioning, collaboration
Output Format .docx (or converted .pdf ) .xlsx Native files (All formats)
Relationship Depth Supports 1:N (Repeating) and N:1 (Lookups) Flat view query + Pivot aggregations Hierarchical folders (Account/Contact based)
ALM Solution Support System Templates: Yes System Templates: Yes Integration Settings / Sites: Yes
Security Enforcement Dataverse privileges at generation time Live security trimming on dynamic refresh Separate SharePoint library permissions