From Spreadsheets to Dataverse: A Mid-Market Guide to Consolidating Business Data

Consolidating spreadsheets into Dataverse works when you treat it as a data modelling project, not a file upload. Start by inventorying every workbook that people actually rely on, then identify the real entities hiding inside them: customers, contacts, assets, orders, cases. Design Dataverse tables with proper relationships, choice columns and alternate keys, clean and deduplicate before import, and load in dependency order so lookups resolve. Then replace the workbook habits with model-driven forms, views and Power Automate flows, and retire the files on a fixed date. Where Microsoft licensing does not fit, the same method applies to a custom CRM on PostgreSQL.

We recommend the right solution - whether that's Microsoft Dynamics 365, Power Platform, or a custom-built CRM. Some businesses need the Microsoft ecosystem. Others need full control without licensing. We deliver both.

Why do spreadsheets stop working as a system of record?

Spreadsheets rarely fail all at once. They fail at the edges. Two people save different copies of the same customer list and nobody knows which is current. A lookup formula breaks because someone inserted a column. The account manager who built the pricing tracker leaves and the macros become untouchable. Sensitive columns are visible to everyone with the file link, because a workbook has no row level security. There is no audit trail showing who changed a status and when, and no way to trigger a follow-up when a date passes.

The deeper problem is structural. A spreadsheet stores one flat grid, so relationships are faked with repeated text. The same company appears as "Acme Ltd", "ACME Limited" and "Acme" across three tabs, and any report built on top inherits the mess.

How do you find out which spreadsheets actually matter?

Run an inventory before anyone opens the Power Apps maker portal. For each workbook, record the owner, who edits it, who only reads it, how often it changes, what decisions depend on it and which other files it feeds. Shared drives and Teams channels usually hold far more workbooks than anyone expects, and many are abandoned snapshots.

Sort them into three groups. Operational workbooks are edited daily and drive real work: these are the migration scope. Reference workbooks hold slowly changing lists such as product codes or regions: these become choice columns or small lookup tables. Analytical workbooks are reports someone builds from the others: these should be rebuilt in Power BI or model-driven views after migration, not migrated themselves.

How do you turn workbook columns into a Dataverse data model?

Read every operational workbook as a collection of entities in disguise. A column called "Client" with free text is a lookup to an Account table. Columns named "Contact 1", "Contact 2" and "Contact 3" are a one-to-many relationship to Contact. A "Status" column with eight spellings of the same five values is a choice column. A tab of site visits keyed by customer name is a child table with its own lookup.

Some practical rules we apply on Dataverse projects:

• Reuse standard tables such as Account and Contact where they genuinely fit, because Outlook integration, Copilot features and Dynamics 365 apps expect them.
• Create custom tables with a clear publisher prefix inside a solution from day one, so the work can move between development, test and production with proper ALM.
• Define an alternate key on every table that will be imported, typically an external reference or a business identifier. Alternate keys let imports and integrations upsert instead of creating duplicates.
• Choose column types deliberately. Currency, whole number and date only columns are hard to change once data exists, and a text column used for numbers will block sorting and rollups later.
• Plan security roles and business units with the model, not after it. Whatever the spreadsheet exposed to everyone should now be scoped by team or owner.

What is the safest way to clean and load the data?

Clean outside Dataverse first. Power Query, which is available in dataflows, is well suited to trimming whitespace, standardising company names, splitting combined fields and mapping legacy status values to the new choice options. Deduplicate against the alternate key you defined, and keep a mapping sheet that records every transformation so business owners can sign it off.

Load in dependency order: parent tables first, then children, so every lookup has a record to resolve against. Dataflows work for repeatable loads, while the Configuration Migration tool or a scripted load through the Dataverse Web API suits larger or one-off migrations. Run at least one full rehearsal into a test environment, compare row counts and spot-check records with the people who own the data, then schedule the production load for a quiet window.

How do you stop people going back to spreadsheets?

Migration fails when the new system is harder to use than the old file. Give users model-driven views that look like the grids they know, with editable grids where bulk updates are common, and Excel export for the moments they genuinely need it. Replace manual chasing with Power Automate flows: reminders on due dates, approvals, and notifications when a record changes state. Where a standard control cannot show the data the way a team works, such as a matrix or a timeline, a PCF control closes the gap without leaving the platform.

Then set a retirement date. Make the old workbooks read only, archive them, and say clearly that Dataverse is the system of record from that day.

When is a custom CRM a better destination than Dataverse?

Dataverse is the natural destination when the business already lives in Microsoft 365 and wants Power Apps, Power Automate and a path to Dynamics 365 Sales or Customer Service later. It is less suitable when most users are occasional or external, when data must be hosted on your own infrastructure, or when the per-user licensing model does not fit the organisation. In those cases a custom CRM on React, Node.js or .NET and PostgreSQL gives the same structured data model with full control. The inventory, modelling and cleaning steps above do not change, only the target.

Either way, the CRM should integrate with the systems around it, such as an ERP or accounting platform, rather than copy their data into yet another spreadsheet. You can see how we approach consolidation for regulated organisations on our financial services industry page and in the European bank CRM modernization case study. Our services overview explains how we run these assessments across Dynamics 365, Power Platform and custom builds.