Before you start
- ✓Every copy of the spreadsheets, and who edits which
- ✓A list of the tabs and columns, and which every customer has
- ✓Who should see what: staff, managers, customers
- ✓Someone who uses the sheets daily, to check the data as it moves
- ✓Agreement that retiring the spreadsheets is the business's call
There's no fixed recipe. Every set of spreadsheets grew up differently, so we always start with a review and adapt the approach to what we find. These are the questions we work through, with how we answered them on an IT support portal we built, and why.
How are the spreadsheets really organised?
Before designing anything, write down how the files are actually used: every file, every tab, every column, which columns every customer has, and who edits what. On the IT support project it was one spreadsheet per client, with a tab for each kind of thing (computers, printers, email accounts, routers, Wi-Fi and so on), 16 in all.
Fixed tables, or flexible fields?
The obvious design is a database table per tab, with typed, validated fields. That gives you consistent data and fast search, but every new column becomes a code change and a database migration. If the columns change often, a flexible custom-fields design may suit you better. On the IT support portal the data was stable and search mattered most, so each tab became its own table, with the things every row needs (notes, attached documents, archiving instead of deleting) in one shared base.
Who should see what, and how will you know who changed what?
A shared spreadsheet lets anyone change anything without a trace. Decide early who needs access to which customers and which kinds of data, whether two-factor authentication is required, and how changes are recorded. On the portal, every change is logged with who made it, when, and the old and new values.
If the sheets hold passwords or other secrets, decide how they'll be protected before the first record goes in: encrypted in the database, and kept out of the audit history. Adding that later means re-processing every stored value, including the audit trail.
What do people actually look things up by?
Finding something in a spreadsheet estate means knowing which file and which tab to open. Ask people what they search for day to day, and design search around that. On the portal, full-text search across every client and every table, with each result labelled with the tab it came from, made finding a client's details over 90% faster.
Script the migration, or do it by hand?
It depends on the volume and how clean the data is. On the portal we chose to move the data manually: going through it record by record let us correct long-standing inconsistencies so the system started clean, and it doubled as acceptance testing, because the client saw their real data going into the new screens. For larger or messier data, script it and validate the result (see how to migrate data safely).
Who goes first?
Staff usually should. Let your own team use the new system for real until it has bedded in, before any customer sees it. When customers do get access, it doesn't have to be all of them: on the portal, read-only access went to the larger clients with their own IT capability, and smaller clients were looked after exactly as before.
What do people need to keep?
People trust formats they know, and nobody should feel locked in. The portal can export a client back into the original workbook layout, one worksheet per tab. On another project, where a spreadsheet other people relied on couldn't be switched off, we added a button that produces exactly the row staff paste into it.
When do the spreadsheets retire?
That's the business's decision, made when people are ready, not a date a developer imposes. Once the new system is clearly better, people stop opening the old files on their own.
What usually goes wrong
From our own projects, not the manual.
No record of who changed what
In a shared spreadsheet it was easy to mistype a password or edit the wrong cell, and nothing showed who had changed it, when, or what it said before. The portal records every change, with the person and the old and new values.
People who work across several customers
Staff assigned to more than one client were stuck on the first one until we added a client switcher. Design for one person, many customers from the start.
Customer logins landed on a staff-only page
After signing in, client-only users were sent to a page only staff could open, so they saw 'access denied' on return visits. Test every role's journey, not just the admin's.
Every new column is a code change
With a table per tab, a new field means a database migration and a deploy. On a system that keeps growing, that adds up, so weigh it against how often your columns change.
Do it yourself, or get help?
If a handful of people use the spreadsheet and it holds nothing sensitive, a low-code tool or a shared database may be all you need. Get help if several people edit it at once, it holds credentials or personal data, or customers will need to see their part of it: access control and an audit trail are much harder to add later than to build in.
Get a developer on it