Cipher challenge toolkit

We have created some Excel spreadsheets containing various tools which automate techniques frequently used in codebreaking.

There are two versions. The first version uses macros. If it doesn't work on your computer or network, try the second version without macros.

Each sheet contains instructions on how to use it.

In general, you only need to change entries in cells highlighted red (the exception is transposer 2 and 3, for which the instructions show the steps you should follow). 

There are four functions in the spreadsheet:

1) Frequency analysis - counts the number of occurrences of each letter in the ciphertext so that you can compare it to the letter frequencies of normal English text. This helps you to determine the sort of cipher that might have been used. 

If the distribution is very similar to normal English, it may be that a transposition cipher has been used.

It also contains a table showing the most common digraphs (pairs of letters), which is also helpful for determining the encryption alphabet.

2) Substitution decoder - decodes the ciphertext according to the encryption alphabet you provide.  

3) Transposers 1, 2, 3 - transposer 1 writes the ciphertext in a rectangular grid of a size you specify. Transposer 2 splits the text into one letter per cell, and transposer 3 effectively reads down the columns for you. 

4) Vigenere - this sheet decrypts a vigenere cipher given a keyword. You can read more about Vigenere ciphers on Wikipedia.

The toolkit should help you decode the messages, though you may find it easier to use pencil and paper for some of your decoding work.

You may wish to explore the formulae used in the spreadsheet to see if you can make sense of how they work. You could then adapt them, or write your own formulae, or extend the formulae if your message is very long.