Working with Data: Excel, OneDrive & Analysis

Beyond the Standard Interface

Edit in Excel

How It Works

From any list page, click Share (the share icon at the top) and select Edit in Excel. Business Central generates an Excel workbook with a live connection back to BC via the Business Central Excel Add-in.

The workflow:

1. Open — The list opens in Excel (browser or desktop app, depending on OneDrive setup)
2. Edit — Make changes to existing records or add new rows
3. Publish — Click the Publish button in the Excel Add-in pane to push changes back to BC
4. Refresh — Click Refresh to pull the latest data from BC into the spreadsheet

What You Can Do

• Update fields on multiple records at once (e.g., bulk price changes)
• Add new records by typing in new rows
• The add-in respects the page filters — only filtered records appear
• Works with the web client and desktop Excel

Limitations

• Not all pages support Edit in Excel (some pages are read-only)
• Complex fields like FlowFields (calculated fields) are read-only in Excel
• Validation rules still apply — if a value violates BC validation, the publish reports an error
• Maximum of 100 columns when using Excel for the web with OneDrive

ℹ️ Scenario: Olivia's bulk price update

Olivia at Coastal Traders needs to increase all item prices by 5% for the new financial year. She opens the Item List, clicks Edit in Excel, uses an Excel formula to multiply the Unit Price column by 1.05, and publishes the changes back. Done in minutes instead of hours.

Open in Excel vs Edit in Excel

Feature	Open in Excel	Edit in Excel
Direction	One-way (BC to Excel)	Two-way (BC to Excel and back)
Can push changes back?	No — download only	Yes — Publish button sends changes to BC
Uses Add-in?	No	Yes — requires BC Excel Add-in
Respects page filters?	Yes	Yes
Use case	Export a snapshot for analysis/sharing	Bulk edit data and write back to BC

OneDrive Integration

Business Central integrates with Microsoft OneDrive for Business to store and share files attached to records.

Attaching Files to Records

On any card page (Customer Card, Vendor Card, Item Card, etc.):

1. Open the record
2. Go to the Attachments FactBox
3. Drag and drop a file onto the FactBox — or click Attach and browse

The file is stored in the user’s OneDrive in a structured folder. Other users with access to the record can view the attachment.

Opening Reports in OneDrive

When you run a report and choose Send to > Microsoft Excel Document, if OneDrive is configured, the file opens directly in Excel for the web in your OneDrive. You can then share it with colleagues using standard OneDrive sharing.

Key Points for the Exam

• OneDrive integration must be enabled by an admin (it is on by default in BC online)
• Files are stored in the user’s OneDrive, not in BC’s database
• Drag and drop works on card pages, not list pages
• Sharing uses standard OneDrive permissions — BC security does not control file access after export

ℹ️ Scenario: Raj attaches a supplier contract

Raj receives a signed contract PDF from a new supplier. He opens the Vendor Card, drags the PDF into the Attachments FactBox, and it is immediately available. When Nina needs to check the contract terms later, she opens the same Vendor Card and clicks the attachment — it opens from OneDrive in her browser.

Data Analysis Mode

Data analysis mode is a built-in analytical experience directly on list pages — no need to export to Excel or build a Power BI report for quick ad-hoc analysis.

How to Enter Analysis Mode

1. Open any list page (e.g., Items, Customers, Sales Orders)
2. Click the Analyse toggle at the top of the page (or press Ctrl+Shift+F12)
3. The page transforms into an analysis workspace

What You Can Do in Analysis Mode

Feature	How
Group rows	Drag a column header to the grouping area
Pivot	Drag columns to rows area and values to the data area
Filter	Click column headers to add analysis-specific filters
Aggregate	Choose Sum, Count, Average, Min, Max for numeric columns
Multiple tabs	Create named analysis tabs with different layouts

Analysis Tabs

You can save multiple analysis configurations as named tabs. Each tab remembers its grouping, pivoting, and filter settings. Tabs are personal to each user.

Example: Olivia creates three tabs on the Customer Ledger Entries page:

• “Overdue by customer” — grouped by Customer Name, filtered to overdue entries, showing sum of Remaining Amount
• “Monthly totals” — grouped by Posting Date (month), showing sum of Amount
• “Payment method breakdown” — grouped by Payment Method Code, showing count and sum

💡 Exam tip: Analysis mode vs Power BI

If the exam asks about quick, ad-hoc analysis directly in BC without leaving the interface, the answer is data analysis mode. If it asks about dashboards, scheduled refreshes, or sharing analysis across the organisation — that is Power BI.

Three Ways to Work with Data Compared

Aspect	Edit in Excel	Open in Excel / Export	Data Analysis Mode
Direction	Two-way (read and write back)	One-way (read only)	Read only (in-app)
Requires Excel?	Yes	Yes	No — works entirely inside BC
Can update BC data?	Yes — Publish button	No	No
Grouping and pivoting?	Manual (build your own pivot table)	Manual	Built-in drag-and-drop pivoting
Best for	Bulk data updates	Exporting snapshots for offline analysis	Quick ad-hoc analysis without leaving BC
Saved configurations?	No (each session is fresh)	No	Yes — saved as named analysis tabs
Needs Add-in?	Yes — BC Excel Add-in	No	No
Access	Share icon on list pages	Share icon or report Send To	Analyse toggle on list pages

Question

What is the key difference between Open in Excel and Edit in Excel?

Click or press Enter to reveal answer

Answer

Open in Excel is a one-way export (read-only snapshot). Edit in Excel creates a live two-way connection via the BC Excel Add-in — you can make changes and Publish them back to Business Central.

Click to flip back

Question

How do you attach a file to a record in Business Central?

Click or press Enter to reveal answer

Answer

Open the record's card page, find the Attachments FactBox, and drag and drop the file onto it (or click Attach and browse). The file is stored in the user's OneDrive for Business, not in BC's database.

Click to flip back

Question

What is data analysis mode and how do you activate it?

Click or press Enter to reveal answer

Answer

Data analysis mode is an in-app pivot-table-like experience for ad-hoc analysis on any list page. Activate it by clicking the Analyse toggle at the top of a list page (or Ctrl+Shift+F12). You can group, pivot, filter, and aggregate data — and save configurations as named tabs.

Click to flip back

Knowledge Check

Knowledge Check

Olivia needs to update the unit prices of 300 items. She wants to use a formula to calculate the new prices and push the changes back to Business Central. What should she use?

AOpen in Excel to export, edit, and re-import via a Configuration PackageBEdit in Excel to modify prices in the spreadsheet and Publish changes backCData analysis mode to group items and update pricesDExport to CSV, edit in Excel, and import via RapidStart

Check Answer

Knowledge Check

Raj wants to quickly see his purchase orders grouped by vendor with total amounts, without leaving Business Central or building a report. What should he use?

AEdit in Excel and create a pivot tableBThe filter pane on the Purchase Order ListCData analysis mode on the Purchase Order ListDPower BI embedded reports

Check Answer

🎬 Video coming soon

---

Next up: Data tools mastered. Now let’s dive into the purchasing cycle — from purchase quotes through orders, receipts, and invoices, including special features like over-receipts and blanket purchase orders.