Excel to Dynamics 365 Business Central: What to Expect When You Switch
8 min readIf your business has grown around Excel, moving to Dynamics 365 Business Central can sound deceptively simple. It isn't a file transfer — it's a sequence of decisions. Here's what actually happens.
You might have financial data in one workbook, customer and vendor lists in separate files, inventory in another spreadsheet, and different teams maintaining different versions of the same information.
The challenge is not simply getting those spreadsheets into Business Central.
An Excel to Dynamics 365 Business Central migration means auditing what's in your spreadsheets, cleaning and standardizing that data, mapping it to Business Central's field structure, configuring the system to match how your business actually runs, importing in stages, then reconciling the new system against the old one before go-live. Most of the real work happens in the cleanup and configuration steps, not the import itself — that's the part we want to walk you through here.
The Migration Path, Step by Step
A practical Excel to Dynamics 365 migration is a sequence of decisions and validation steps:
Microsoft supports importing master data and selected transactional data into Business Central using Excel and configuration packages. Configuration packages can be exported to Excel, populated, imported back into Business Central, and validated before the data is applied.
So what actually happens during a migration? We've walked enough businesses through this to know where it tends to go sideways, and where it doesn't need to. This guide covers what data typically moves, what happens to your existing spreadsheets, how data is prepared and mapped, and what needs to happen before the business starts working in Business Central.
Step 1: Inventory What You Have in Excel
Before anyone starts importing data, you need to understand what exists. For many businesses, the first migration exercise looks less like a technology project and more like a spreadsheet audit.
You may find customer records, vendor records, products and items, chart of accounts, inventory records, pricing information, payment terms, open invoices, open purchase documents, bank-related information, and historical records.
The important question is not, “How do we move every spreadsheet?” It is, “Which information does the business actually need in Business Central?”
A useful way to categorize the existing data:
Must migrate
Information required to operate the business.
Needs cleanup
Valuable information that isn't ready for import yet.
Can be archived
Historical or obsolete information that doesn't need to enter the new system.
Recreate in BC
Better represented through configuration than copied directly from Excel.
Microsoft's Business Central documentation identifies customers, vendors, inventory, bank accounts, and other business information among the types of data that can be transferred from other finance systems.
Here's what actually shifts once that data has real structure behind it:
| Excel-Based Process | Business Central | |
|---|---|---|
| Where data lives | Multiple workbooks, often duplicated across teams | Single structured database |
| Customer/vendor records | Free-text entry, prone to duplicates and formatting drift | Standardized fields (Customer No., G/L Account, etc.) |
| Reconciliation | Manual, workbook-by-workbook | Built-in validation against configuration packages |
| Who can trust the numbers | Depends on which version of the file you're looking at | Single source of truth across finance and operations |
| Scaling to new modules | Requires a new spreadsheet system each time | Add modules onto the same data foundation |
This inventory stage is important because migrating unnecessary data simply moves your old problems into a new system.
Step 2: Clean the Data Before It Goes Anywhere
Once you've decided what needs to move, the next step is data cleanup. This is often where the real work begins — and where we spend more time than clients expect.
Excel gives users enormous flexibility. That's useful for day-to-day work, but it also means the same business information can be entered differently by different people. You might discover two customer records for the same company, inconsistent item numbers, obsolete products, different units of measure, outdated account numbers, duplicate vendors, blank required fields, and inventory records that no longer reflect the business.
Imagine three spreadsheets containing ABC Manufacturing, ABC Manufacturing Inc., and A.B.C. Manufacturing. A person may recognize these as the same customer. A system will not necessarily make that assumption for you.
The cleanup process involves standardizing names, codes, formats, units, account references, and other fields before import. The principle is straightforward:
Bad source data doesn't become good ERP data simply because it is imported into Business Central.
Data quality should be treated as part of the Business Central migration, not as an administrative task that happens afterward.
Step 3: Map Your Excel Data to Business Central
After the data has been cleaned, you need to determine where each piece of information belongs in Business Central. This is called data mapping — a translation between your current spreadsheet structure and the structure Business Central expects.
| Existing Excel Field | Business Central Field |
|---|---|
| Customer Name | Customer Name |
| Customer Code | Customer No. |
| Product Code | Item No. |
| Product Description | Description |
| Account Number | G/L Account |
| Warehouse | Location |
| Payment Terms | Payment Terms |
The names and structures in your spreadsheets will not necessarily match the fields used by Business Central. This is where configuration packages help: Microsoft documents them as a way to work with structured Business Central tables, export data to Excel, map and prepare legacy information, then import and validate it before applying the package. The default package includes master data such as customers, vendors, items, and accounts, along with selected setup and transaction tables.
Mapping also exposes gaps. You may discover that your Excel process has no consistent field for information Business Central requires. That isn't necessarily a migration problem — it's a signal the business needs to decide how that information should be represented going forward. That makes mapping more than a technical exercise: it's an opportunity to establish one consistent data structure instead of every department maintaining its own interpretation.
Step 4: Decide What Needs to Be Configured in Business Central
Data migration and system configuration are two different activities. Data migration means moving existing information. System configuration means preparing Business Central so that information can actually be used correctly.
Depending on the business, configuration may include company information, chart of accounts, posting groups, locations, payment terms, units of measure, tax setup, number series, customer and vendor settings, inventory structure, and user permissions.
You could successfully import thousands of customer records and still have an unusable system if the underlying financial and operational configuration is incomplete. Microsoft's guidance recommends setting up a company in a sandbox so users can practice before working in production.
This is one area where Dynamics 365 consulting can add value beyond the mechanics of importing a spreadsheet. The objective is to translate the way the business currently operates into a Business Central structure that users can actually work with.
Step 5: Import the Data in Stages
A migration should not be treated as one giant spreadsheet upload. A staged approach makes it easier to identify errors and understand where they originated.
- Basic setup
- Chart of accounts
- Customers
- Vendors
- Items
- Inventory
- Pricing
- Opening balances
- Open documents
- Selected transactional data, where applicable
Microsoft's default configuration package supports multiple master-data tables and selected transaction tables, including customers, vendors, items, G/L accounts, sales and purchase documents, and journal lines.
The exact migration scope depends on the organization's starting point and what's actually required on day one. This is also why a Business Central migration shouldn't be planned around spreadsheet volume alone — a company may have hundreds of Excel files but only need a relatively small subset of that information in the new ERP.
Step 6: Test, Validate, and Reconcile
The first successful import is not the final migration. The next stage is validation.
You should check both individual records and aggregate business figures: Do customer and vendor counts match? Are all required items present? Do inventory quantities make sense? Do account balances reconcile? Are open receivables and payables correct? Are required fields populated? Are duplicate records present? Do relationships between records work correctly?
Microsoft specifically documents the ability to import and validate configuration-package data before applying it. Testing should also happen outside the production environment wherever possible — Microsoft's guidance recommends using a sandbox so users can practice and verify the setup before moving into production.
We tell clients the real milestone isn't “the import worked.” It's being able to reconcile the new system against the old one and trust the result. That distinction matters because migration errors can remain hidden until users start processing real transactions.
Financials First, Everything Else Later
We don't usually hear “we need Business Central” as an opening line. What we hear instead is more familiar: Excel has stopped working for finance, QuickBooks can't keep up with the rest of the business, and nobody's confident the numbers are right anymore.
One version of this we run into a lot: a field-services business — installation crews, project-based work, equipment tied to specific jobs — running on QuickBooks since it was a fraction of its current size. Finance is the immediate pain: QuickBooks wasn't built for job costing across dozens of active projects, and every month-end close means reconciling numbers that live in three different spreadsheets. But the finance conversation never stays contained to finance. Once we're in the weeds, we hear about scheduling headaches, commission tracking held together with formulas, HR and PTO tracked separately from payroll, and equipment or asset management nobody's touched since the business was half its size.
The instinct is to solve all of it at once. We push back on that. A financials-first phase, done well, builds the trust and the data foundation that make the operations and HR phases faster and cheaper later. Trying to migrate accounting, invoicing, project management, HR, and equipment tracking in a single project usually means the timeline balloons — and the financials, the part that actually needed fixing, get rushed.
If your business looks like this — Excel or QuickBooks handling the books while five other systems handle everything else — the question worth asking isn't “how do we replace all of it.” It's “what's the right order.” And if manual reconciliation and repetitive data entry are still eating hours after go-live, that's often where RPA consulting comes in — automating the repetitive parts so the new system actually saves the time it was supposed to.
What About a QuickBooks to Dynamics 365 Migration?
If you're not moving directly from Excel but currently use QuickBooks, the process can be somewhat different. Microsoft provides a QuickBooks Data Migration Extension for Business Central that can import customers, vendors, items, the chart of accounts, beginning General Ledger balances, inventory quantities, and certain open customer and vendor documents.
That means a QuickBooks to Dynamics 365 migration may have dedicated Microsoft-supported migration capabilities that aren't identical to an Excel-based project. However, the same fundamental questions still matter: What should move? Is the source data clean? How should it map? What needs to be configured? How will the result be tested and reconciled?
The technology changes. The migration discipline doesn't.
The Migration Is About Moving From Files to Structure
Moving from Excel to Business Central means more than transferring rows and columns. The important work happens before and after the import: deciding what information is worth keeping, cleaning inconsistent data, mapping existing fields to Business Central, configuring the new environment, importing information in manageable stages, testing the result, and reconciling the new system against the old one.
Excel's flexibility often makes it easy for businesses to adapt their processes over time. Business Central introduces a more structured environment, and the migration needs to account for that difference.
We don't measure a successful Excel to Dynamics 365 migration by how quickly the data gets imported. We measure it by whether the data is accurate, structured, reconciled, and ready to support the business on day one.
If you're planning a migration, start by documenting your current data sources and deciding what actually needs to move. That assessment gives you the foundation for determining the right migration approach — and whether you need additional Dynamics 365 consulting support for data preparation, mapping, configuration, or testing.
Frequently Asked Questions
A migration means auditing what's in your spreadsheets, cleaning and standardizing the data, mapping it to Business Central's fields, configuring the system to match how the business runs, importing in stages, then reconciling the new system against the old one. The cleanup and configuration steps typically take longer than the actual data import.
Timeline depends on how much data needs cleanup and how many modules are in scope. A financials-only migration with clean source data moves faster than a project that also covers operations, HR, or industry-specific workflows in the same phase. Staging the rollout usually shortens time to value even if the total project runs longer.
Not everything you have — only what the business actually needs going forward. Categorize spreadsheets into what must migrate, what needs cleanup first, what can be archived, and what's better recreated directly in Business Central's configuration rather than copied from Excel.
Yes, partially. Microsoft provides a dedicated QuickBooks Data Migration Extension that can import customers, vendors, items, the chart of accounts, and beginning balances directly. But the underlying questions — what should move, is the source data clean, how does it map, what needs configuring — are the same regardless of source system.
Not strictly, for very small, clean datasets. But most businesses that have grown around Excel have inconsistent data, undocumented workarounds, and requirements Business Central's default setup doesn't cover out of the box. Dynamics 365 consulting typically adds the most value in data cleanup, mapping decisions, and configuration.
Yes, and it's a common approach. Businesses often start with a financials-only migration to get accounting, invoicing, and reporting onto a stable foundation, then expand into operations, HR, project management, or industry-specific modules in later phases.
Historical files are typically archived, not deleted — useful for reference during reconciliation and for records that don't need to live in Business Central. Active spreadsheets that duplicate what's now in Business Central should be retired once the new system is validated.
Not sure where your migration should start?
We'll help you map what actually needs to move, in what order, before you touch a single import.
You might also like
Stay ahead in tech with Sunflower Lab’s curated blogs, sorted by technology type. From AI to Digital Products, explore cutting-edge developments in our insightful, categorized collection. Dive in and stay informed about the ever-evolving digital landscape with Sunflower Lab.
