How to Replace a Spreadsheet Process Without Losing Work

To replace a spreadsheet process safely, transfer the rules and outstanding work as well as the rows. Rehearse the move, test real jobs from start to finish, and agree exactly when staff stop updating the old sheet.

The biggest risk is importing a tidy list that no longer tells people what to do next. A yellow cell might mean a customer needs chasing. A formula might decide whether an invoice can be raised. Neither behaviour necessarily survives a file import.

I would start by tracing a live job with the person who actually updates the spreadsheet, before writing a software feature list. Then pilot the replacement on a clearly bounded set of jobs, with one agreed place to update each job.

Trace one real job from enquiry to completion

Ask a member of staff to show you a recent job, including the emails, documents and other sheets they used. Follow how it arrived, who took responsibility, what changed its status and how everyone knew it was finished. Include a job that went wrong, not only a straightforward example.

Inspect formulas, hidden columns, comments, colour codes and filters. Ask what happens outside the workbook: who chases missing information, who checks a payment and who notices work that has stalled.

For each convention, record its meaning and decide whether it is a necessary business rule, a workaround or an obsolete habit. You do not need to reproduce every column as a software feature.

Worked example: turn spreadsheet habits into requirements

Illustrative example: a service business tracks repair jobs in a workbook. Office staff book visits, engineers report completion, and a manager approves jobs for invoicing. These are example requirements, not results from an actual migration.

• Yellow row: staff are waiting for the customer to confirm access. Replace the colour with an explicit “Awaiting access confirmation” status, a named owner and a follow-up date. Test that the job appears in that person's follow-up list.
• Invoice formula: a cell flags completed jobs with recorded parts costs. Confirm the full rule with the manager. If approval is also required, test that completion alone cannot make the job ready for invoicing.
• Comment saying “return visit”: the first visit is complete but the job remains open. Provide a way to record another visit without losing the first visit's notes. Test that the job remains outstanding.
• Blank engineer field: staff currently recognise this as unassigned work. Give the replacement an unassigned queue and test that these jobs remain visible.

This becomes the requirements brief: the situation, the required behaviour and the test that proves it works. It is useful whether you configure an existing product or commission custom software.

Choose the first set of jobs and the history it needs

Choose a pilot that covers a complete operational task with a clear boundary. In the repair example, that could be jobs assigned to one service team, including its existing open jobs. Avoid choosing only easy new bookings if the eventual system must handle partially completed repairs.

List what each open job needs to continue: customer details, current status, assigned staff, next action, appointments, relevant notes, documents and payment information where applicable. Preserve a stable job reference so you can match the old and new records.

Decide separately what to do with completed history. Some records may need importing for repeat visits, reporting or customer queries. Others may remain in a controlled, read-only archive. Agree retention and lookup needs with the business rather than importing everything by default or deleting history after a successful transfer.

Write down the pilot boundary so staff can tell, without guessing, which system owns any particular job.

Prepare and rehearse the record transfer

Save a dated source copy before cleaning or transforming the data. Work through duplicates, inconsistent statuses, mixed date formats, missing owners and cells that contain several different pieces of information. Microsoft's Excel-to-Access guidance recommends this kind of preparation, including separating values, correcting formats and backing up the original workbook.

Create a field map that says where each important value will go. Record decisions such as whether “Booked” and “Confirmed” mean the same thing. Do not silently merge ambiguous statuses or discard notes that somebody still uses.

Rehearse the import away from live operations. Include ordinary jobs, awkward records and jobs at different stages. Check how another import would behave: would an existing job be updated, duplicated or rejected? Resolve that before planning a final transfer.

Keep a discrepancy list with an owner for each correction. Workday's migration checklist similarly recommends test migrations, source comparisons, documented discrepancies and retesting after corrections.

Test whether staff can continue the work

A successful import proves that records can be loaded. It does not prove that the receptionist can reschedule a visit or that an engineer's completion note reaches the person preparing invoices.

Have the people who do the work run agreed scenarios using representative records. In the repair example, test a normal booking, a cancellation, a reassignment, a return visit, missing customer information and a correction to a completed job.

Check both the records and the actions:

• Active jobs: reconcile counts by status and owner, then match individual job references. Equal totals can still hide one missing job and one duplicate.
• Information: verify required fields, notes, dates and document links. Compare relevant financial totals on the same basis in both systems.
• Next actions: confirm that overdue, unassigned and waiting jobs appear where staff expect to find them.
• Permissions: test with actual staff roles, not only an administrator account. Confirm who can view, edit, approve and export sensitive information.
• Corrections: check what happens after a mistaken status change, duplicate entry or failed update, and who can resolve it.

Agree which failures block launch. Missing open jobs, incorrect invoice calculations or staff being unable to see their assigned work should not be treated as minor tidy-up tasks.

Run a bounded pilot without double entry

For pilot jobs, make the replacement the agreed place for updates. Mark those jobs clearly in the old spreadsheet and prevent further editing there where practical. Non-pilot jobs can remain in the spreadsheet until their agreed transfer.

Do not ask staff to keep both systems current indefinitely. Conflicting updates make it difficult to know whether a problem comes from the replacement or from someone consulting an outdated copy.

If temporary comparison is necessary, define its purpose, end point and owner. One person might compare an exported status report against agreed test outcomes, for example. That is different from asking everyone to maintain two live job lists.

Name a business owner who can resolve process questions and a technical owner who can investigate failures. Review early jobs through to completion before expanding. The pilot should demonstrate that work can finish correctly, not merely that staff can log in and create records.

Plan the final switch and recovery before launch

The rehearsal copy will become stale while staff continue working. Your cutover plan must account for jobs created, edited, cancelled or completed since that copy was taken.

Where operations allow it, a controlled pause in editing can make the final transfer easier to reconcile. If work cannot pause, define how changes will be captured and applied, including who handles incoming enquiries during the transfer. Do not rely on staff remembering what they changed.

Agree the following before the switch:

• Transfer boundary: when old-system updates stop and how new work is captured during the move.
• Final reconciliation: who checks active-job references, statuses, owners, required information and relevant totals after the final load.
• Approval: who confirms that the checks pass and authorises staff to use the replacement.
• Recovery: which failures would stop the launch, who decides, and how work entered after the switch would be preserved if you return to the old process.
• Old-sheet access: where the dated archive lives, who can see it and how it is clearly labelled as reference rather than current work.

Recovery needs more than a backup file. If staff have already updated jobs in the replacement, those changes must be reconciled before the spreadsheet can safely become authoritative again.

Use this checklist to approve the move

Your spreadsheet-to-software migration brief should answer these questions before launch:

• Have the rules hidden in formulas, colours, comments and staff habits been confirmed or deliberately retired?
• Can every outstanding job be matched to a record with the right status, owner and next action?
• Have staff completed routine tasks and everyday exceptions using their own permissions?
• Is there exactly one agreed place to update each job during the pilot and final switch?
• Are changes since the rehearsal included in the final transfer?
• Are approval, support and recovery responsibilities named?
• Can staff find historical information without mistaking it for live work?

You can still use spreadsheets for analysis or exports if that remains useful. The critical change is that staff no longer have to consult a second editable list to discover which jobs are outstanding or what should happen next.