How Do I Migrate Spreadsheet Data Without Losing Anything?
Start with the ugly version, not the tidy one
If you are asking, “How do I migrate data from spreadsheets into a custom software system without losing anything?”, the first move is not import. It is forensic work. Most failed spreadsheet to software migration projects do not lose data in the transfer. They lose it because nobody understood what the spreadsheet was really doing before the transfer started.
I have seen this across Australian businesses in healthcare, logistics, construction, and professional services. The sheet looks simple until you open the formula bar, unhide rows, and realise half the “data” is actually a mix of comments, workarounds, and manual corrections that only one person understands.
That is the trap. If you treat the spreadsheet like a clean database export, you will preserve the wrong things and flatten the important ones.
First, separate data from behaviour
A spreadsheet usually contains four different things at once:
- Actual records like customer names, job numbers, service dates, or supplier details
- Calculated values like totals, ageing buckets, status flags, or commission amounts
- Human notes like “call before 3pm”, “use old address”, or “waiting on approval”
- Workflow hacks like colour coding, merged cells, hidden columns, and copy-pasted values that were once formulas
Before you migrate spreadsheet data, decide which of those belong in the new system.
Keep as data anything the software needs to query, validate, report on, or audit later.
Preserve separately anything that explains the history of the record but should not drive the live workflow.
Flatten intentionally anything that only exists because Excel or Google Sheets was being used as a lightweight app.
That last part matters. If a formula was calculating a status that your custom software can calculate natively, import the source fields and rebuild the logic in the application. Do not import the formula result as the only truth unless that result is the business record you need to preserve.
Key takeaway: A safe migration keeps the meaning of the spreadsheet, not every spreadsheet trick.
What to preserve, what to flatten
When a sheet has years of hidden edits, merged cells, formulas, and copy-pasted values, the safest answer is usually: preserve the meaning, not the structure.
Here is the practical split I use.
| Spreadsheet element | Preserve as-is? | Better treatment in migration | |---|---:|---| | Core record fields | Yes | Map to database fields | | Formula results that are business-critical | Sometimes | Recompute in the software if possible | | Formula logic | No | Rebuild in application rules or reporting logic | | Merged cells | No | Unmerge and normalise into rows and columns | | Hidden rows and columns | Maybe | Extract, review, then decide if they are active records or junk | | Colour coding | No | Convert to explicit status fields | | Notes in cells | Sometimes | Split into note/comment fields with timestamps if needed | | Copy-pasted values replacing formulas | No | Trace back to source if the original logic matters |
Merged cells are the classic landmine. They often mean the sheet was built for human readability, not machine readability. A merged customer name spanning five line items, for example, usually means there is a parent-child relationship hiding in plain sight. Your import should model that relationship directly, not fake it with formatting.
Hidden rows and columns deserve suspicion. In many operations teams, they contain archived jobs, old pricing, or temporary overrides. If you do not inspect them, you can miss records entirely. If you import them blindly, you can resurrect dead data and create duplicate workflows.
If you are planning a custom software data import, this is where a good data model pays for itself. A proper system can store the parent record, the child record, the note, and the status separately. A spreadsheet cannot.
The safest migration window is one with a freeze, a delta, and a rollback
The safest way to migrate data when people still need to edit the spreadsheet is not to let both systems drift freely. That is how you get duplicate records, conflicting versions, and a very expensive reconciliation exercise.
Use a three-part approach:
-
Freeze the structure
- Lock columns, formulas, and sheet layout early.
- Tell users what can still change and what cannot.
- Stop ad hoc new tabs from appearing during the migration window.
-
Capture a delta
- Export the spreadsheet at a known point in time.
- Track changes made after that snapshot.
- Reconcile only the deltas, not the entire workbook again.
-
Keep a rollback path
- Store the original export untouched.
- Keep a versioned copy of the transformed import.
- Make sure you can re-run the migration if the first pass exposes a mapping mistake.
For teams in Australia, this matters even more when the spreadsheet feeds operational reporting, compliance records, or customer-facing work. If the sheet is still live, you need cutover discipline, not optimism.
A decent migration checklist should include:
- file inventory
- field mapping
- data type checks
- duplicate detection rules
- sign-off from the person who actually owns the spreadsheet
- a cutover time
- a rollback plan
- a post-import verification report
If you want a broader view of how this fits into your wider stack, API Integration Strategy for Growing Australian Businesses is worth reading alongside this. Spreadsheet migration is often the first integration project a business does properly.
When the sheet is messy halfway through, stop pretending it is clean
Halfway through an import, you will usually find records that conflict, overlap, or are just wrong. That is not a failure. That is the point where the real work starts.
The experienced move is not to push through and “fix it later”. It is to stop, classify the problem, and decide what the system should trust.
Use this triage:
-
Duplicate records
Compare unique identifiers first, then names, then secondary fields like phone, email, ABN, job number, or supplier code. -
Conflicting records
Choose the authoritative source. Often that is the newest record, but not always. In healthcare, logistics, and compliance-heavy workflows, the newest value is not automatically the right one. -
Partially wrong records
Import the valid fields, quarantine the bad ones, and flag them for review. -
Missing required values
Do not invent them. Route them into a remediation queue.
The mistake people make is assuming the spreadsheet should be “cleaned” into one perfect version before import. That sounds tidy, but it hides the real history. Better to preserve the raw source, capture the cleaned version, and keep a clear audit trail of what changed and why.
That approach matters in regulated environments too. Pierce Solutions works across healthcare, logistics, construction, agriculture, supply chain, and professional services, and the pattern is the same: if you cannot explain a change after the fact, you have not really migrated the data.
Preserve the business meaning, not the spreadsheet’s quirks
When stakeholders insist the migration must preserve every historical quirk, they are usually trying to protect against loss. Fair instinct. Wrong implementation.
If you preserve every quirk inside the new system, you are not migrating. You are rebuilding spreadsheet chaos in a more expensive form.
The better question is: what is the business meaning behind each quirk?
Keep as-is
- Historical notes that are part of the record
- Original timestamps where they matter
- Source identifiers, even if they are ugly
- Legacy status values if they are needed for reporting continuity
Normalise
- Date formats
- Phone numbers
- Postcodes
- State abbreviations
- Currency and GST-related fields
- Duplicate contact names with the same ABN or email
Rebuild, don’t import
- Calculated statuses
- Approval logic
- Escalation rules
- Conditional formatting used as a workflow signal
- Manual flags that should become proper fields or tasks
This is where a custom software data model earns its keep. If the sheet has one cell doing the work of three different concepts, split them. For example, “Approved - waiting on invoice - urgent” should probably become:
- approval status
- invoice status
- priority
That gives you reporting, filtering, and workflow control without losing the meaning that was buried in the text.
If you need a deeper look at how old systems and new systems can coexist during this kind of change, A Guide to Integrating Custom Software with Legacy Systems covers the handover problem well.
Use a staging import, not a one-shot leap
A safe data migration is staged. A one-shot import is how you discover your mapping errors after users already trust the new system.
The practical sequence looks like this:
-
Profile the spreadsheet
- Count rows
- Identify tabs
- Find formulas, hidden columns, merged cells, and data types
-
Build a mapping document
- Source column
- Target field
- Transformation rule
- Validation rule
- Owner of the field
-
Import into a staging environment
- Not production
- Not the live customer-facing system
- A place where you can inspect the result without consequences
-
Compare source to target
- Record counts
- Sample checks
- Totals
- Exception reports
-
Fix and repeat
- Do not move to production until the import behaves the same way twice
This is where custom software development and integration services matter. At Pierce Solutions, we build around .NET, Blazor, and Azure, which makes it easier to create a staging pipeline, validate records before they hit production, and support the migration with proper audit trails rather than guesswork.
For projects where the spreadsheet is only one part of a larger system change, 12 Signs Your Systems Need an Integration Audit can help you spot the wider failure points before they bite you.
What to do with notes, history, and “tribal knowledge”
A lot of the most valuable information in spreadsheets never sits in a proper field. It lives in comments, side columns, cell notes, and the one tab nobody wants to touch.
Do not throw that away. Just do not force it into the wrong shape.
Use one of these patterns:
- Comment field for short operational notes
- History table for changes over time
- Attachment or document link for supporting evidence
- Audit log for who changed what and when
- Exception queue for items that need manual review
That is especially useful when the spreadsheet has been acting as a shared memory for years. The goal is not to preserve the spreadsheet. The goal is to preserve the knowledge without trapping it in a format that breaks the moment one person leaves.
A practical migration checklist that actually catches the bad stuff
If you are responsible for the transfer, this is the checklist I would use before cutover:
- Export the original spreadsheet and lock it
- Identify every tab, hidden row, and hidden column
- List all formulas and decide whether to preserve, rebuild, or discard them
- Map merged cells into proper relational fields
- Define unique identifiers for each record type
- Decide how duplicates will be matched
- Decide how conflicts will be resolved
- Separate notes from live workflow data
- Test the import in staging
- Run record counts before and after
- Reconcile exceptions manually
- Get sign-off from the business owner
- Keep the source file archived
If the spreadsheet is still changing during migration, add a delta capture step and a final reconciliation pass. That is the difference between a controlled cutover and a mess you will be cleaning up for weeks.
A real-world lesson from a fast setup
Michael Jones came to Pierce Solutions needing a secure, affordable setup for his website and Microsoft 365 tenancy, and the work was completed in less than 24 hours. Different problem, same principle. The result was one bill, one point of contact, and a cleaner operating model.
That is what good migration work should feel like too. Not flashy. Just controlled, fast, and traceable.
When the process is done properly, the business gets:
- fewer duplicate records
- fewer broken workflows
- cleaner reporting
- less time spent reconciling spreadsheets
- a system people actually trust
The answer, in plain terms
How do I migrate data from spreadsheets into a custom software system without losing anything? You do it by treating the spreadsheet as a source of business meaning, not a file to be copied. Preserve the raw export. Profile the mess. Decide what is data, what is history, and what is spreadsheet behaviour. Import through staging. Reconcile exceptions. Then rebuild the workflow properly in the new system instead of dragging old spreadsheet habits into software.
If you want the safer path handled end to end, Custom Software and Integration Services are built for exactly this kind of work, from mapping and cleanup through to a controlled import into a system your team can actually run on.
