How to move business data from spreadsheets to a new system

A safe data migration from Excel to a new business system follows five steps: find every source of data, clean duplicates and gaps, map each spreadsheet column to a field in the new system, run test imports with checks, then plan a final switch-over when the old sheets stop being used. Most problems come from rushing the cleaning and testing. With a clear plan, you can move years of records without losing or doubling any of them.

Data migration simply means moving information from one place to another. In this case, from spreadsheets (in Excel or similar tools) into a new system such as a CRM, booking platform or custom business application. If you are planning a new system, our custom software service page explains how we approach these projects.

Planning a data migration from Excel: finding every source of data

Before cleaning anything, find out what data exists and where. Businesses are often surprised by how many copies and versions they have.

1. List every spreadsheet in use. Ask each team, not just managers. Personal copies on laptops and email attachments often hold important records.
2. Find the other sources. Data may also live in email inboxes, paper forms, accounting software or old systems.
3. Identify the master copy. Where several versions of the same sheet exist, decide which one is correct and most up to date.
4. Describe each source. Note what it contains, who updates it, how often, and roughly how many rows it has.
5. Decide what to move. Not everything needs to come across. Old records you are no longer required to keep may be archived instead of migrated. Check your record-keeping and privacy obligations before deciding.
6. Name an owner for each dataset. Someone who knows the data should answer questions and approve the result.

The outcome is a simple inventory: each source, its owner, what it holds and whether it will be migrated, archived or left behind.

Cleaning duplicates and gaps

Spreadsheets allow anything to be typed anywhere, so data quality varies. A new system is usually stricter, and messy data either fails to import or causes problems later. Cleaning is usually the biggest part of the work.

1. Work on a copy. Never clean the original files. Keep them untouched as a reference.
2. Find duplicates. Look for the same customer, product or supplier entered more than once, often with small differences in spelling or format. Decide which record to keep and what to merge.
3. Standardise formats. Dates, phone numbers, country names, currencies and units should follow one format. Choose the format the new system expects.
4. Fill or flag gaps. Required fields, such as an email address or a customer ID, may be missing. Fill them where you can, and flag the rest for someone to review.
5. Split combined fields. A single column holding "name and phone" or a full address in one cell may need splitting into separate fields.
6. Remove formulas and formatting. Colours, merged cells and formulas carry meaning to people, but the new system needs plain values. If a colour meant "overdue", turn it into a proper status column.
7. Watch for silent changes. Spreadsheet programs can change values when files are opened or saved, such as removing leading zeros from codes or reformatting dates and long numbers. Check these fields carefully in the exported files.

Keep a short log of every cleaning rule you apply. It explains later why a record looks the way it does, and it lets you repeat the same steps on the final export.

Mapping columns to the new system

Mapping means deciding where each piece of spreadsheet data goes in the new system. It is where business knowledge and system design meet.

1. List the fields in the new system. Your developer can provide these, with the format each expects and which are required.
2. Match each column to a field. Build a simple table with three columns: spreadsheet column, new field, and any conversion needed.
3. Handle lists and codes. Status values, categories and types often differ between sheets. Map each old value to one of the new system's options, such as turning "Done", "done" and "Complete" into one status.
4. Connect related records. Spreadsheets often link records by name, while systems use IDs. For example, orders may need to be linked to customers after the customers are imported.
5. Decide the import order. Import records that others depend on first, such as customers before orders and products before stock levels.
6. Record the decisions. The mapping table becomes the instructions for the import and a reference for anyone checking the result.

Deciding what happens to history

Spreadsheets often mix current records with years of history. Decide how much history the new system really needs.

1. Current records. Active customers, open orders, current stock and upcoming bookings almost always need to move.
2. Recent history. Past orders or jobs are useful for reports and customer service. Many businesses move a limited period and archive the rest.
3. Old history. Very old records may be better kept in a read-only archive, where they can still be searched if needed.
4. Legal requirements. Some records must be kept for set periods. Check your obligations before deciding to leave anything behind.

Moving less history makes cleaning, mapping and testing faster, and keeps the new system tidy from the start.

Structured requirements make this step much easier. If you are still describing the new system, our guide on how to write software requirements explains how to capture the rules and data it needs.

If data will keep flowing from another tool after launch, our guide to business software integration explains one-way and two-way syncs.

Test imports and checks

Never import straight into the live system. Run test imports into a copy first, check the results carefully, fix the problems and repeat.

1. Export clean files in a consistent format. CSV (comma-separated values) files are common. Agree the format, character encoding and date style with your developer so names with accents and special characters survive.
2. Import into a test environment. This is a copy of the new system where mistakes do no harm.
3. Compare counts. The number of customers, products or orders in the new system should match the cleaned source. Differences point to rows that failed or were doubled.
4. Check totals. For financial data, compare totals such as outstanding balances or stock values between the old and new data.
5. Spot check real records. Ask the data owners to open a sample of records they know well and confirm they look right, including linked records.
6. Review the error report. Rows that could not be imported should be listed with a reason, fixed in the source and imported again.
7. Test everyday tasks. Ask staff to use the imported data for normal work, such as finding a customer or creating an invoice.

Repeat until the checks pass cleanly. Most migrations need more than one round.

Who should check the data

Checking is not only a technical job. The people who use the data every day are best placed to spot problems.

• Data owners confirm that records are complete and correct for their area, such as customers, products or finances.
• Everyday users try normal tasks and notice when something looks unfamiliar or is missing.
• Finance confirms that balances and totals match.
• The developer checks technical details such as failed rows, links between records and character encoding.

Agree in advance who signs off each dataset. A clear sign-off prevents last-minute doubts on the switch-over day.

The final switch-over

The switch-over is the moment the business stops using the spreadsheets and starts using the new system. Planning it carefully prevents a period where both are updated and neither is correct.

1. Choose a quiet time. Pick a period with low activity, such as the end of a week or month.
2. Freeze the spreadsheets. Announce a cut-off time after which no one edits the old files. Make them read-only if possible.
3. Export, clean and import the final data. Apply the same cleaning rules and mapping you tested, so the result is predictable.
4. Run the same checks. Compare counts, totals and sample records again before announcing that the system is live.
5. Keep the old files as an archive. Store the final spreadsheets safely in read-only form, in case questions come up later.
6. Support staff in the first days. Expect questions about where data went and how to find it. Quick answers build trust in the new system.

If the volume of data is small, some businesses enter a few recent records by hand instead of repeating the import. Decide this in advance rather than on the day.

The switch-over is one part of a wider software rollout plan, which also covers training and support for staff.

Summary

• Find every data source, choose master copies and decide what to migrate or archive.
• Clean a copy of the data: duplicates, formats, gaps, combined fields and silent changes.
• Map each column to a field, convert lists and codes, and import in the right order.
• Run test imports and check counts, totals and real records until they match.
• Freeze the spreadsheets, repeat the tested steps for the final import and keep the originals archived.

Good data is what makes a new system useful from its first day. If you're planning to move your business data into a new system, you can tell us about it here.