Moving off a spreadsheet without losing the twenty years in it
The spreadsheet is the most common incumbent in this category, and it is the one nobody writes a migration guide for — there is no vendor on the other side to write it. So: how to move a membership list that has been accumulating since before the current treasurer joined, without losing the parts of it that are not really data.
The core problem is that a long-lived spreadsheet holds two things. Columns, which import cleanly. And knowledge — colour coding, a column called Notes2, a row that is blank because that family moved away but nobody wanted to delete them. The knowledge is the part worth protecting.
Before you touch anything, take a copy
Duplicate the file and date the copy. Everything below is destructive in the sense that it changes what the working file looks like, and the original is your only record of what the colours meant.
Then work through this
- 01
Write down what the formatting means, in a cell
Yellow rows, bold names, a strikethrough — every long-lived membership spreadsheet encodes status in formatting, and no import reads colour. Add a plain text column and put the meaning in it: "lapsed 2024", "life member", "do not email". This is the single highest-value thing you will do, and it takes twenty minutes.
- 02
Split anything holding two facts in one cell
"John & Mary Chen" is two members. "555-0143 (cell) / 555-0187 (h)" is two numbers. "12 Oak St, Apt 4" is fine as one field, but "Joined 2019, renewed 2021, lapsed 2023" is a history that belongs in three places. Split the ones that matter and leave the rest.
- 03
Decide what a household is before you import, not after
Two adults at one address paying one subscription is the case every membership system handles differently, and it is much cheaper to decide now. Either they are one record with two contacts, or two records linked by a household. Pick one and make the spreadsheet say it consistently.
- 04
Keep the lapsed ones
The instinct is to import only current members, because it is cleaner. Resist it. Lapsed members are the cheapest recruitment list any organization has, and a former member with a join date and a lapse date is worth far more than a name on a list of people you once knew. Mark them lapsed; do not delete them.
- 05
Normalise dates to one format, and one format only
A column with 03/04/2019, 2019-04-03 and "April 2019" in it will import as three different things or fail entirely. ISO — 2019-04-03 — is the format least likely to be misread as US or UK order. If a date is genuinely unknown, leave the cell empty rather than guessing; an empty cell is honest and a wrong date is worse than none.
- 06
Leave the email addresses exactly as they are
Do not tidy, deduplicate or correct them by eye. If two members share an address, that is a real thing that happens and the system should be told. If one looks like a typo, flag it in a note rather than fixing it — you will be wrong roughly a third of the time, and a corrected address that was right originally is a member who silently stops hearing from you.
- 07
Import a sample of twenty first
Twenty rows chosen to include the awkward ones: a household, a lapsed member, someone with no email, someone with an apostrophe in their name, and your longest-standing member. Look at all twenty in the new system. Every mapping error you are going to hit is in that set, and finding it there costs minutes instead of an afternoon of unpicking.
- 08
Reconcile on three numbers, not on a glance
Total records, count of active members, and the sum of dues owed. If all three match the spreadsheet, the import worked. If one is off, it is off for a reason you can find. "It looks right" is not a check.
What to do with the spreadsheet afterwards
Keep it, read-only, somewhere the board can reach it, and stop editing it the day the new system is live. The failure mode here is not losing the file — it is running both for four months, because someone updates the spreadsheet out of habit and now there are two truths and no way to tell which is current. Pick a date, announce it, and make the spreadsheet read-only on that date.
Free to use, adapt and hand to whoever does this job next. If something here is wrong or missing, tell us and we will fix the page — contact us.