Blue Flower

Encryption and Cryptography in Excel

Enigma Encryption Machine

I believe you have heard about ENIGMA, a device that can encrypt message or text. Creating a message encryption machine is not difficult for cryptography and encryption professionals. There are also specialists dealing with code breaking, ie decrypting the message. ENIGMA is one of the devices designed to encrypt messages that are difficult to decipher. Encouraged by a TV show on the Enigma machine as well as a series of TV shows about spy messages between France and England in the "Victorian era", an interesting idea seemed to try to make an easy way to encrypt or encrypt the letters in Excel. For example, you want to encrypt your passwords that you use to access emails or forums, facebook, instagrams, whatsup applications, and the like.

Prepare the order of letters and characters for encryption

For this Excel tutorial, I will show "a simple example" how to encrypt and decrypt a specific text. The encryption and decryption process itself consists of two parts. "Encrypt" and "Decrypt" the letters. Below the picture below, see the layout of the letters and characters by which I have encrypted and decrypted the text/letter.

Image 1

The order of letters and characters for text encryption

In the picture above you can see two pairs of cell ranges related to encryption and decryption. In this way I have twice encrypted the letters/text. First, the open text was encrypted with the letters from columns 'A:C' and then encrypted in columns 'I:K'. So twice I repeated encryption (Encription 1 => Encription 2). Also note two different layout of letters and characters in the columns 'B,F/J,N'. So this is important because anyone who wants to hack this code should know these two sequence. Each letter and character should have an apostrophe added at the beginning!

The following formulas are placed in the cells:

In the 'A3' cell, the formula is set:
=ROW(A1)-1

In the 'A4' cell is set the formula: (this formula is copied to 'A6' cell)
=A3+1

In the 'A7' cell, the formula is set:
=C3

The 'A8' cell is set by the formula: (this formula is copied to the last row)
=A7+1

In the 'C3' cell is set the formula: (this formula retrieves the data from the 'R2' cell and this is the number that determines the start number)
=$R$2

The 'C4' cell is set by the formula: (this formula is copied to the last row)
=C3+1

In the other columns of the formulas, the same are only referenced to other cells (all can be seen in the excel file attached)

Once you set the starting position for a possible series of letters and characters in the middle column, you can prepare a certain order in advance as shown below (this sequence may be on another worksheet).

Image 2

A series of secret letters and characters for cryptography or encryption

Encrypting Open Text in Excel

On the 'Image 3' below you see parts that I used to encrypt and decrypt text. There are two numbers in 'R2' and 'S2' cells. At the beginning of this tutorial I pointed out that in this way we set the start number. The number of 'R2' cells is reflected in 'C3', 'E3', 'A7' and 'G7' cells. The number of 'S2' cells is reflected in 'K3', 'M3', 'I7' and 'O7' cells (see Image 1).

In 'Z2' and 'AA2' cells, there are identical numbers as control keys for one of the formulas that can be found in the tutorial below (I will describe it later).

In 'R5:AE5' cells, there are our letters that we want to encrypt.

Image 3

Excel encryption and cryptography of letters in plain text

Formulas for encrypting and encoding text in Excel (Encrypted Text)

In 'R9: AE9' cells there are formulas by which we encrypt every fifth letter. So the letter 'W' is encrypted in 'A' letter, and so on.

Formula u 'R9' ćeliji je slijedeća: (ova formula mora biti postavljena u stupac i ovu formulu kopirajte u desnu stranu)
=IF(ISEVEN(COLUMN(R1))=TRUE;IF(R5="";"";VLOOKUP(VLOOKUP(R5;$B$3:$C$45;2;FALSE);$A$3:$B$45;2;FALSE));IF(R5="";"";VLOOKUP(VLOOKUP(R5;$J$3:$K$45;2;FALSE);$I$3:$J$45;2;FALSE)))

The formula above can be created with named cell ranges, so it looks like in the 'R11' cell:
=IF(ISEVEN(COLUMN(R1))=TRUE;IF(R5="";"";VLOOKUP(VLOOKUP(R5;encription1Alphabet;2;FALSE);encription1Numbers;2;FALSE));IF(R5="";"";VLOOKUP(VLOOKUP(R5;encription2Alphabet;2;FALSE);encription2Numbers;2;FALSE)))

Formulas for Decrypting and Decoding Text in Excel (Decripted Text)

In 'R14:AE14' cells there are formulas by which we decrypt each letter from the encrypted text. So the letter 'A' is decrypted to 'W' in a letter, and so on. These formulas should be your secret (or create some other formula that will use your secret decryption key).

The formula in 'R14' cell is the following: (copy this form to the right).
=IF(ISEVEN(COLUMN(R1))=TRUE;IF(R9="";"";VLOOKUP(VLOOKUP(R9;$F$3:$G$45;2;FALSE);$E$3:$F$45;2;FALSE));IF(R9="";"";VLOOKUP(VLOOKUP(R9;$N$3:$O$45;2;FALSE);$M$3:$N$45;2;FALSE)))

The formula above can be created with named cell ranges, so it looks like in 'R16' cell: (copy this form to the right).
=IF(ISEVEN(COLUMN(R1))=TRUE;IF(R9="";"";VLOOKUP(VLOOKUP(R9;decription1Alphabet;2;FALSE);decription1Numbers;2;FALSE));IF(R9="";"";VLOOKUP(VLOOKUP(R9;decription2Alphabet;2;FALSE);decription2Numbers;2;FALSE)))

The formula in 'R18' is linked to 'Z2' and 'AA2'. In the previous text, I mentioned these two cells. They are the control key you need to know to properly decrypt the text. The sum of these two numbers should be equal to the sum of numbers in 'R2 and S2' cells. Copy the Formulus to the right.
=IFERROR(IF(AND($R$2=$Z$2;$S$2=$AA$2;ISEVEN(COLUMN(R1))=TRUE);VLOOKUP(VLOOKUP(R9;$F$3:$G$45;2;FALSE);$E$3:$F$45;2;FALSE);VLOOKUP(VLOOKUP(R9;$N$3:$O$45;2;FALSE);$M$3:$N$45;2;FALSE));"")

Encrypt the text by adding a letter group to each letter or character

If we want to complicate decryption for "potencijalog hackers" or reinforce the combination of codes or unwanted person, then we can increase encryption protection.
In Figure 4, note the columns 'AI' and 'AJ'. These are the helper columns in which I placed random letters in four character/letter groups. Of course you can place more letters in each group.

You also notice the 'AL2:AY2' cell. In this cell range there are my secret numbers with a precise sequence. They are the key to encrypting text in the fifth row below.

Encripted Text 2

In the 'AL5' cell is the formula: (this formula, copy to the right)
=VLOOKUP(AL2;$AI$1:$AJ$43;2;FALSE)&R9
This simple VLOOKUP formula search item of 'AL2' cells in the cell range of extra columns, returns a certain group of letters and concatenate them with an encrypted letter from the 'R9' cell. So we got groups of 5 letters/characters. In this case, the encrypted letter is added to the end of each 4-letter group. You can combine your formulas to create a formula that will encrypt the letter in another position in the group.

Image 4

Encrypt text by five or ten letter groups

Dectipted Text 2

Decryption in the case a group of letters/characters we can use the following formula in 'AL15' cell: (copy this form to the right).
=IF(ISEVEN(COLUMN(AL1))=TRUE;IF(AL5="";"";VLOOKUP(VLOOKUP(IF(LEN(AL5)=5;RIGHT(AL5;1);"");$F$3:$G$45;2;FALSE);$E$3:$F$45;2;FALSE)); IF(IF(LEN(AL5)=5;RIGHT(AL5;1);"")="";"";VLOOKUP(VLOOKUP(IF(LEN(AL5)=5;RIGHT(AL5;1);"");$N$3:$O$45;2;FALSE); $M$3:$N$45;2;FALSE)))

The formula in the 'AL17' cell is linked to the "Control Code Key" in the cells 'R2, S2, Z2 and AA2'.
=IFERROR(IF(AND($R$2=$Z$2;$S$2=$AA$2;ISEVEN(COLUMN(R1))=TRUE);VLOOKUP(VLOOKUP(IF(LEN(AL5)=5;RIGHT(AL5;1);"");$F$3:$G$45;2;FALSE );$E$3:$F$45;2;FALSE);VLOOKUP(VLOOKUP(IF(LEN(AL5)=5;RIGHT(AL5;1);"");$N$3:$O$45;2;FALSE);$M$3:$N$45;2;FALSE));"")

Encrypting and Decrypting Text that consists of multiple characters and letters

In the following case, we have encrypted text in multiple letters and characters (Image 5). For this encryption we use helper columns as in the previous example. Since the Auxiliary Column is one of the secret keys for cryptography or encryption, we only know that every fifth letter is our key-coded letter as we can see in the results of the fifth order.

Encripted Text 2a

Formula in 'AL24' cell: (formula combines groups of 5 letters/characters into one multiple string of letters)
=SUBSTITUTE(TRIM(AL5&" "&AM5&" "&AN5&" "&AO5&" "&AP5&" "&AQ5&" "&AR5&" "&AS5&" "&AT5&" "&AU5&" "&AV5&" "&AW5&" "&AX5&" "&AY5);" ";"")

Decripted Text 2a

To decrypt text from 'AL24' cells, I used the following formula in 'AL28' cell: (when copying this formula to your Excel, put it in one line).
=IF(ISEVEN(COLUMN(AL1))=TRUE;IF(MID($AL$24;COLUMN(A1)*5;1)="";"";VLOOKUP(VLOOKUP(MID($AL$24;COLUMN(A1)*5;1);$F$3:$G$45;2;FALSE); $E$3:$F$45;2;FALSE));IF(MID($AL$24;COLUMN(A1)*5;1)="";""; VLOOKUP(VLOOKUP(MID($AL$24;COLUMN(A1)* 5;1);$N$3:$O$45;2;FALSE);$M$3:$N$45; 2;FALSE)))

Image 5

Encrypt and decrypt text with multiple series of letters and characzers

If you are in love with encryption and encryption and this tutorial has encouraged you to play more with encryption and decryption in Excel, on the Internet there are a lot of examples on the subject of text encryption. Also on some websites you can download Excel files that were made using VBA macros. In this Excel example tutorial you have the ability to download (DOWNLOAD file with the Excel Encryption example) to your computer file from the example that was made using simple Excel formulas. And finally you remember, there is no 100% protection, any code can break only the issue of time.

Be careful and watch who you trust when you launch XLS or XLSM Excel files on your computer!
If you want to run the Excel file format *.xls or *.xlsm and you are unsure whether the file in question is a malicious VBA code, then press the SHIFT key when opening the Workbook (this will prevent you from running VBA macros).

Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.