How to mask data in Excel
Show the last digits, hide the rest
Masking hides most of a value but keeps enough to recognise it, like
************1234 for a card number. Excel can do it with formulas, as long as
you remember one catch.
Masking with formulas
With the original value in A2:
| Goal | Formula | Result |
|---|---|---|
| Keep the last 4 characters | =REPT("*",MAX(0,LEN(A2)-4))&RIGHT(A2,4) |
************1234 |
| Keep the first character | =LEFT(A2,1)&REPT("*",MAX(0,LEN(A2)-1)) |
M************* |
| Mask an email, keep the domain | =LEFT(A2,1)&"***"&MID(A2,FIND("@",A2),LEN(A2)) |
m***@example.com |
MAX(0,…) keeps short values from causing a #VALUE! error. The
email formula returns #VALUE! for cells without an @.
The catch: the original is still there
A formula only changes what you see. The original value stays in its column, and anyone you send the file to can read it. After masking:
- Copy the masked column and paste it back as values (Paste Special › Values).
- Delete the original column.
- Check other sheets, hidden columns, comments and pivot table caches for copies.
Masking also is not always enough on its own: the last four digits of a phone number plus a name and a city can still identify someone. Mask or remove the other columns too.
A faster way
The Scrubsheet add-in for Excel masks, redacts or replaces whole columns in one step and writes real values, not formulas, into a copy of the sheet, so there is no original left behind. It detects which columns contain names, emails, phone numbers, IBANs and ID numbers, and masking is included in the free plan. Outside Excel, the free browser tool does the same for Excel and CSV files.
Try it on your own file. The Scrubsheet browser tool anonymizes Excel and CSV files in your browser, free and without uploading anything. In Excel, use the Scrubsheet add-in.
Anonymize a spreadsheet