Study guide
14.3 Edit in Excel, OneDrive & Data Analysis Mode
Microsoft Dynamics 365 Business Central Functional Consultant MB-800
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Company Creation & Data Migration
1.1 Creating Companies & Assisted Setup Wizards 1.2 Configuration Packages & RapidStart Worksheets 1.3 Opening Balances & Data Migration Strategies
2 Chapter 2: Security, Profiles & Auditing
2.1 Users, User Setup & Role Center Profiles 2.2 Permission Sets & Security Group Management 2.3 Security Filters, Change Log & Security Auditing
3 Chapter 3: Core System Configuration, Number Series & Copilot
3.1 Company Information, Number Series & Job Queues 3.2 Report Layout Management & Copilot AI Capabilities
4 Chapter 4: Dimensions Architecture & Governance
4.1 Dimension Concepts & Dimension Values 4.2 Global Dimensions vs Shortcut Dimensions 4.3 Default Dimensions, Combinations & Priorities
5 Chapter 5: Workflows & Approvals
5.1 Native Workflow Templates & Approval Hierarchy 5.2 Workflow User Groups & Notification Configuration
6 Chapter 6: Financial Setup, Periods & Currencies
6.1 General Ledger Setup & Accounting Periods 6.2 Payment Terms, Payment Methods & Deferrals 6.3 Multi-Currency Setup & Exchange Rates
7 Chapter 7: Chart of Accounts, Categories & Allocations
7.1 Chart of Accounts Structure & G/L Account Cards 7.2 Account Categories, Subcategories & Financial Reporting 7.3 General Ledger Allocation Accounts & Sharing
8 Chapter 8: Posting Groups & Posting Setup
8.1 Specific Posting Groups: Customer, Vendor, Bank & Inventory 8.2 General Posting Groups & General Posting Setup Matrix 8.3 Inventory Posting Setup & Alternative Posting Groups
9 Chapter 9: General Journals & Recurring Methods
9.1 Bank Account Cards & Posting Setup 9.2 General Journal Templates, Batches & Number Series 9.3 Recurring General Journals & Allocation Methods
10 Chapter 10: Accounts Payable & Accounts Receivable Setup
10.1 Purchases & Payables Setup & Vendor Master 10.2 Sales & Receivables Setup & Customer Master 10.3 Payment Journals, Cash Receipts & Payment Registration Setup
11 Chapter 11: Fixed Assets Setup & Depreciation Books
11.1 Fixed Asset Setup, Classes & Depreciation Books 11.2 Fixed Asset Cards, Main/Component Assets & Depreciation Methods
12 Chapter 12: Inventory Setup, Costing & Locations
12.1 Inventory Setup, Item Types, Categories & Variants 12.2 Costing Methods (FIFO, LIFO, Average, Standard) & Ledger Architecture 12.3 Locations, Transfer Routes & Stockkeeping Units (SKUs)
13 Chapter 13: Logistics Master Data, Pricing & Discounts
13.1 Shipping Agents, Locations & Vendor Lead Times 13.2 Sales Prices, Purchase Prices & Price Lists 13.3 Line Discounts, Invoice Discounts & Thresholds
14 Chapter 14: User Interface, Views, Inspection & Excel
14.1 Personalization, Profile Customization & Designer 14.2 Searching, Advanced Filters, Saved Views & Page Inspection 14.3 Edit in Excel, OneDrive & Data Analysis Mode
15 Chapter 15: Purchase & Sales Order Execution
15.1 Purchase Orders, Receiving, Over-Receipt & Undo 15.2 Purchase Invoicing, Recurring Lines & Blanket Orders 15.3 Sales Quotes, Availability Checks & Order Conversion 15.4 Sales Shipping, Invoicing, Undo & Blanket Orders
16 Chapter 16: Invoices, Credit Memos & Document Corrections
16.1 Direct Invoices, Credit Memos & Document Reopening 16.2 Correct, Cancel & Corrective Credit Memo Procedures 16.3 Combined Shipments/Receipts & Prepayment Workflows
17 Chapter 17: Cash Management, Bank Reconciliation & Ledger Applications
17.1 Processing Payment Journals & Cash Receipt Registrations 17.2 Applying, Unapplying & Reversing Customer/Vendor Entries 17.3 Bank Account Reconciliation & Statement Matching Rules
18 Chapter 18: Operational Adjustments, Inventory & Asset Accounting
18.1 Exchange Rate Adjustments & Dimension Corrections 18.2 Inventory Adjustments, Transfers & Physical Counting 18.3 Fixed Asset Acquisitions, Depreciation Runs & Disposals
14.3 Edit in Excel, OneDrive & Data Analysis Mode
Key Takeaways Explain This Topic AI
Business Central offers two distinct Excel integrations: 'Open in Excel' (a static, read-only .xlsx export of the active view) and 'Edit in Excel' (a live, bidirectional read-write integration using the Microsoft Dynamics Office Add-in).
Edit in Excel communicates via OData V4 web services; publishing updates from Excel triggers full Business Central table validation logic (OnValidate) and enforces user security permissions, rolling back any invalid rows.
Integration with Microsoft OneDrive for Business allows users to open, view, and co-author report outputs and Excel files directly in Excel for the Web without downloading files locally.
Data Analysis Mode toggles list pages into interactive operational analytical surfaces directly in the browser, supporting multiple analysis tabs, hierarchical row groupings, pivoting, and aggregations (Sum, Count, Min, Max, Average).
Data Analysis Mode is strictly an operational reporting tool that does not allow data entry or record modifications, ensuring that underlying transactional and master data integrity is maintained.
Last updated: September 2026
14.3 Edit in Excel, OneDrive & Data Analysis Mode
Quick Summary: Dynamics 365 Business Central provides deep productivity integrations with the Microsoft 365 ecosystem. Users can perform read-only spreadsheet analysis via Open in Excel ( Ctrl+F10 ) or execute bidirectional, bulk data updates using Edit in Excel powered by the Microsoft Dynamics Office Add-in over OData V4 web services. For cloud-native collaboration, OneDrive for Business enables in-browser spreadsheet viewing and co-authoring without local downloads. Directly inside the web client, Data Analysis Mode transforms standard list pages into interactive analytical pivot tables with hierarchical row groupings, column pivots, and multi-metric aggregations.
Excel Integration Architecture: Open in Excel vs. Edit in Excel
Spreadsheets remain the universal language of business finance. Business Central provides two fundamentally distinct mechanisms for interacting with Microsoft Excel, each designed for specific operational scenarios. Excel Integration Paths:
- [List Page View] ──► Action: Open in Excel (Ctrl+F10)
- └──► Generates static .xlsx file
- └──► Snapshot of active columns and applied filters
- └──► READ-ONLY (No connection back to ERP)
- [List Page View] ──► Action: Edit in Excel
- └──► Generates dynamic .xlsx linked to OData Web Service
- └──► Powered by Microsoft Dynamics Office Add-in
- └──► BIDIRECTIONAL (Edit, Insert, Delete, Publish)
- Detailed Architectural Comparison
Capability / Property Open in Excel Edit in Excel
Primary Objective Ad-hoc static reporting, printing, formatting Bulk master data updates, initial data migration
- Data Direction One-way export (ERP -> Excel) Two-way bidirectional (ERP <-> Excel)
- Underlying Protocol Direct XML/OpenXML generation OData V4 Web Service (REST API)
- Add-in Requirement None (Works in any spreadsheet viewer) Microsoft Dynamics Office Add-in required
- Authentication None after export Microsoft Entra ID (Azure AD) Single Sign-On
- Business Logic Execution None Full table trigger execution ( OnValidate )
- Row Insertion / Deletion Allowed locally (no impact on BC) Inserts new records or deletes records in BC
Active Filter Handling Exports only records matching active filters Queries web service; can refresh to full dataset
The Mechanics of Edit in Excel
When a user selects Page -> Edit in Excel on a list page (e.g., Customers, Vendors, Items, General Journal lines), Business Central constructs and downloads an .xlsx workbook pre-configured with data connection metadata.
The Microsoft Dynamics Office Add-in
Upon opening the downloaded workbook in Microsoft Excel, the Microsoft Dynamics Office Add-in task pane initializes on the right side of the screen:
Authentication: The add-in authenticates the user against Microsoft Entra ID, ensuring the session adheres to conditional access, MFA, and licensing policies.
Web Service Mapping: The add-in queries the underlying Business Central OData V4 Web Service (listed on the Web Services page, Page 810) linked to the source page or table.
Data Population: Data rows are populated into an Excel structured table matching the published field schema.
Data Modification, Insertion, and Deletion
Inside Excel, users can manipulate records with spreadsheet efficiency:
Edit Existing Data: Modify values in editable columns (e.g., changing Payment Terms Codes, Credit Limits, or Posting Group assignments across 500 customers).
Insert New Records: Add new rows to the bottom of the structured table. Mandatory primary keys (such as No. ) must be provided unless the underlying table supports automated number series assignment via API.
Delete Records: Right-click a table row and select Delete -> Table Rows to flag records for removal in Business Central.
The Publishing Pipeline & Validation Enforcement
Clicking Publish in the Excel Add-in task pane initiates a structured data synchronization pipeline: Edit in Excel Publishing & Validation Pipeline:
- Excel Workbook (User clicks Publish)
- │
- ▼
- [OData V4 Web Service Call (HTTPS / REST)]
- │
- ▼
- [Business Central Data Validation Layer]
├── 1. Security Check: Does user have Modify/Insert/Delete permissions?
- ├── 2. Table Validation: Executes OnValidate triggers for each field
- └── 3. Relational Integrity: Checks foreign keys & posting setup
- │
- ├──► Validation Passed ──► Record Committed to Database (Green Checkmark)
- └──► Validation Failed ──► Row Rolled Back & Highlighted Red in Add-in
- Error Handling and Technical Constraints
Trigger Execution ( OnValidate ): Every field published executes the identical AL validation code that runs when typing directly into the web client. For example, changing a Customer Posting Group triggers validation of open ledger entries; if invalid, the update is rejected.
Row-Level Errors: If row 45 contains an invalid Payment Terms Code, the Add-in flags row 45 with a red indicator and outputs the exact Business Central error message in the task pane. Rows that passed validation are successfully committed.
Security Enforcement: The add-in strictly respects the user's assigned Permission Sets . If a clerk lacks Modify permission on Table 18 ( Customer ), clicking Publish produces an unauthorized error.
Read-Only Fields: FlowFields (e.g., Balance ($) ), calculated fields, and system audit fields cannot be modified via Edit in Excel.
Microsoft 365 & OneDrive Integration
Business Central integrates natively with Microsoft OneDrive for Business , establishing a modern cloud file-handling framework that avoids cluttering user local drives.
Core Cloud Capabilities
Open in OneDrive: When viewing report layouts, financial statements, or exported Excel workbooks, users can select Open in OneDrive . Business Central stores the file in a dedicated Business Central folder on the user's OneDrive and automatically launches Excel for the Web or Word for the Web in a new browser tab. Multiple team members can immediately co-author the document in real time.
Share to Microsoft Teams: Users can click the Share action on any document card (such as a Sales Order or Purchase Invoice) and select Share to Teams . This dispatches an interactive card preview directly into a Teams channel or private chat, allowing colleagues to review line items, totals, and approval statuses without leaving Teams.
Document Attachments Framework: The Attachments FactBox on master and document records allows users to drag and drop PDFs, images, and spreadsheets directly into Business Central. These files can be stored internally or synchronized with SharePoint/OneDrive document libraries.
Data Analysis Mode: Interactive In-Client Operational Reporting
Data Analysis Mode transforms standard list pages into multidimensional analytical workspaces directly inside the Business Central web client. It eliminates the need to export data to Excel or configure external Power BI datasets for everyday operational slicing and dicing. Data Analysis Mode Layout on List Pages:
- [Analyze Toggle: ON] ─────────────────────────────────────────────────────────┐
- │ Analysis Tabs: [Sales by Customer] [+] [Items by Location] │
- ├──────────────────────────────────────┬──────────────────────────────────────┤
- │ ANALYTICAL GRID │ CONFIGURATION PALETTE │
- │ │ │
- │ ▼ Customer Posting Group: DOMESTIC │ ▼ Pivot Mode [ON / OFF] │
- │ ► Cust-10000 | Invoiced: $45,000 │ │
- │ ► Cust-10200 | Invoiced: $12,500 │ ▼ Row Groups │
- │ *Subtotal DOMESTIC: $57,500* │ [Customer Posting Group] │
- │ │ [Customer No.] │
- │ ▼ Customer Posting Group: EU │ │
- │ ► Cust-20000 | Invoiced: $82,000 │ ▼ Values (Aggregations) │
- │ *Subtotal EU: $82,000* │ [Sales (LCY) -> Sum] │
- │ │ [Quantity -> Sum] │
- │ **GRAND TOTAL: $139,500** │ │
- │ │ ▼ Column Labels (when Pivot is ON) │
- │ │ [Posting Year] │
- └──────────────────────────────────────┴──────────────────────────────────────┘
- Activating and Navigating Analysis Mode
To enter Data Analysis Mode, the user toggles the Analyze switch located at the top of any list page (e.g., Customer Ledger Entries, Sales Lines, Item Ledger Entries).
Core Components of Analysis Mode
Analysis Tabs: Users can maintain multiple analytical perspectives on a single list page. Clicking the + icon creates a new tab, which can be renamed, duplicated, reordered, or deleted.
Columns Palette: Displays all available fields from the list dataset. Users can search for fields, toggle visibility checkboxes, and drag fields into grouping buckets.
Row Groups: Dragging fields into Row Groups creates collapsible, hierarchical groupings (e.g., grouping sales entries first by Country/Region Code , then by Customer Posting Group ).
Values & Aggregations: Numerical fields dragged into the Values bucket can be aggregated using five standard mathematical functions:
Sum: Calculates total amounts (default for currency and quantities).
Count: Counts the number of non-blank records.
Min / Max: Determines lowest and highest values.
Average: Computes arithmetic mean.
Pivot Mode: Enabling the Pivot mode toggle switch unlocks the Column Labels bucket. Dragging a categorical field (such as Posting Date: Year or Location Code ) into Column Labels transforms the grid into a multi-axis pivot table, displaying metric values across columns horizontally.
Analysis Filters: Users can apply tab-specific filters within the Analysis configuration pane. These filters refine the analytical tab dataset without modifying the primary list page filters.
Sharing and Governance of Analysis Views
Sharing Analysis Tabs: Users can copy a direct URL link to an analysis tab and share it with colleagues. If an administrator creates an analysis tab during Profile Customization , that tab becomes standard equipment for all users assigned to that role.
Read-Only Analytic Safety: Data Analysis Mode is strictly a reporting interface. Users cannot edit, insert, or post records while the Analyze toggle is active, preventing accidental data modification during financial analysis.
Comparison of Reporting and Analytical Tools
Feature / Attribute Open in Excel Edit in Excel Data Analysis Mode Power BI
Primary Purpose Ad-hoc static export Bulk data entry & update In-client operational pivot reporting Cross-company executive dashboards
Interface / Tool Microsoft Excel desktop/web Excel + Dynamics Add-in Business Central Web Client Power BI Desktop / Service
Write-Back to ERP No (Read-Only) Yes (Full Validation) No (Read-Only) No (Read-Only)
Pivoting & Grouping Excel native PivotTables Excel native PivotTables Native Browser Pivots & Groups Advanced DAX & Visualizations
Real-Time Latency Snapshot at export time Real-time query & publish Real-time browser query Scheduled refresh or DirectQuery
- Step-by-Step UI Workflows
- Workflow A: Bulk Updating Item Posting Groups via Edit in Excel
Open the Items list page ( Alt+Q -> type Items ).
In the action bar, select Page -> Edit in Excel .
Open the downloaded workbook in Microsoft Excel.
In the Microsoft Dynamics Office Add-in pane, click Sign in and authenticate.
In the spreadsheet table, locate the Gen. Prod. Posting Group column.
Change the value from MISC to RETAIL across 250 rows.
In the add-in pane, click Publish .
Verify that the task pane displays "Publish successful" with zero errors.
Workflow B: Configuring a Sales Analysis Tab by Location and Year
Navigate to Customer Ledger Entries ( Alt+Q -> type Customer Ledger Entries ).
At the top of the grid, switch the Analyze toggle to ON .
Right-click the active tab header, select Rename , and enter Sales by Posting Group .
In the right-hand Analysis pane:
Toggle Pivot mode to ON .
Drag Customer Posting Group into Row Groups .
Drag Sales (LCY) into Values (ensuring aggregation is set to Sum ).
Drag Posting Year into Column Labels .
Collapse individual row groups to review annual sales subtotals by customer group directly in the browser.
Common Implementation Pitfalls & Exam Traps
Pitfall 1: Edit in Excel on Custom Pages Without Web Services. Users often wonder why Edit in Excel is unavailable or errors on a custom page. Edit in Excel requires that the underlying page object be registered and published on the Web Services page (Table 810). If the web service is missing, the add-in cannot establish an OData connection.
Pitfall 2: Bypassing Table Triggers in Excel. A frequent exam trick asks if Edit in Excel can be used to bypass mandatory posting group validations or closed fiscal periods. It cannot. The add-in executes standard OnValidate table triggers for every cell published, strictly enforcing business logic.
Pitfall 3: Confusing Analysis Mode with Data Entry. Users in Data Analysis Mode frequently report that they cannot edit cells or create new records. Analysis mode is strictly an analytical viewing surface; the Analyze toggle must be switched off to resume operational data entry.
Pitfall 4: Modifying FlowFields in Excel. Attempting to publish changes to calculated FlowFields (such as Balance ($) or Quantity on Hand ) fails because FlowFields are calculated dynamically from ledger entries and cannot be directly written to.