From spreadsheets to a proper system: when to move, and how to do it safely.

Signs you have outgrown the spreadsheet

• Several people need to update it at once, and edits collide or overwrite.
• Nobody is sure which version is current, or the same data lives in several files.
• Mistakes keep happening from copying, pasting and broken formulas, and cost real money.
• Different people need to see different things, and a whole file cannot be shared safely.
• You need an audit trail: who changed what, and when.
• Other systems should be fed automatically, and re-typing is slow and error-prone.
• One person is the single point of failure for how it works.
• It is slow, large or constantly crashing.

One of these alone may be fixable inside the spreadsheet. Several together usually mean it is time.

Before you build or buy anything

• Describe the process in plain words. What triggers each row, who touches it, what decisions are made and what the end result is. Include the unwritten rules that the owner keeps in their head.
• Find the real data. List every file, tab and column, and where each value comes from. Note which columns are inputs and which are calculated.
• Clean it. Duplicates, inconsistent spellings, mixed formats and free text where a choice belongs all make migration painful. Fix them in the sheet first; it is cheaper there.
• Decide what to throw away. A migration is a chance to leave behind columns nobody uses and records past their retention period.
• Check an existing product first. Many processes fit software you can buy. See build or buy.

Moving the data safely

• Back up the originals and keep them untouched.
• Agree what each field means and what format it takes, such as dates, phone numbers and currencies. See forms and data for Hong Kong.
• Handle Chinese text properly. Check that import and export keep characters intact, and that sorting and search work as users expect.
• Reconcile counts and totals after the import: the same number of customers, the same value of open orders.
• Treat personal data with care. Move only what you need, restrict access and delete the stray copies. See the PDPO guide.
• Plan a way back. If something goes wrong in week one, you should be able to return to the old sheet.

People are the harder part

• Involve the people who use the sheet today. They know the exceptions, and they can sink the project if they are ignored.
• Make the new way easier, not just newer. If entering an order takes longer than before, people will drift back.
• Train briefly and early, and leave a one-page guide.
• Name an owner. Someone must decide, fix and answer questions after the supplier leaves. See maintenance and support.

An illustration: a small import business

(An illustrative scenario, not a client.) A business tracks stock and orders in one large workbook edited by four people. Orders are re-typed from messaging apps, and the stock count is wrong every month end. After mapping the process, the first release replaces only the order tab: each order is captured once, linked to stock, and the sheet is kept read-only for a month as a cross-check. Stock and supplier invoices move in the second phase. The accounting package stays as it is.

Keep reading

To see whether moving a task is worth it, try the automation payback calculator.