+1 480 485 7373
Microsoft Teams
Why workbook data needs more work than data from another ERP
When data comes out of another ERP, it at least obeyed that system's rules. Every invoice had a customer, and every item had a unit of measure. Spreadsheets promise nothing of the kind. The same vendor appears under three spellings across two workbooks. Quantities hide in merged cells. And the current price list is whichever copy someone saved last. Business Central rejects much of this on import, which is useful, but it means cleaning comes first.
Spreadsheet-run companies also encode process in the file itself. Think of a cell color that means on hold, a tab per warehouse, or a column someone updates by hand every Friday. Business Central handles each of those habits with a field, a status or a location. So part of the migration is naming the habits and choosing their replacements before we load anything.
Cleaning and deduplicating the workbooks
- 1
Collect every source
List every workbook anyone relies on for customers, vendors, items, prices, stock and open orders, with its owner. Private copies on desktops count too.
- 2
Name a master copy per list
For each list, agree which file wins when two disagree, and stop edits to the others.
- 3
Normalize the text
Trim spaces and standardize case. Split combined fields such as city, state and zip, and unify units of measure and date formats.
- 4
Find the duplicates
Match on tax ID, email, phone and cleaned names, then merge by hand where a match is uncertain. Fuzzy matching finds candidates; a person decides.
- 5
Assign real keys
Give every customer, vendor and item a number from the number series you chose. Keep a cross-reference to the old spreadsheet names.
- 6
Validate before loading
Check required fields, posting groups and allowed values against the target tables. Then errors surface in your review, not during the import.
Common workbook tabs and their Business Central destinations
Business Central features made for spreadsheet imports
Configuration packages
Configuration packages are part of what Microsoft calls RapidStart. You choose a table and its fields, export an Excel file, fill it and import it back. Then you review validation errors row by row and apply the data.
- ✓Pick only the fields that matter
- ✓Errors show per row before you apply anything
- ✓Works for setup tables as well as master data
Edit in Excel
Most lists and journals open in Excel through the Business Central add-in. You edit them there and publish them back. It serves bulk corrections after go-live as much as the migration itself.
- ✓Opening journals prepared in Excel, posted in Business Central
- ✓Bulk updates to item or customer fields
- ✓Respects your Business Central permissions
Customer, vendor and item templates
Templates hold posting groups, payment terms, tax settings and other defaults. So a clean spreadsheet only needs the fields that truly differ per record.
- ✓Fewer columns to fill, fewer mistakes
- ✓The same templates serve new records after go-live
- ✓Separate templates for, say, domestic and export customers
Balances, open items and history a spreadsheet can support
- ✓An opening trial balance from your accountant or current books at a month end, not a mid-month date
- ✓Each unpaid customer invoice as its own line, so collections can apply payments against it
- ✓Each unpaid vendor bill as its own line with its due date, so payment runs work in week one
- ✓Stock counted physically at cutover, because spreadsheet stock figures are rarely safe to load
- ✓Open orders keyed or imported from the tracker, with confirmed quantities and dates
- ✓Past sales left in the workbooks, or loaded as monthly totals only where a report truly needs them
What takes over from the hand-built reports
Spreadsheet companies usually run on a handful of reports someone builds by hand. Typical ones are a weekly sales summary, a cash forecast, an aged debtors list and a stock reorder sheet. After an Excel spreadsheets to Business Central migration, most of these become standard reports or saved list views. Examples are aged accounts receivable, item availability and the requisition worksheet. Financial reports cover the monthly profit and loss. Anything that still needs a custom view goes to Power BI or to analysis mode on a list.
Excel does not disappear, and it should not. Finance teams keep it for modeling, and Business Central exports to Excel from nearly every page. The difference is that Excel becomes a place to analyze data held in one system, not the system itself. We rebuild the logic buried in macros and formulas rather than migrate it. Approval steps become workflows, reorder formulas become item reorder policies, and pricing formulas become price lists.
Spreadsheet migration questions
Can we just import our spreadsheets as they are?
Rarely. Business Central validates every field on import. Blank posting groups, unknown units of measure or duplicate numbers stop the load. Cleaning the data first costs far less than fixing bad records after go-live. We hand over the configuration package files early so your team can start filling them while setup continues.
Is there a Business Central data migration tool for Excel?
Yes, for the basics. The data migration assisted setup includes an Excel option for customers, vendors and items using a template workbook. For prices, opening entries, stock and setup tables, configuration packages and Edit in Excel do the rest. That is why most projects use a mix of the three.
How do we deduplicate customers when names differ in spelling?
Match on something more stable than the name first. Tax IDs, email domains, phone numbers and billing addresses find most duplicates once we normalize the text. A fuzzy-match pass then suggests the remaining candidates, and someone who knows the customers confirms each merge. We keep a cross-reference, so you can still trace old spreadsheet names.
More Excel spreadsheets to Business Central migration questions
Should we move years of sales history out of Excel?
Usually not at line level. Old spreadsheet history is often incomplete and inconsistent, and loading it produces reports nobody trusts. Keep the workbooks archived and read-only for lookups. Load monthly totals only where a year-over-year comparison matters on day one.
Do we need an ERP at all, or just better spreadsheets?
It depends on how many people touch the same data and how often it goes wrong. If one person keeps the books and orders are few, a good accounting package may be enough. Once several people update stock, prices and orders at the same time, spreadsheet errors start to cost real money. Our readiness assessment looks at that honestly before recommending anything.
Talk to us about your project.
Tell us what you run today and what has to change. A senior consultant replies with a written next step.