The problem lies in the fact that the frequency with which letters occur in English is remarkably consistent. The letter E is the one that occurs most often, followed by T and then R and so on. Frequency analysis involves going through a piece of text, plain or encoded, and seeing how many times each letter (or symbol) appears.
To demonstrate the regularity of this frequency pattern, I have taken the first 1000 letters of two separate texts and counted how many times each letter appears. Here are the results:
| A: | 76 | F: | 31 | K: | 10 | P: | 20 | U: | 37 | ||||
| B: | 15 | G: | 11 | L: | 40 | Q: | 2 | V: | 8 | ||||
| C: | 21 | H: | 68 | M: | 25 | R: | 57 | W: | 26 | ||||
| D: | 38 | I: | 46 | N: | 56 | S: | 78 | X: | 0 | ||||
| E: | 129 | J: | 0 | O: | 87 | T: | 104 | Y: | 13 | ||||
| Z: | 2 | ||||||||||||
| A: | 86 | F: | 24 | K: | 1 | P: | 18 | U: | 32 | ||||
| B: | 15 | G: | 25 | L: | 51 | Q: | 0 | V: | 8 | ||||
| C: | 30 | H: | 60 | M: | 19 | R: | 67 | W: | 20 | ||||
| D: | 43 | I: | 69 | N: | 55 | S: | 71 | X: | 1 | ||||
| E: | 116 | J: | 8 | O: | 74 | T: | 90 | Y: | 17 | ||||
| Z: | 0 | ||||||||||||
It's surprising that there is so much agreement between two such different texts (one a deep, philosophical, thought-provoking text, and the other a play by Shakespeare), written four hundred years apart. Anyway, the average frequencies of the letters for fairly long pieces of text (200 letters or more) are as follows:
| A: | 73 | F: | 28 | K: | 3 | P: | 27 | U: | 27 | ||||
| B: | 9 | G: | 16 | L: | 35 | Q: | 3 | V: | 13 | ||||
| C: | 30 | H: | 35 | M: | 25 | R: | 77 | W: | 16 | ||||
| D: | 44 | I: | 74 | N: | 78 | S: | 63 | X: | 5 | ||||
| E: | 130 | J: | 2 | O: | 74 | T: | 93 | Y: | 19 | ||||
| Z: | 1 | ||||||||||||
... and here it is as a chart:
If you want to decode a message written in a simple replacement code like this, construct a frequency chart for the letters in the code. Providing you have enough letters in the code, you should be able to hazard a well-informed guess as to which code letter represents E, which represents T, which A etc. Exactly how far you can keep going guessing at the identity of the letters depends on how many letters there are in the message - typically you will be able to decode about 7 or 8 letters. Mind you, after you have decoded the 7 most common letters of the alphabet, you should be able to work out some of the words in the message simply by looking at them.
We are going to dedicate the first line of the spreadsheet entirely to the message as it may be dozens (hundreds) of letters long. Type this text into cell A1 and don't worry if it spills over into the adjoining cells, or even if it takes up several screensful - Excel can cope!
Now put the letter A in cell C3, followed by B in D3, then C in E3 etc. until you reach Z, which should be in AB3. Follow this with the word Total in cell AC3.
Put the numbers 1, 2, 3, 4 etc. in cells A5, A6, A7 etc. down the left side of the sheet. Make sure 1 goes in A5 to leave enough room for the totals to appear above it. In fact, you can just type in the first numbers of the list (up to about 4), highlight these first few numbers, then use Fill Down to add many more numbers. Put enough numbers to cover every character in the message, i.e. if you think the message contains 500 characters, put in numbers down to at least 600 to be on the safe side. Your screen should now look like this:
You'll notice that I have shrunk the columns holding the letters A to Z quite severely so that they all fit onto one screen. This is a matter of taste, of course, but I think it makes it easier to see what's going on. I highlighted the columns and then selected Autofit Selection from the Format menu. This shrinks (or expands if necessary) the columns until they are just wide enough to hold the widest items that they contain, and since each column only contains a single letter, they shrink quite a lot!
Now we have to split the message so that each individual letter can be counted. Put the following formula in cell B5:
This extracts the first letter of the message. Fill this formula down the column to extract the others. I don't intend to go into detail about how this works as it is the same as what we did with Caesar's Cypher. Click here for an explanation.
Anyway, you fill this down the column as far as you have filled the numbers. Don't worry if you go past the end of the message - the cells will simply hold blanks from there on. In cell C5 put the following formula:
This means "If the character in cell B5 (directly to the left of this cell) is the same as the letter in C3, then put a 1 here. Otherwise put a 0". If the letters do match, then we have found (another) occurrence of that particular letter, so we register it with a 1.
Having typed the formula in, fill it to the right so that it is duplicated for each letter in row 5. You will find that the cells C5 to AB5 hold all zeroes, except that one of the cells may hold a 1. This will be the cell under the letter in row 3 which also appears in cell B5. For instance, in the following diagram, the first letter is a letter T and the corresponding cell in row 5 has a 1 in it.
Having filled the row across highlight all the cells from C5 to AB5 and fill down the columns as far as the you have gone with the numbers. You will end up with something like this:
Each line corresponding to a letter in the message will produce a 1 in the corresponding column. Cells which contain spaces and other punctuation will produce rows with nothing but 0s in them, as they don't match any letters.
All that remains to be done is to count how many 1s there are for each letter of the alphabet. In cell C4 (you probably wondered why we left that row blank!) put the following formula:
This just means "add up all the 1s in this column from here downwards". Your message probably won't go as far as row 1000, but it will simply ignore empty cells beyond the end of the message. In this diagram, which shows the final spreadsheet, I have put the totals for each letter and the letters themselves in bold type, and put a border round them. I have also added a formula at the end to give the total number of letters in the message - it simply sums the numbers in cells C4 to AB4:
. Choose which sort of chart you would like - I suggest something like a bar chart or a line chart. Then click on the Next icon at the base of the Chart Wizard window.
This specifies the cells above the ones you selected - the cells holding the letters of the alphabet.
If you go through each of these options in turn, you will end up with a chart similar to the one shown further up on this page. You can see the complete chart on the example spreadsheet (in ZIP format).