I have been interested in cryptography since I was a wee laddy. I don’t know how I got started – probably an article or puzzle in Boys Life or some boys’ book. But, on and off, I have dabbled with making codes and ciphers to play with my kids or just for fun. I was always especially interested in the historical aspects and the tools used by the Romans, Greeks, in the world wars and in the cold war, etc. Then, in the 1990s, I really got interested in a more serious way when the extent of the NSA and government snooping were revealed. I even taught myself how to write code in Visual Basic and then later, Python, so I could make some rather more intricate ciphers.

Now I am far from an expert, but I thought I would take a shot at giving folks a little tool that is quite secure and anyone can build and use almost anywhere they have Excel or a clone. There are many encryption tools available, but I am always skeptical of anything commercial, because you can bet the hackers have already compromised it. Not to mention, the NSA probably having a built-in back door. Even the so-called impregnable Iphone has now been compromised beyond repair.

What I am going to teach you is a common one-time pad with some unique variations. One-time pads are quite secure (nothing is totally secure!!) but are somewhat cumbersome due to the amount of mathematical calculations needed to get her cookin’. However, by using the power of good old Excel, we can take all that calculatin’ out of the picture. It will help if you have some knowledge of Excel, but I have tried to make that as unnecessary as possible. Do not let the large number of steps intimidate you - each one is very short and simple.

Step 1:

So, here we go…. First open Excel and beginning in Cell A20 or so create the table shown. In Column A put digits 0-9 and the alphabet a-z. In Column B put numbers 1-36, then copy Column A into Column C.
That’s it – there’s our table.

Step 2:

Now, in Row 1 beginning at Cell F1, we will enter our plain text (without spaces) that we want to hide. In our example I will show all the calcs, but when you make a working copy you will probably want to hide them just to make the whole thing cleaner looking.

Beginning in Cell F2, we are going to convert our plain text to the corresponding numbers (1-36) in Column B of our table. We can do this using the VLOOKUP function in Excel. This function looks up and returns values from a vertical table like we created in step one. In Cell F2 enter the formula shown >> =VLOOKUP(F1,$A$20:$B$55,2) You should end up with 12 in Cell F2. Note: Functions always begin with =.

Step 3:

Now beginning in Row 3, Cell F3, we will enter our key word or phrase. This should be unique to every message and be known to both sender and recipient. This can be done by referencing a book or other written material or by exchanging the keys in other ways. The key must be the same length as the message so if the phrase you choose is shorter than the message, you will have to repeat it until it is.

In Cell F4, enter another Vlookup only we will reference Cell F3 instead of Cell F1 >> =VLOOKUP(F3,$A$20:$B$55,2) This returns 23 to Cell F4.

Step 4:

Now in Cell F5, we are going to add the values of the plain text and the key together by entering this simple formula >> =f2+f4 << 12 + 23 = 35 – Cell F5.

Step 5:

As you can see, we will often end up with sums that exceed the limits of our table. We need to tame these down by subtracting 36 if they are over value. To do this, we have to use a logic formula called the IF. So, in Cell F6, enter >> =IF(F5<=36,F5,F5-36)

Step 6:

Now, we get to the meat of the matter and create our ciphered message by using the old Vlookup again. In Cell F7, enter >> =VLOOKUP(F6,$B$20:$C$55,2) This returns the Letter y, the first letter of our ciphered message.

Step 7:

You did it!! Our message – big village – becomes yx8nhy32z7!! Now, you can copy (read the help or manual for Excel – its easy!) the cells that you entered formulas in to the right down each row as far as you want.

Step 8:

OK, so now we have encrypted the message using our one-time pad and sent it to our partner. But, they will need a decryption process and here’s how we do that. Create another set of cells in Row 9 beginning in Cell F9 where you will enter the message to be deciphered.

In Cell F10, enter >> =VLOOKUP(F9,$A$20:$B$55,2) This should return the value (35) of the first letter of the cipher text (y) to Cell F10.

Step 9:

This time we subtract the Key value from the cipher text to get the plain text value. Enter >> =F10-F4 in Cell F11. 35-23=12 << Cell F11

Step 10:

In this case, you can see we will, again, often get values outside the range of our table, so we need to adjust by entering >> =IF(F11<1,F11+36,F11) in Cell F12.

Step 11:

Finally, enter another Vlookup in Cell F13 >> =VLOOKUP(F12,$B$20:$C$55,2) which will return the plain text of our ciphered message, the letter b to Cell F13.

Step 12:

There it is – a complete encryption program using a one-time pad! Now, if you were General Custer, you would have finished your message …….. Big Village Be quick Bring Packs….. Of course, his would have been a real ONE-TIME PAD!!

Step 13:

A final note….you can easily copy and paste messages into and out of the spreadsheet, but you will see when you copy your cipher text and paste it into Notepad or Word or whatever, there are spaces between characters. This is because of Excel’s formatting the text into individual cells and is called comma delimited. Eliminate the spaces simply by placing the cursor behind the characters and hitting Delete. Conversely, before pasting the cipher message into your spreadsheet, go behind each character and hit Tab which will place the characters into the cells when you paste into the spreadsheet.

I am planning a followup to this ‘Structable which will be a One-time Pad on steroids (See 'Fun With Crypto Version 2.0') and should be good for about whatever you want to do with it. It’s a little more complicated, but once you have this one figured out, it’ll be a snap. ytkmqrbgwkcavrb9jgun3l1g8!