Removing Personal Data From an Excel File Before Sharing

Someone asks for the customer file, and what they need is the product mix by region. The file you hold has names, emails, phone numbers, a notes column and the account manager's comments. The tempting move is to open it, delete a few columns and send it. That works until the recipient finds the deleted columns in a pivot cache, or the author's full name in the properties, or a comment saying "difficult customer, ask Maria". This article is the removal procedure that holds up, and it complements the audit in hidden sheets and metadata leaking out of shared workbooks by showing how to fix what that audit finds.

The openpyxl documentation on workbook properties lists the document properties that a library can set or clear.

Decide what the recipient needs

Start from the purpose, not the file. List the questions the recipient needs to answer and the fields that answer them. Everything else stays out. This is data minimization, and it is the strongest control you have, because data never sent cannot leak.

For the product mix by region, the needed fields are product, region and quantity. The name, email, phone, street address and notes are not. If a field might be useful later, that is the recipient's request to make later.

Think about indirect identifiers too. A postal code, a birth date and a gender together can single out a person even when the name is gone. A rare job title in a small company does the same. Decide whether to generalize (a region instead of a postal code, a birth year instead of a date) or to drop the field.

Reduce the data first

Do the reduction on data, before any workbook is written.

import pandas as pd

df = pd.read_excel("customers.xlsx", sheet_name="Customers", dtype=str)

keep = ["product", "region", "qty"]
shared = df[keep].copy()
shared["qty"] = pd.to_numeric(shared["qty"], errors="coerce")
summary = shared.groupby(["region", "product"], as_index=False)["qty"].sum()

An aggregate is often what the recipient wanted all along. A small group is a risk of its own: a cell that says "1 customer in region X bought product Y" identifies that customer to anyone who knows the region. A common rule is to suppress or merge groups below a threshold, such as five. Apply it explicitly and record the threshold:

summary = summary[summary["qty"] >= 5]

Suppression is crude and it is easy to explain, which counts for a lot in a review.

Mask, hash or generalize

When the recipient does need row-level data, choose the least revealing form that still works.

• Drop the field. This is the default.

• Generalize it: a date to a month, an age to a band, a postal code to its first digits.

• Mask it: j***@example.com keeps the shape for display but cannot be joined.

• Pseudonymize with a keyed hash so the same customer gets the same token across files, letting the recipient link rows without learning who they are.

A plain hash of an email address is not safe. Anyone can hash a list of likely emails and compare. Use a keyed hash, with a secret key that the recipient does not have:

import hmac, hashlib

KEY = b"load-this-from-a-secret-store"

def token(value):
    return hmac.new(KEY, value.strip().lower().encode(), hashlib.sha256).hexdigest()[:16]

df["customer_token"] = df["email"].map(token)

Keep the key out of the repository. If you lose it, tokens in later files will not match earlier ones. Pseudonymized data is still personal data under laws such as the GDPR, because someone holding the key can reverse the mapping. Say so in your handling notes, and share it only on the same footing as the original.

Build a new workbook

Write the reduced data into a fresh file. A new workbook has no hidden sheets, no pivot cache, no external links and no comments.

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Summary"
ws.append(list(summary.columns))
for row in summary.itertuples(index=False):
    ws.append(list(row))

wb.properties.creator = "Data team"
wb.properties.lastModifiedBy = "Data team"
wb.properties.title = "Product mix by region"
wb.properties.company = ""
wb.save("product_mix.xlsx")

Set the properties explicitly. A fresh openpyxl workbook sets the creator to openpyxl by default, which is harmless, but overwrite it with the name you want shown. If you start from a template file instead of Workbook(), you inherit the template's properties, sheet names and defined names, so check those.

When you cannot rebuild

Sometimes the workbook must stay as it is: it has formatting, formulas and charts that took a week to build. Then clean it in place and verify hard.

1. Remove personal columns from every sheet, and from any sheet that feeds a chart or a pivot.

2. Delete pivot caches by deleting the pivot tables and pasting their results as values, or by turning off "Save source data with file" and refreshing.

3. Delete comments and notes, and review the Review tab for threaded comments.

4. Delete hidden sheets you do not need, rather than leaving them hidden.

5. Remove external links under Data, Edit Links, Break Link.

6. Clear document properties with File, Info, Check for Issues, Inspect Document, and remove personal information.

7. Save under a new name, then audit the saved file by reading its parts.

Excel's Document Inspector handles several of these in one pass, and it is worth using. It is a convenience rather than a guarantee, so audit the output with a script and read the workbook once as a stranger would.

openpyxl round-trips cleanly for plain data, and it drops or changes some features when it loads and saves a file, for instance charts and images in older versions and pivot caches. Do not assume that loading and saving with a library produces a clean copy; it produces a different copy, and you must inspect it.

Check the output

Open the result as the recipient would. Search every sheet for @, for digit runs that look like phone numbers and for the names you know are in the source. Search the unzipped parts too:

unzip -o product_mix.xlsx -d check/ >/dev/null
grep -ril "maria\|@example.com" check/ || echo "no matches"

A clean result prints no matches. That does not prove there is no personal data, since it only finds strings you thought of, and it catches the careless leaks. Add an automated test to the export job that fails if the output contains any value from the source's personal columns.

For checking sheet states, properties and caches, reuse the audit from the companion article and run it on every outbound file.

Keep a record

Write down what was shared, with whom, on what date, with which fields and under what justification. If the recipient later reports a problem, or if a person asks what you hold on them, the record answers. Store the record with the job, not in someone's inbox. For recurring reports, the script and its configuration are the record.

When removal is not enough

Removal is not enough when the remaining data can be linked to a person by someone who holds another dataset, which is true of most detailed row-level data. It is not enough when the law or a contract limits who may receive the data at all, whatever its form; ask the privacy officer first. It is not enough when the workbook contains free text, because names appear in notes in ways a column rule will never catch. In those cases share aggregates, share under an agreement that restricts use, or give the recipient access to a controlled environment where they can run their analysis without a copy leaving. See Excel security problems developers overlook for the other outbound risks, and testing a generated Excel report for assertions you can add to the job. The UK Information Commissioner's guidance on anonymization explains the difference between anonymized and pseudonymized data in more depth.