Anonymise Personal Data in an Excel File with Python

A support team wants to share ticket data with an analytics agency. HR needs to send salary data to a benchmarking survey. A developer needs a realistic test file. In each case the workbook holds names, emails, dates of birth and addresses that must not leave the organisation, while the rest of the data must stay useful. Deleting the name column is not enough: a free-text note can still say "called Mrs Okafor on 07700 900123", and a 67-year-old in a small postcode with an unusual job title is identifiable without any name at all. This page, part of Validating Excel Data with Python, works through the techniques in the order they are usually applied, and ends with checks that prove the output is clean. It is a technical guide, not legal advice: what counts as anonymous enough depends on your jurisdiction and the recipient.

Prerequisites

pip install pandas openpyxl

customers.xlsx has a Customers sheet with an ID, name, email, date of birth, postcode, plan, monthly spend and a free-text notes column, plus a Tickets sheet keyed by the same customer ID. One cell carries a comment, and the file's author property names a person — both are places personal data hides.

Step 1 — Drop what the recipient does not need

The safest identifier is one that is not in the file. Start from the recipient's actual question and keep only the columns that answer it:

import pandas as pd

df = pd.read_excel("customers.xlsx", sheet_name="Customers")
tickets = pd.read_excel("customers.xlsx", sheet_name="Tickets")
out = df.drop(columns=["Name"])
print(list(out.columns))

An analytics agency studying spend by plan and region does not need names at all, so Name goes. Email is kept for one more step only to show masking; in most real shares it would be dropped here too.

Step 2 — Replace IDs with keyed pseudonyms

The customer ID links Customers to Tickets, and the recipient needs that link. Replace the ID with a pseudonym that is the same everywhere the ID appears but cannot be reversed. A plain hash is not enough — anyone can hash every possible C-1xxxx and match them up. Use a keyed hash (HMAC) with a secret that stays in-house:

import hashlib
import hmac
import os

SECRET = os.environ.get("PSEUDONYM_KEY", "use-a-long-random-secret-from-a-vault").encode()

def pseudonym(value, prefix="P"):
    digest = hmac.new(SECRET, str(value).encode(), hashlib.sha256).hexdigest()
    return f"{prefix}-{digest[:10]}"

out["CustomerID"] = out["CustomerID"].map(pseudonym)
tickets["CustomerID"] = tickets["CustomerID"].map(pseudonym)
print(out["CustomerID"].head(3).tolist())
print("join still works:", len(out.merge(tickets, on="CustomerID")) == len(out))

Because the key is the same, next month's extract produces the same pseudonym for the same customer, so the recipient can follow customers over time without knowing who they are. Truncating to ten hex characters keeps the pseudonyms readable; with 16¹⁰ possible values, collisions are not a concern for customer lists. Keep the key in a secret store, not in the script — see Store Credentials for Excel Report Scripts Safely. Pseudonymised data is still personal data under laws such as the GDPR, because whoever holds the key can reverse it; only when the key is destroyed does it approach anonymous.

Step 3 — Mask values whose shape matters

Sometimes the recipient needs to see that an email exists and which domain it belongs to — to count business versus consumer customers, say — but not the address itself:

def mask_email(e):
    if not isinstance(e, str) or "@" not in e:
        return e
    user, domain = e.split("@", 1)
    return f"{user[0]}***@{domain}"

out["Email"] = out["Email"].map(mask_email)
print(out["Email"].head(3).tolist())

Masking keeps the domain, which is often all an analysis needs. Be careful with small organisations: j***@tiny-firm.example may point to one person. When in doubt, reduce the column to what is actually needed — here, perhaps just the domain's top-level suffix.

Step 4 — Generalise the quasi-identifiers

A date of birth plus a postcode identifies most people, even without a name. Replace them with coarser values: an age band instead of a birth date, the postcode district (SW1A) instead of the full postcode:

as_of = pd.Timestamp("2026-09-29")
age = (as_of - out["DateOfBirth"]).dt.days // 365
out["AgeBand"] = pd.cut(age, bins=[0, 30, 40, 50, 60, 120], right=False,
                        labels=["under 30", "30–39", "40–49", "50–59", "60+"]).astype(str)
out["District"] = out["Postcode"].str.split().str[0]
out = out.drop(columns=["DateOfBirth", "Postcode"])
print(out[["CustomerID", "AgeBand", "District", "Plan"]].to_string(index=False))

The age calculation divides days by 365, which is off by a day or two near birthdays; for bands ten years wide that does not matter. Choose band widths with the recipient: wider bands protect more and tell less. Bin Values into Ranges with pandas cut covers the binning options.

Step 5 — Scrub free text

Notes columns are where identifiers leak. Replace anything that looks like an email address or a phone number, and remove names you know about:

import re

EMAIL = re.compile(r"[\w.+-]+@[\w-]+(\.[\w-]+)+")
PHONE = re.compile(r"(\+?\d[\d\s()-]{7,}\d)")
known_names = set(" ".join(df["Name"]).replace("'", " ").split())

def scrub(text):
    if not isinstance(text, str):
        return text
    text = EMAIL.sub("[email]", text)
    text = PHONE.sub("[phone]", text)
    words = re.split(r"(\W+)", text)
    return "".join("[name]" if w in known_names else w for w in words)

out["Notes"] = out["Notes"].map(scrub)
print(out["Notes"].tolist())

The phone pattern needs at least nine characters of digits and separators, so a ticket number like 5521 survives while 07700 900123 and +44 161 496 0000 do not. Name removal uses every word from the known names, which catches "Adaeze" and "Ken's" (as [name]'s). It is a blunt tool — a first name that is also an ordinary word will be removed from text where it is not a name — and it cannot catch names the file does not otherwise contain. For free text that matters, the honest options are to drop the column or to have a person review what remains.

Step 6 — Check how many people share each profile

Generalising helps only if each combination of remaining details is shared by several people. Count the group sizes for the quasi-identifiers — the smallest group is the file's k in k-anonymity terms:

quasi = ["AgeBand", "District", "Plan"]
groups = out.groupby(quasi).size().rename("people").reset_index()
print(groups.sort_values("people").to_string(index=False))
print("smallest group (k):", groups["people"].min())

Three of the five combinations describe a single customer — with eight rows that is unavoidable, and it shows why small extracts are hard to anonymise. With real volumes, generalise further (wider age bands, region instead of district) or suppress rare combinations until every group has at least, say, five people. The right k is a policy decision; the calculation makes it measurable.

Step 7 — Write a clean workbook and scan it

Write only the anonymised data to a new file — not into a copy of the original, which would carry comments, properties and hidden sheets with it — and then scan every cell of the result for patterns that should not be there:

from openpyxl import load_workbook

with pd.ExcelWriter("customers_shared.xlsx", engine="openpyxl") as xw:
    out.to_excel(xw, sheet_name="Customers", index=False)
    tickets.to_excel(xw, sheet_name="Tickets", index=False)

hits = []
wb = load_workbook("customers_shared.xlsx")
for ws in wb.worksheets:
    for row in ws.iter_rows():
        for c in row:
            if isinstance(c.value, str) and (EMAIL.search(c.value) or PHONE.search(c.value)):
                hits.append((ws.title, c.coordinate, c.value))
            if c.comment:
                hits.append((ws.title, c.coordinate, "comment"))
print("creator:", wb.properties.creator, "| findings:", hits)

The scan flags one value, on both sheets: the pseudonym P-7582122945 happens to be all digits, and the phone pattern matches it. That is a false positive, and a useful reminder that a pattern scan produces a list for a person to review, not a verdict. The masked emails do not match, because the asterisks break the address pattern. Everything else is clean: the comment about the VIP customer did not travel, and the author property says openpyxl rather than the original author, because the file was written fresh instead of copied. Remove Hidden Data Before Sharing an Excel File lists the other places a workbook keeps information.

Common pitfalls

• Unkeyed hashes. sha256("C-10041") can be reversed by hashing every possible ID. Use HMAC with a secret key.
• Forgetting the other sheets. An ID pseudonymised on one sheet and left raw on another undoes the work — and breaks the join.
• Copying the original file. Comments, hidden sheets, defined names and document properties come along. Write fresh.
• Trusting regexes on free text. They catch formats, not meaning. Drop or review free text that matters.
• Small groups. A rare combination of age band, district and job title identifies a person. Measure group sizes.

Conclusion

Drop every column the recipient does not need, replace linking IDs with HMAC pseudonyms from a secret key, mask values whose shape matters, generalise quasi-identifiers into bands and districts, and scrub or drop free text. Then measure: count the smallest group of people sharing the remaining details, write the result to a fresh workbook with no comments or properties, and scan every cell for patterns that should not survive.

Frequently asked questions

How do I anonymise an Excel file with Python? Read it with pandas, drop direct identifiers such as names, replace IDs with keyed pseudonyms, generalise dates of birth and postcodes, scrub free-text columns, and write the result to a new workbook. Finish by scanning the output for emails, phone numbers and comments.

What is the difference between pseudonymisation and anonymisation? Pseudonymised data replaces identifiers with codes that the key holder can map back to people, so it is still personal data. Anonymised data cannot be linked back to a person by anyone with reasonable effort.

Why not just hash the customer ID? Customer IDs follow a predictable format, so an attacker can hash every possible ID and look up the matches. A keyed hash such as HMAC-SHA256 with a secret key prevents that as long as the key stays secret.

How do I keep pseudonyms consistent across monthly files? Use the same secret key every time. HMAC gives the same output for the same input and key, so a customer gets the same pseudonym in every extract.

Related

• Up one level: Validating Excel Data with Python — checks that data is fit to use and to share.
• Validate Email and Phone Formats in Excel Data — the patterns behind the scrubbing step.
• Profile an Excel Dataset with pandas — understanding columns before deciding what to keep.
• Password Protect an Excel File with Python — protecting the files you cannot anonymise.