A system instead of Excel: when a spreadsheet stops being enough and how to switch without disrupting work

Excel is not the enemy. Most record-keeping in Polish companies started out in a spreadsheet, because a spreadsheet was to hand and did not need anyone from IT. The problem starts later, when the spreadsheet becomes a system: it has versions, it has an “owner” who is the only one who knows how it works, and it has formulas nobody dares to touch. Below: how to tell that the spreadsheet is no longer enough, and how to move to a system without halting work.

Signs that the spreadsheet is no longer enough

• Several people enter data at once. A file on a network drive with a queue of “who has it open”, or copies with the date in the file name and merging on Fridays.
• The same fact is in three spreadsheets. An employee in the training records, the clothing records and the attendance list, with three variants of their surname.
• Nobody knows which version is current. The summary for the board differs from the summary for the department because they were created from different copies.
• History does not exist. An overwritten cell has no previous value and no author. During an inspection, you cannot show who changed the medical examination date and when.
• The spreadsheet has an “owner”. When that person is on holiday, the report does not get made.
• The formulas are secret knowledge. A macro someone wrote years ago works as long as nobody touches the column.
• Data has to be retyped further on. From the spreadsheet into the ERP, into an email, into a PDF for the customer.

One sign is nothing yet. Three at once mean that the spreadsheet is already a system, just without a system’s safeguards.

How a system differs from a spreadsheet

Not in the number of features, but in four things:

1. A single source of truth. An employee, product or work order exists once; all records refer to it.
2. Who, what, when. Every change has an author and a time; you can go back to the previous value.
3. Validation on entry. A date in the date field, an employee from the list, a quantity that is not negative. An error shows up straight away, not during an audit.
4. Parallel work. Ten people at once, each seeing the same thing.

If the planned “system” does not provide these four things, it is a spreadsheet in different packaging.

How to switch without stopping the company

Step 1: choose one set of records, not all of them. The one with the most signs from the list above and a single owner on the company’s side.

Step 2: clean up the data in the spreadsheet before you move it. The import is the best moment for a clean-up: one spelling of surnames, one list of job positions, dates in one format, duplicates removed. A system with dirty data is worse than a spreadsheet, because it adds a sense of order to the mess.

Step 3: import with an error report. A good rollout imports the spreadsheet through a template and returns a list of rows that did not go through, with the reason. Those rows are corrected in the spreadsheet and imported again until the list is empty.

Step 4: a parallel run, but a short one. For a set period (weeks, not months), data is entered into the system and the spreadsheet is used only for comparison. If after that time someone is still entering data into the spreadsheet, the system has not been rolled out.

Step 5: spreadsheet read-only. The file stays as an archive, locked for editing. From that day on, the truth is in the system.

Step 6: the reports that were missing. Summaries for the board generated from the system, in the same form as they used to come from the spreadsheet, so that recipients do not have to learn a new layout in the first month.

Typical mistakes

• Moving all the spreadsheets at once. The company drowns in imports, and nobody uses the system.
• Moving the mess in the hope that the system will tidy it up.
• Keeping the spreadsheet “just in case” with no end date. It stays forever.
• Replicating the spreadsheet one to one, with the same columns, without a data model. The result is a spreadsheet in a browser.
• No data owner on the company’s side. A system without a custodian degrades just like a spreadsheet.

Checklist

• One set of records chosen for migration, with an owner.
• Data in the spreadsheet cleaned up: dictionaries, formats, duplicates.
• Import template and error report in the rollout plan.
• End date of the parallel run set in advance.
• Reports from the system in a layout familiar to recipients.
• Plan for the next set of records only after the first one is closed.

At ErgoFlow, moving off a spreadsheet starts with the data model and an import with an error report, not with screens; that is how our applications for record-keeping, workflows and settlements, described on the Applications page, were built. We write about how we connect them with ERP and other systems on the Automation page.

This article is for information only. How many sets of records it is worth moving, and in what order, depends on the company; we give the scope and timing after an audit.