In the first ‘Able I did on this, we created a One-time pad spreadsheet in Excel to encrypt short text strings. In this one, I’ll show you how to improve the complexity of the tool we created and make your encryptions more secure.

Step 1:

The one-time pad we created in the first ‘Able was pretty good, but fairly predictable in the context of real cryptography. The data was all in alpha/numeric order and employed a set of common characters and values.
In cryptography, we don’t want orderly. We want organized chaos, but chaos that can be reorganized at the recipient end.

But, what if we scramble the table? A zero will no longer always have a value of ‘1’, and ‘a’ will not always be equal to ‘11’. Now, we’re talkin’!!

And then, what if we create a bunch of tables which all have the data in completely different orders and be able to choose which table to use? Essentially, what we will be doing is inserting a Substitution cipher algorithm, which as a stand-alone is very weak cryptography, inside our One-time pad algorithm. However, it adds to the complexity of our one-time pad algorithm and makes it much harder to break.

Step 2:

First, we need to add some new tables for our data. So, scroll down to Cell A59 and enter 0-9 and a-z in Column A. Then in Column B, at Cell B77 (next to ‘I’ in Column A), enter ‘1-18’ down to ‘z’ in Cell A94.

Then, enter 19-36 beginning in Cell B59.

Step 3:

Now, we will populate Column C with the numbers 1-36 in Cells C59 – C94.

Step 4:

Now, enter 0-9 and a-z in Column D so that the data corresponds to Columns A and B. In other words, so that 18 = z and 29 =a, etc.

Step 5:

We have to have Columns A and C in alpha numeric order because Vlookup must have the data in order to function. This is easy if we just shift the data up or down, but if we manually scramble the data in Column B, it becomes rather tedious to reorder Column D. You can make this super easy by using the Sort tool in Excel. Here’s a quick and dirty description of how to do it:

(1) Copy Column A to Column D and Column B to Column C

(2) Select all the cells in Columns C and D

(3) Open the Sort/Filter tool

(4) Click Sort A to Z – smallest to largest

Step 6:

Create as many tables as you want - up to 64 max. You won’t need nearly that many, though, to make this dude purty dang hard to punch through. It helps to name your tables and record their addresses as I did here.

Note: Make sure you redo Table 1 to match the new ones!!

Step 7:

OK, now we are ready to employ our new tables to dramatically improve the security of our one-time pad cipher. We are going to do this by calling the data from each table on demand with the functions in our spreadsheet.

Lets look at our first Vlookup in Cell F2 >> =VLOOKUP(F1,$A$20:$B$55,2). We are going to modify this to select the table we want to use by inserting it into an IF function like this:

=IF($A$1=1,VLOOKUP(F1,$A$20:$B$55,2),0)

So, if we enter a ‘1’ in Cell A1, the function will return values from Table 1. If we enter nothing or anything except a ‘1’, it will return a zero. We are basically telling the computer – if I put a ‘1’ in Cell A1, lookup the character in F1 in Table 1 and give me its value, if I don’t put a ‘1’ in A1, give me a zero.

Next, we are going to move that zero over and replace it with another IF function to select Table 2 when we choose. Now, the formula looks like this:

=IF($A$1=1,VLOOKUP(F1,$A$20:$B$55,2),IF($A$1=2,VLOOKUP(F1,$A$59:$B$94,2),0))

Notice that we changed the table address to $A$59:$D$94 which is the location of Table 2.

So, referencing another table consists of (1) Moving the zero to the right (2) Inserting a new IF function with the nested Vlookup function after the comma (3) Adding a closed parenthesis

I usually lose count of my darn parenthesis and mess this up, but Excel will catch it and offer to correct it for you which is very nice of it.

You can employ up to 64 nested IF functions, but its really unnecessary to have that many variations and wont significantly increase the security of the algorithm unless you use this very often.

The next step is replace all the Vlookups in Rows 2 and 4 with the new nested IF functions.

Step 8:

We now need to change the formulas in Rows 7, 10 and 13 like we did for Rows 2 and 4 to use our new tables.

Just be sure your Vlookup equations for Rows 7 and 13 reference Columns C and D and the equations for Row 10 reference Columns A and B.

Here’s what the formula in Cell F13 would look like for two tables:

=IF($A$1=1,VLOOKUP(F12,$C$20:$D$55,2),IF($A$1=2,VLOOKUP(F12,$C$59:$D$94,2),0))

You will find it easier, when entering these long formulas, to use a text editor like word or notepad and then copying into Excel.

Step 9:

If you want to add a further twist, you can alter the equations in Row 4 (the Key Vlookup values) to reference a different table than you are using for the rest of the spreadsheet.

For example, here is the first part of Cell F4 >

=IF($A$1=1,VLOOKUP(F3,$A$20:$B$55,2),IF($A$1………………….

Simply change the ‘A’ to ‘B’ like this in each nested IF function and it will check Cell B1 to determine which table to use >

=IF($B$1=1,VLOOKUP(F3,$A$20:$B$55,2),IF($B$1………………….

I’m sure some advanced Excel users and crypto experts can see how easily one could create a facsimile of an Enigma machine by creating a bunch of tables, a number of ‘setting’ cells like A1 and B1 and programming each cell in the rows to reference these different ‘settings.’ If you do this, you would have a cipher that is very close to, if not actually, completely unbreakable in any kind of reasonable time frame.

Step 10:

A final word. If you absolutely, positively have to rely on encryption, I am a big believer in double or triple encrypting stuff. Here are some free text encryption tools, some of which look pretty good and purport to use Rijndael or AES algorithms.

http://listoffreeware.com/list-best-free-text-encryption-software/

I have downloaded a few and played with them and haven’t had any problems – but use at your own risk. One or more of these employed with your One-time pad should really give the KGB a headache. They’ll probably give up cracking your message and just trap you in an embassy somewhere or exile you to Russia.

And, this link is to the same folks and has a list of free file encryption tools and some of these are worth checking out for people who need casual encryption.

http://listoffreeware.com/list-best-free-file-encryption-software/

Good luck and I hope you enjoy your adventures in cryptography!