Contents
Password protection of sheets in Microsoft Excel has long been a byword. In the sense that it, in fact, does not exist. With regularity about once a month, I receive questions in the mail on the topic “how do I protect my data in an Excel sheet from being viewed / modified?” And every time I don’t know what to say. You can, of course, give a link to an article with a detailed description of all the ways to protect cells and sheets in Excel, but such protection will only stop a beginner. On the network you can find a bunch of paid and free programs for cracking such protection with blunt enumeration in a matter of minutes.
At some point, I got tired of this and I began to look for ways to more reliably protect data in Excel on my own. The most simple and convenient was the Vigenère cipher.
Visionary code principle
One of the oldest and easiest to implement is the Caesar cipher, which used it for secret correspondence. Its essence is that each letter of the original encrypted message is shifted in the alphabet by a given number of characters. So, for example, if the shift is 3, then the letter A will turn into a D, the letter B into a D, and so on:
Characters at the end of the alphabet (E, Yu, Z), respectively, will turn into its beginning (A, B, C).
It is easy to implement such a cipher, but its durability is not great – you can even find the desired shift number and decrypt the message even by brute force in 20-30 iterations, which will take even a person no more than an hour, and a fraction of a second for a modern computer. Therefore, back in the 15th century, it was first invented, and then in the 16th century by the French diplomat Blaise Vigenère, a more advanced method based on the Caesar cipher, which later became known as the “Vigenère cipher”, was officially presented. Its principle is that each letter in the original encrypted text is shifted alphabetically not by a fixed, but by a variable number of characters. The shift value of each letter is given by a key (password) – a secret word or phrase that is used for encryption and decryption.
Let’s say we want to encrypt the phrase “THE TREASURE IS BURY IN THE GARDEN” using the word WINTER as the key. We write this word in a row several times under the original phrase:
For the convenience of encryption, we use the so-called “Vigenère square” – a table where in each line the alphabet is shifted one position to the right:
If we take a row with the first letter of the key (Z) and a column with the first letter of the source text (K), then at their intersection we will see the letter “T” – this will be the first letter of our encrypted message. Then the procedure is repeated for all other pairs of letters of the key and the original message in turn, and as a result we get an encrypted version of our original phrase:
Note that the same letter (for example, A) in the original messages turned into different letters in the output (N, Y and B), because the encryption shift was different for them. That is why it is impossible to open the Vigenère cipher in simple ways – until the 19th century it was considered unbreakable and was successfully used by the military, diplomats and spies of many countries, in particular, by the Confederates during the American Civil War.
Implementation by Vigenère squared formulas
If you use the ready-made Vigenère square as in the example above, then you can implement encryption using one formula using the functions INDEX (INDEX) и MORE EXPOSED (MATCH), as it was described in the article about two-dimensional table search. It might look something like this:
The logic behind this formula is as follows:
- The first MATCH function (highlighted in green) looks for the first letter of the key (Z) in the green column (B9:B40) and returns the sequence number of the cell where it found it, i.e. line number in the Vigenère square on which the encryption is going.
- The second MATCH function (highlighted in pink) similarly searches for the first letter of the original message (K) in the red line and returns the ordinal column number.
- The INDEX function gives the contents of the cell from the square (C9:AH40) from the intersection of the row and column with the found numbers.
Implementation by formulas by character codes
It is easy to understand that in real life, documents can use not only letters of the language, but also Latin, numbers, punctuation marks, etc. Making a Vigenère square with all these symbols is still epic, but there is another, much simpler way.
Inside the computer and the operating system, each character has its own numeric code from 0 to 255 (also called the ASCII code). Microsoft Excel has two functions in its standard set that can work with them:
- Function KODSIMV (CODE) – returns the numeric code of the character specified as an argument. For example, CODSYMV(“W”) will return 198.
- Function SYMBOL (CHAR) – produces a character corresponding to the code specified in the argument, i.e. on the contrary, CHAR(198) will give us the letter J.
To use the Vigenère cipher, we write our source text and the key under each other as before and derive the codes for each letter using the CODESYM function:
Now let’s add the character codes of the key and the source text by adding the function THE REST (MOD)to stay within 256-0 when the maximum number of characters (255) is exceeded:
Now it remains to use the SYMBOL function to display the characters according to the received codes and form an encrypted message:
Of course, one could do without additional lines, putting all the functions in one formula for compactness:
The decoding is done in exactly the same way, only the plus sign in the formula changes to minus:
For espionage games, encryption with such special characters, of course, is not very convenient – I can imagine the eyes of the radio operator Kat when trying to transmit the third and fifth characters of our encryption 🙂 our goals – will do.
Macros for encryption-decryption
Well, now the most interesting. To apply the Vigenère cipher in real life, it would be better to use a simple macro that performs all the operations described in the previous paragraph with each cell of the current sheet automatically. Open the Visual Basic Editor with a keyboard shortcut Alt + F11 or by button Visual Basic tab developer (Developer). Insert a new module using the menu command Insert – Module and copy the text of our macros there:
'Encrypt current sheet Sub Encrypt() Dim Pass$, Key$ Pass = InputBox("Enter key to encrypt:") Key = WorksheetFunction.Rept(Pass, 100) For Each cell In ActiveSheet.UsedRange Out = "" Txt = cell .Formula For i = 1 To Len(Txt) Out = Out & Chr((Asc(Mid(Txt, i, 1)) + Asc(Mid(Key, i, 1))) Mod 256) Next i cell.Value = Out Next cell End Sub 'Decrypt current sheet Sub Decrypt() Dim Pass$, Key$ Pass = InputBox("Enter key to decrypt:") Key = WorksheetFunction.Rept(Pass, 100) For Each cell In ActiveSheet.UsedRange Out = "" Txt = cell.Value For i = 1 To Len(Txt) Out = Out & Chr((Asc(Mid(Txt, i, 1)) - Asc(Mid(Key, i, 1)) + 256) Mod 256) Next i cell.Formula = Out Next cell End Sub The first macro asks the user for a key and encrypts all the cells in the current sheet. The second macro performs the reverse decryption operation. You can run the resulting macros using the keyboard shortcut Alt + F8 or buttons Macros (Macros) tab developer (Developer). It might all look like this:
Important nuances
- ATTENTION! If you carefully read the article, you should clearly understand that there is no easy way to find out or pick up the key! There are several techniques for breaking the Vigenère cipher, but all of them are very difficult for a layman and do not give a 100% guarantee. If you forget the key, you will lose data forever with a high probability. If so, I warned you.
- When encrypting, formulas, links and formatting are not violated – after decryption everything works fine.
- If during decryption you enter the key incorrectly, you will get a meaningless “porridge” of special characters instead of your text (because the code shift will be incorrect). Then you have to go back a step by re-encrypting with the same password and then try to decrypt the document again (this time using the correct key).
- 4 Ways to Protect Data in Microsoft Excel
- Super Hidden Sheet in Excel Workbook
- Selective display of book sheets to users by password