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**!

## Share

### DumbodoreL made it!

## 5 Discussions

2 years ago

And z?

Reply 2 years ago

kestrel smoke: This algorithm does not use case. There is also no punctuation possible. One could perhaps incorporate Asci code into it somehow, but that is not the intent here. This type of algorithm is solely intended to convey short, succinct messages.

2 years ago

How do you do a y in lower case?

2 years ago

Good!!! it wouldn't take much to make this polyalphabetic. I'm not sure why you don't trust PKI though, for instance PGP hasn't given anyone backdoors, and it's free because they couldn't sell it due to te Wasennauer agreement that deals with the transmission of arms. I can see how you want a one time pad over any other because technically it is at this point the most secure as long as you never use the same key twice.

Reply 2 years ago

Thank you Wolfbane221. Yes, indeed, PGP is the gold standard and has stood the test of time - at least as far as we know, huh? lol! I guess I just like to be the 'master of my own domain' and create my own toys!