How to Migrate From Spreadsheets to a Proper System

Who This Guide Is For

This guide is for business owners and operations leads who run a process on spreadsheets (Excel, Google Sheets or a mix of both) and have decided it needs to move into a proper system: a database, a CRM, a client portal, an inventory or contract system, or a custom app. It covers getting the data across without losing anything and without stopping the team working. It applies whether the sheet holds a sales pipeline, performance figures, stock, contracts, territories or asset records for a CAFM system. The steps are the same; only the columns change.

Before You Start

You need to know what you are moving to. This guide covers the migration itself, not the choice of system. If you are still deciding, spreadsheets vs a database for operations covers when a spreadsheet is still the right tool, and Airtable vs a custom system looks at the halfway house most teams try first.

You also need a list of which sheets hold live data and which are working documents. Not everything in a spreadsheet needs to move. Some of it is scratch work, some is out of date, and some is a copy of another tab.

When Not to Migrate Yet

A migration is the wrong move in a few situations, and it’s cheaper to spot them now than halfway through:

• The process itself isn’t settled. If the columns change every month because the team is still working out how the job should run, a fixed system will freeze the wrong process. Let it settle in the sheet first.
• One person uses it and nothing else depends on it. A personal tracker with a few hundred rows rarely repays the effort.
• The real problem is a shared file nobody owns. Sometimes the fix is an owner, locked header rows and data validation, not a new system.
• Nobody has time for the cutover. A migration needs a few hours of someone who knows the data in every week of the project. Without that, the new system launches with gaps and the team drifts back to the sheet.

If none of those apply, carry on.

Step 1: Audit What You Actually Have

Open every sheet that feeds the process and write a one-line inventory entry for each: file and tab name, what it tracks, number of rows, who edits it, and known problems. Note which sheets look things up from other sheets, because every VLOOKUP, XLOOKUP or IMPORTRANGE is a relationship the new system has to hold.

Expect to find duplicate records across tabs, columns that mean different things depending on who filled them in, dates typed in three formats, and “temporary” columns that became permanent. A Status column with fourteen spellings of four real statuses is normal.

The inventory becomes your migration checklist, and the lookups become the relationships in the data model.

Step 2: Clean the Data in the Spreadsheet First

Clean the data while it is still in the spreadsheet, where everyone knows how to edit it. Once it has moved, every bad record has other records pointing at it and is harder to fix.

Work on a copy, never the live file. The built-in tools cover most of the job:

• Duplicates. In Excel, Data then Remove Duplicates, choosing the columns that identify a record (an email address or account number, not a name). In Google Sheets, Data then Data cleanup then Remove duplicates. Review what it would remove before you trust it; two “John Smith” rows can be two real people.
• Stray spaces and inconsistent text. TRIM removes leading and trailing spaces, and Google Sheets’ Data cleanup has a Trim whitespace option. PROPER or UPPER makes names and postcodes consistent.
• Dates. Pick one format and convert everything to it. Dates typed as text (a left-aligned date in Excel is usually text) are the most common cause of failed imports.
• Free-text categories. Filter each category column, list the distinct values, and decide what each one maps to.
• Missing required fields. Filter for blanks in the columns the new system will insist on (an email address for a contact, a client for an invoice). Fill them, or set the rows aside.

For larger or messier files, Excel’s Power Query (Data then Get Data) lets you record the cleaning steps once and rerun them on a fresh export the night before cutover, which saves repeating the work by hand.

Records you can’t clean go into a separate archive file, not into the new system. A system with the 95 percent of records you trust is more useful than one with everything and a doubt over every row.

Step 3: Map Every Column to a Field

The new system has fields, fixed lists of options and links between records. Your spreadsheet has columns. They won’t line up one to one, so write the mapping down before anything moves. A mapping document is a table with one row per source column:

Three kinds of row need a decision. A column with no target (drop it, or keep it in an archive). A target field with no column (a default value, or someone fills it in after). A column that becomes two fields, or two that become one. Getting those agreed on paper is most of the work, and it’s the step most often skipped.

The same template works for any data type. For a sales pipeline, the hard rows are stages and owners. For performance or reporting data, it’s the date granularity and whether totals are stored or calculated. For stock, it’s units and locations. For contracts, renewal dates and linked documents. For CAFM, asset IDs and the site and room each asset belongs to.

If you are turning the spreadsheet into an app rather than loading it into an existing product, the mapping table is also the first draft of the database design. Each tab usually becomes a table, and each lookup between tabs becomes a link between tables.

Step 4: Export and Run a Test Migration

Export the cleaned data as CSV. In Excel, use Save As then CSV UTF-8 (Comma delimited), because the plain CSV option can mangle accented names and the £ sign. In Google Sheets, File then Download then Comma-separated values. Export one CSV per tab.

Then load a sample of fifty to one hundred records using the target system’s importer. Most CRMs, Airtable, and tools such as Microsoft Access (External Data, then New Data Source, then From File, then Excel) have a column-matching import screen, which is where your mapping document earns its keep. For a custom system, your developer writes the import script from the same document.

Check the sample against the source by hand. Do dates, statuses and amounts come through correctly? Are the links between records intact, so a contact still belongs to the right account? Test the awkward records on purpose: the longest name, the oldest date, a record with an apostrophe or an accent, a record with blank optional fields. Fix the mapping or the source and run the sample again. Two or three rounds is normal.

Step 5: Migrate the Full Dataset

With a clean test, take a fresh export (rerunning any Power Query steps), load everything, and reconcile:

• The record count in the new system matches the cleaned spreadsheet, minus the rows you archived.
• Totals agree. The sum of open pipeline value, or the stock count by location, is the same in both.
• A sample of records from the start, the middle and the end of the file looks right. The first ten rows are rarely where the problems are.

Agree a cutover date with the team. From that date the spreadsheet is read-only and new work goes into the system. Running both in parallel for a short period is sensible; two to four weeks is typical, and it should have an end date.

Step 6: Retire the Spreadsheet

Once the team is working in the new system and the reconciliation holds, archive the spreadsheet. Don’t delete it. Set it to view-only, and rename it so its status is obvious: “Client Pipeline – ARCHIVED – moved to CRM 2026-10”.

An editable spreadsheet that’s still around will get used, out of habit more than doubt about the new system. Taking edit access away is what finishes the migration. The people side of that change is covered in How to Roll Out a New Internal System.

An Example Migration

A twelve-person services firm tracks its sales pipeline in a shared Google Sheet: one tab of deals, one of contacts, and a tab of won work the finance lead copies figures from each month. The sheet has around 900 deal rows going back four years.

The audit shows the contacts tab duplicates names from the deals tab, and the Stage column holds eleven variations of six stages. Cleaning removes duplicate contacts by email address, maps the stages to six fixed values, and archives deals closed more than two years ago, which leaves about 350 open and recent deals. The mapping document has 18 rows, including one decision that took a meeting: the free-text Next Step column becomes a dated task, not a note. Two test imports of 60 deals catch a date format problem and a set of contacts with no email address. The full import runs on a Friday, the totals reconcile, and on Monday the sheet goes view-only.

That is a data migration at its smallest: one source, one target, a few hundred records. Moving between two live systems, or across several at once, is covered in How to Plan a Data Migration.

Common Mistakes

• Migrating without cleaning. Bad data in a new system is harder to fix than bad data in a spreadsheet, because it now has relationships and other records pointing at it.
• Skipping the test import. A test of fifty records takes an hour. Fixing five hundred after a bad full import takes days.
• Moving everything. A client who hasn’t been active for three years doesn’t need to be in the new system on day one. Archive old records separately; they can be loaded later.
• Mapping on the fly. Without a written mapping, the same column gets treated two ways on two days.
• Leaving the spreadsheet editable. Someone will keep updating it, and the two will drift apart within a week.

Frequently Asked Questions

How do I turn an Excel spreadsheet into a software application?

There are three routes. A no-code tool such as Airtable, or Microsoft Power Apps on top of Dataverse, imports the sheet and lets you build forms and views over it. An off-the-shelf system (a CRM, an inventory or a contract management system) imports the data into a structure someone else designed. A custom app gives you a database designed around your process, with the spreadsheet’s tabs and lookups as the starting design. In every case the data work is the same: audit, clean, map, test, migrate. Custom software development is the route when neither of the first two fits the way you work.

Can you convert an Excel file to an Access database?

Yes. In Access, use External Data, then New Data Source, then From File, then Excel, and choose whether to import the sheet into a new table or link to it. Clean the sheet first and give every column a single data type, because Access sets field types from the data it finds and a column mixing numbers and text imports badly. Access suits a small team on one network; for anything used remotely or by more than a handful of people, a web-based system is usually the better target.

What is an example of a data migration?

Moving a sales pipeline from a shared spreadsheet into a CRM is a typical small one, like the example above. Larger ones include moving from an old accounting package to a new one, merging two companies’ customer databases after an acquisition, or moving a legacy system’s records into a modern platform.

How long does a spreadsheet migration take?

For a single spreadsheet of a few hundred to a few thousand rows going into an existing product, one to three weeks, most of it cleaning and mapping. Several linked spreadsheets going into a custom system take longer, because the data model has to be designed as well. The import itself is usually the shortest step.

Should we migrate historical data or start fresh?

Migrate what the team needs to do its work: open records, active clients and recent history. Archive the rest in its cleaned form so it can be loaded later if someone needs it. Starting completely fresh only works when the old records are no longer referred to.

Can we keep using the spreadsheet for reporting after the move?

Yes, as long as it’s fed from the new system, through an export or a connected report, and nobody types into it. The moment people edit both, you have two sources of truth again. A reporting dashboard built on the new system usually replaces the reporting spreadsheet within a few months.

What Good Looks Like

The new system holds clean, complete data for every active record. The totals reconciled on cutover day. The team works in the new system and the old spreadsheet is read-only. The mapping document is filed with the project, so the next person to touch the data can see why each field holds what it holds.

Next Steps

For moving data between two live systems, How to Plan a Data Migration covers the more complex case, and the other implementation guides cover the rollout around it. If the dataset is large, regulated or spread across several systems, Data Migration Services is how we take on that work, or get in touch to talk through what you have.