One of the challenges sometimes with sending your spreadsheet to someone, whether it’s to review or to make changes to it, is that it contains sensitive information.
So I’ve created a template and add-in that will help you accomplish that.
You can download the template here or if you prefer, the add-in is available here.
Disclaimer: I do not offer any guarantees or promises that this will work perfectly and it’s the responsibility of the user to ensure and confirm that all data is adequately protected as the spreadsheet still requires manual steps from the user.
The template will look like a regular spreadsheet, but with one main difference. On the far right end of the Home tab, you’ll see this button:
If you select the data you want to encrypt, whether it’s a few cells or an entire column, then clicking this button will mask your data. Here’s how it works.
In my spreadsheet, in column A, I have a list of customer names that I want to protect.
If I highlight the range and click on the Encrypt Selection button, what will happen is it will mask the data and create a new tab showing me which customer is mapped to which mask:
After all, masking the data is great, but if you don’t know what the data means, it’s not very helpful. The macro creates a tab called “Mapping Hidden Cells” and it’ll give you a popup warning you to delete this tab before you send the data to anyone. The purpose of this tab is to give you the table, and you can either make note of it or save it somewhere. Either way, you’ll want to delete this afterward, otherwise it would make masking the data pointless.
If I return to my main tab I notice that the data is now masked:
The customer names are now nowhere to be found. At this time I’d like to point out that the masking will only happen to the cells that you selected. If for example I had customer data in a different area and didn’t select it, that data would not have been masked.
This is why it is crucial for the user to review the data themselves and validate that the sensitive information has in fact been correctly masked, and that the mapping tab has been deleted.