How do you protect your Excel file?
Every now and then my clients ask me, “Jürgen, what is the most effective way to protect your data and IP in Excel?”
And the simple answer is “you don’t…”
Let me explain…
There are two aspects here to think through: How? and Why?
Let’s talk first about the How.
There are a couple of options here.
- A) You could simply highlight all the cells which allow for data-entry in a specific colour eg. Light yellow. This would signal to the educated user, please enter data here, everything else are formulas and you’d better not touch them.
- B) You could use the sheet protection Excel offers. It is a bit clumsy, but once you go the hang of it, it’s not complicated. Now any user is not able to touch any cell that is not labelled as “locked” (see cell-formatting / protection). I’d still recommend to highlight the cells which are open to avoid frustrations by the user.
- C) You could use the Workbook protection on top to even further prevent any normal user to hack into your data and read more than they should.
- D) If you are using the macro language VBA – like I do all the time, you could also protect the code with a password that prevents any user from seeing your code and be able to make changes to your programming.
You know what all these options A) to D) have in common: they are not safe. Nowadays it only takes a few searches on the internet and you’d find free tools to download to overcome these simple barriers.
There is an even more simplistic trick to outsmart all of the above, but I’m not sharing this publically here… 😉
I have been asked multiple times to help a client to get back into his or her own Excel file because they’d forgotten the password to unlock the protection. As long as my clients can prove that these files are actually theirs or that they have the legal rights to access them, I’m happy to help.
- E) Work with encrypted data and separate the data from the actual program in Excel.
The advantage is, that even though it looks like a normal Excel file, no one can read the data without having access to the Decrypting code.
The big disadvantage is, that the loading and storing of data takes longer.
Just recently I’ve used this procedure for a client and with 500,000 records the decrypting took 60 seconds whereas the encrypting took 90 seconds. Not bad, but maybe sometimes not practical.
Before I talk about the how safe is this let’s look at the other big question I’ve mentioned earlier:
Why do you want to protect your data/IP?
Again there might be several different reasons:
1st You want to make sure no-one is overwriting your complex formulas
2nd You fear someone would steel your IP or data
3rd You want to prevent that your program is copied and pasted all over the internet
If the first one is your reason for implementing some protection on your worksheet that is totally valid and makes sense. Still there are some words of caution though. Once you haven’t used your Excel file for three months, chances are that you also forgot the precious password that might get you back in.
So what do you do? You write down the password on a piece of paper and eventually you even give this password to the person working with the sheet all the time. Which means you might as well don’t have a protection and password in the first place.
Remember I’ve seen some clients coming to me in desperation, because they’ve lost their password. And within Excel there is no “Forgot your password” function.
When your why is motivated by reason two or three then I must tell you that if someone really I mean r-e-a-l-l-y want to steal your IP or your precious program they will do so and you can’t prevent it.
In my humble opinion I think you should make the operation of your Excel workbook as smooth as possible for the honest user and not try to build fences for the very few that might or might not try to steal from you.
We all put up with the enormous security measures around the airport when we simply want to fly from Sydney to Melbourne or back just because there is a one in a million chance that someone is trying to highjack that plane.
I’ve mentioned earlier the protection by using encrypted data. Again, if someone really wants to hack into your system and into your data they will do so. And sometimes the higher the challenge – the higher the walls you build, the more ambitious the people become to climb over it. For some it is a sporting competition.
So that’s why my answer to the typical question “how do I protect my Excel file?” is “you don’t”.
More than happy to consult with you over your specific situation, because everyone and every situation is different.
Please book a phone-conversation with me using this link.