Removing personal data from an Excel file so it is still usable

Deleting the columns destroys the analysis. Masking badly destroys the joins. What a format-preserving, checksum-valid fake looks like, and why random digits fail review.

Sooner or later a spreadsheet of real customer data has to leave the building: to a vendor, a contractor, a demo environment, or a model-training pipeline. Deleting the personal columns answers the compliance question and destroys the file's usefulness. Replacing them with random values keeps the shape and breaks everything downstream.

Three properties a masked file needs

1. Format-preserving. An 11-digit phone stays 11 digits. An 18-character ID stays 18 characters with a valid birth date inside it. If the output format differs, every downstream parser breaks and people re-run it on the real data to make the error go away.
2. Checksum-valid. Bank cards must still pass Luhn. Chinese ID numbers must still pass the ISO 7064 MOD 11-2 check digit. This is not cosmetic: form validation, importers and test suites reject numbers that fail the checksum, so a masked file that fails it cannot be used for testing.
3. Consistent and reversible. The same real value must map to the same fake value everywhere, in every file, on every run. Otherwise a JOIN on customer id silently returns nothing and the "anonymized" dataset is useless for the analysis it was created for.

Why random digits fail review

# what people write first
import random, string
fake = "".join(random.choice(string.digits) for _ in range(18))
# -> 483920174638291047   fails ISO 7064, has month 92, and differs on every run

Three separate defects. It fails the checksum. Its embedded birth date is impossible, so anyone who glances at it knows it is fake and cannot tell whether the rest of the file is real too. And because it is random, the same person gets a different id in the next file, so nothing joins.

The fix is a keyed deterministic map: derive the fake from HMAC(secret, real_value) and then repair the check digit. Same input plus same secret gives the same output forever, and without the secret you cannot reverse it to a real person.

The false-positive problem

The naive approach is a regex for "18 digits = Chinese ID". Real spreadsheets contain 18-digit order numbers, timestamps in milliseconds, and concatenated fields. If you mask all of them, your colleague opens the file, sees the order numbers destroyed, and stops trusting the tool — which is the worst outcome, because next time they will hand over the real data instead.

Validate before masking:

• Chinese ID: checksum passes and the embedded birth date is a real date and the province code exists
• bank card: Luhn passes and the length is 13 to 19 and the BIN prefix is plausible
• phone: the prefix is an allocated mobile range, not just 11 digits

In a typical mixed spreadsheet this takes the finding count from roughly 400 down to the 88 that are actually personal data.

What must not be masked

Masked columns are not automatically safe. Three things leak identity on their own and are usually forgotten:

• Free-text notes. "Called him back on his mobile, he said the account under his wife's name" — no regex finds that.
• Rare combinations. Postcode plus job title plus birth year identifies most individuals even after names are gone. If the file is small, consider dropping a column rather than masking it.
• File metadata. Author name, company and the original path stay in the .xlsx container after the cells are rewritten.

The tool

DataMask does format-preserving, checksum-valid, keyed masking across csv, xlsx, txt, log, json, markdown and docx, and writes a --map file so the original values can be restored by whoever holds the key. It reports what it validated and what it skipped, so false positives are visible instead of silent. 29 USDT, one Python file, runs offline.