You don't always need to use a programming language for cryptography. In this post, I describe how to use a spreadsheet to encipher and deciper with a Caesar cipher.

The spreadsheet

You can follow along with a copy of the spreadsheet on Google Drive. You'll find this post makes a lot more sense if you can see how all the bits fit together in the spreadsheet, as well as being able to see the results of the formulas used. Make your own copy of it if you want to encipher and decipher your own messages.

Describing how the spreadsheet works is tricky, as you can't just quote snippets of program code and discuss them. I've broken the spreadsheet down into regions (see the diagram below) and will go through them, region by region, describing how it all fits together.

Dashboard (B2:B7)

At the top of the spreadsheet, in cells B2 to B6, is the "dashboard" summarising the cipher breaking.

• Cell B2 has the original message
• Cell B4 has the Caesar key to use
• Cell B6 is the generated plaintext using that key (it contains the formula =AA47).
• Cell B7 is the generated ciphertext using that key (it contains the formula =AA48).

To use the spreadsheet, you put your message in cell B2 and the key in cell B4. The spreadsheet will then encipher the message and put the result in cell B7 and decipher the message with the result in cell B6. That way, you don't have to tell the spreadsheet whether you want the message enciphered or decipered: it does both for you.

During the processing, the spreadsheet uses uppercase for ciphertext letters and lowercase for plaintext letters. That means that case isn't preserved in the generated plaintext and ciphertext.

Alphabet (B10:AA10)

Just a copy of the alphabet.

Shifts (A16:A41)

Simply all the shifts for the Caesar cipher, 0–25.

Shifted plaintext (B16:AA41)

Each cell in this block says what each plaintext letter would be, with the Caesar shift given in the corresponding row in A16:A41.

Cells B16 to AA16 just contain the formulas =B10 to =AA10: a Caesar shift of 0 gives the original alphabet.

Cells B17 to Z17 each contain the formula =C16 to =AA16: in other words, the content of the cell one above and to the right. Cell AA17 contains the formula =B16. Together, these formulas rotate the row above one space to the left, with the leftmost cell of the block moving to the right.

Cut and paste row B17:AA17 into the remaining rows of the block, B18:AA41. You'll end up with all the shifts of the alphabet, with row 41 containing z a b c … w x y.

Enciphered and deciphered message (B44:AA48)

Now we know how to do convert plaintext to ciphertext letters for all shifts, we can do the enciphering and deciphering for the shift given in cell B4.

Each column in the shifted plaintext block shows how a single plaintext letter is transformed with different shifts. Going down column B, you can see how a is transformed into a, b, c, … as the Caesar shift changes from 0, 1, 2, …

We treat the shifted plaintext block as a large lookup table, giving us the ciphertext letters we need. The LOOKUP function does the lookups for us.

| | B | C | D | … | AA |
|:-:|:-:|:-:|:-:|:-:|:-:|:--:|
| 44 | =UPPER(LOOKUP($B$4, $A$16:$A$41, B16:B41)) | =UPPER(LOOKUP($B$4, $A$16:$A$41, C16:C41)) | =UPPER(LOOKUP($B$4, $A$16:$A$41, D16:D41)) | … | =UPPER(LOOKUP($B$4, $A$16:$A$41, AA16:AA41)) |
| 45| =B10 | =C10 | =D10 | … | =AA10 |

LOOKUP can be used in a couple of ways. The way I'm using it here has this signature:

LOOKUP(search_key, search_range, result_range)

The function looks for the search_key in the search_range (either a row or a column). If it finds the key, it moves along the result_range the same number of steps and returns the value it finds there. The result_range can be either a row or a column, but need not be the same shape as the search range.

In this case, the search_key is the identified Caesar shift, found in cell B4. The search_range is the list of possible shifts in the cells A16:A41. The result_range depends on the plaintext letter we're after. It's the column under the appropriate plaintext letter from the alphabet block. For a, the result_range is B16:B41. For b, the result range is C16:C41, and so on, up until AA16:AA41 for the column corresponding to z.

Therefore, we can say the formula LOOKUP($B$4, $A$16:$A$41, B16:B41) will give the ciphertext letter corresponding to plaintext a for the Caesar shift we're using.

As we're using uppercase letters for ciphertext, we wrap the result in a call to UPPER() to convert the ciphertext letter to uppercase.

That explains row 44: it's the ciphertext letters for each . Row 45 is just those plaintext letters, copied down from B10:AA10.

Now for rows 47 and 48.

The SUBSTITUTE(where, search, replace) command looks in where for text search and replaces it with replace. When enciphering, we use it to substitute platinext letters with ciphertext letters. When deciphering, we replace ciphertext letters with plaintext.

Cells B44:AA44 and B45:AA45 together show how each ciphertext letter relates to a plaintext letter. The cells in B47:AA47 do the conversion from ciphertext to plaintext. With a shift of 21, B44:B45 will say that ciphertext V corresponds to plaintext a.

The formula in B47 converts all the Vs in the message into as. The formula in C47 takes the result of in B47 and then converts all the Ws into bs. D47 takes the result in C47 and converts all the Xs into cs, and so on until AA47 converts all the Us into zs. The result in AA47 is the final, extracted plaintext, and that result is copied up to the dashboard by cell B6.

This is why the use of case to distinguish plaintext and ciphertext is important. With a shift of 21, ciphertext v converts to plaintext a, and ciphertext a converts to plaintext f. If we just did those two conversions one after the other, the final plaintext would have no as but a whole bunch of fs. However, if we use case, we can distinguish between the conversions V to a and A to f. That's why where's a call to UPPER in cell B47: it ensures that what we're treating as ciphertext is all uppercase.

In the same way, the cells B48:AA48 convert the message into ciphertext.

The two results, AA47 and AA48, a copied back up to the dashboard.

And that's the result! That's how you can use a spreadsheet to implement Caesar ciphers.

Other ciphers

There's no particular reason why you can't extend this approach to other ciphers, such as the affine cipher. But there is a practical limit to do with the number of keys. The Caesar cipher has 25 possible keys. The affine cipher has 12 × 26 × 2 = 624 possible keys. That would mean that the spreadsheet would need 624 rows in the large blocks, as you still need one row for each possible key.

I've not got around to building a spreadsheet that large, but there's no reason why you couldn't.

Acknowlegements

Post cover image by NordWood Themes.