How to anonymise a spreadsheet so the data cannot be pieced back together

Direct identifiers and quasi-identifiers

With tabular data it helps to separate two kinds of column. Direct identifiers (name, NI number or SSN, email, phone number, bank details) point to a person on their own and should almost always be removed or replaced. Quasi-identifiers (date of birth, postcode, job title, sex, start date) look harmless one by one, but together they can single out a row: in a small team, “head of service, born 1971, postcode in the same street” may be just one person.

There is a third category people forget: free-text columns such as Notes, Reason or Comments, which can contain anything from a relative's name to a diagnosis. Those cells cannot be treated as a single value; they need to be read and the personal data inside them detected.

Decide what to do with each column

The question for each column is the same: does the recipient need it? If not, delete it. If they need it to link records but not to know who someone is, pseudonymise it. If they need it as it is and it does not identify anyone, keep it. Applying data minimisation settles most questions before you reach for any technique.

What you cannot see in a spreadsheet

• Hidden and very hidden sheets. A hidden sheet does not appear in the tabs but its data is still in the file; sheets set to “very hidden” through VBA are not even listed under Unhide.
• Hidden or filtered rows and columns. Hiding the NI number column or filtering rows does not delete anything; anyone can unhide them.
• Formulas. A cell can show a total while pointing to a hidden sheet full of names, or to an external workbook whose path reveals folder or user names.
• Pivot caches. A pivot table stores a copy of its source data; even if you delete the source sheet, double-clicking a value can rebuild the detail rows.
• Comments, notes and workbook properties, with authors, dates and paths.
• Charts and defined names pointing to ranges with personal data.

Excel's Inspect Document finds hidden sheets, rows and columns, comments and properties, but it does not decide what to do with each visible column. For pivot tables, the safest option is to paste them as values or delete them before sharing; for formulas that depend on data you are removing, consider pasting the results as values.

Removing names is not enough: re-identification risk

With tabular data, the real risk lies not in one cell but in the combination of columns. k-anonymity measures exactly that: a dataset is k-anonymous if every combination of quasi-identifiers appears in at least k rows. The Article 29 Working Party's opinion on anonymisation techniques and the ICO's anonymisation guidance both treat it as one way to manage identifiability, not as an absolute guarantee: if all k rows share the same sensitive value, that value is still disclosed.

In the US health sector, the HIPAA Safe Harbor method takes a similar line for tabular data, requiring dates to be reduced to the year and geographic units below state level to be removed, with a narrow exception for the first three digits of a ZIP code. In practice: generalise (year instead of date, area instead of full postcode), merge categories with few cases, suppress unique rows where they add nothing, and assess the risk before publishing.

Pseudonymise consistently

If the recipient needs to follow the same person across rows, sheets or files (January and February payroll, say), the substitution must be consistent: the same NI number always becomes the same substitute. If that column is the key between the Staff sheet and the Payroll sheet, both must receive the same value or the joins break. Pseudonymised data is still personal data, and the mapping table must be stored separately and protected. More on this in document pseudonymisation.

How to do it in Anonimatum

Since version 1.9.0, Anonimatum handles spreadsheets column by column in .xlsx, .xls, .ods and .csv: it suggests the data type of each column and you choose to keep, redact, replace, pseudonymise or detect in text (useful for notes columns). The result comes out in the same format, with its sheets, formatting and formulas. Every 50 rows use 1 page of quota, so a 2,000-row register counts as 40 pages.

To stay consistent across files, a case file's shared map gives the same data the same substitute in every document it contains. If you work with payroll, our page on anonymising payslips in PDF and Excel covers the typical cases.